ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Error 42703

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Error 42703

    I have this sql statement that runs fine in strsql, but when i put it in my rpg program it gives me error 42703, i cant figure this out it is driving me nuts. The job log tells me
    Cursor CURSOR1 already open or allocated.
    Column or global variable AMT_PAID not found.

    PHP Code:
       DECLARE CURSOR1 CURSOR FOR                                   
     
    SELECT CNDESC AS CLASS_NAME,CNCODE AS CLASS_NUMBER,            
     
    MONTH(WSDATE) AS MONTHCOUNT(DISTINCT(WSCNUM)) AS WORKSHOPS,  
     
    COUNT(WKSNUM) AS CLIENTS,(COUNT(DISTINCT(WSCNUM)) * CNRATE)    
     AS 
    amt_paid                                                    
     FROM CAWRKS JOIN CAWKSD                                        
     ON WKCA
    #=WSCA# AND WKCNUM=WSCNUM                               
     
    JOIN CACNM                                                     
     ON CNCODE
    =WSCNCO                                               
     JOIN CABINV                                                    
     ON WSINV
    =CBINVA                                                
     WHERE WSDATE BETWEEN 
    :FDATE AND :TDATE                         
     
    AND CBSTAT='Y'                                                 
     
    GROUP BY CNDESC,CNCODE,MONTH(WSDATE),CNRATE                    
     ORDER BY CNCODE
    ,MONTH(WSDATE); 

  • #2
    Re: Error 42703

    Where is the
    Exec Sql Close Cursor1

    Not sure but I always do this differently;
    CBSTAT='Y' yours... Mine....
    CBSTAT= :MyYes
    Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

    Comment


    • #3
      Re: Error 42703

      Comment


      • #4
        Re: Error 42703

        PHP Code:
        //OPEN THE FILE VIA SQL                                           
         
        EXEC SQL                                                          
            OPEN CURSOR1
        ;                                                  
                                                                           
         
        //FETCH EACH ROW INTO THE RPG VARIABLES                           
         
        EXEC SQL                                                          
            FETCH CURSOR1 INTO 
        :class_name, :class_number, :month,         
                               :
        workshops, :clientsamt_paid;             
                                                                           
         
        //SQL STATE OF 00000 MEANS A GOOD FETCH OF THE DATA               
         //ACTS AS YOUR DOW NOT %EOF                                       
         
        DOW SQLSTT='00000';                                               
                                                                           
                                                                           
          If 
        class_number <> hldnum and hldnum <> 0;                       
               
        TOTAL1 = %XFOOT(CNT);                                       
               
        TOTAL2 = %XFOOT(CLT);                  
               
        TOTAL3 = %XFOOT(MON);                  
         
        //Get name of counseling                    
              
        CHAIN hldnum CACNM;                    
              
        INITIA =  CNDESC;                      
                                                     
              
        EXCEPT INTNAM;                         
              
        EXCEPT DETAIL;                         
              
        EXCEPT CLIENT;                         
              
        EXCEPT AMT;                            
              
        CLEAR MON;                             
              
        CLEAR CNT;                             
              
        CLEAR CLT;                             
           ENDIF;                                    
                                                     
         If 
        hldnum <> class_number;                  
              
        EXCEPT DOT;                            
           ENDIF;              
             
        X=month;                                                   
            
        mon(X) = amt_paid;                                         
            
        cnt(X) = workshops;                                        
            
        clt(X) = clients;                                          
                                                                       
            
        hldnum class_number;                                     
                                                                       
         
        EXEC SQL                                                      
            FETCH CURSOR1 INTO 
        :class_name, :class_number, :month,     
                               :
        workshops, :clientsamt_paid;         
                                                                       
         
        ENDDO;                                                        
                                                                       
               
        TOTAL1 = %XFOOT(CNT);                                   
               
        TOTAL2 = %XFOOT(CLT);                                   
               
        TOTAL3 = %XFOOT(MON);                                   
          
        //Get name of counseling          
                       
        CHAIN hldnum CACNM;             
                  
        INITIA =  CNDESC;               
                                                  
                  
        EXCEPT INTNAM;                  
                  
        EXCEPT DETAIL;                  
                  
        EXCEPT CLIENT;                  
                  
        EXCEPT AMT;                     
                                                  
            
        EXEC SQL                              
               CLOSE CURSOR1
        ;                     
                                                  
                                                  
            *
        INLR=*ON;                            
                                                  
        /
        END-FREE 

        Comment


        • #5
          Re: Error 42703

          Run this again. Look at the job log when it is in error status, roll back a page and see if there is something there of value.

          Id like to you change the cbstat test too.
          Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

          Comment


          • #6
            Re: Error 42703

            Your Fetch Cursor1 Into statement doesn't have a : in from of amt_paid (both places the statement appears), unless that just happened when posting the code here.

            Comment


            • #7
              Re: Error 42703

              Hi Danba:

              shouldn't this:
              PHP Code:
               EXEC SQL                                                          
                  FETCH CURSOR1 INTO 
              :class_name, :class_number, :month,         
                                     :
              workshops, :clientsamt_paid
              be this:
              PHP Code:
               EXEC SQL                                                          
                  FETCH CURSOR1 INTO 
              :class_name, :class_number, :month,         
                                     :
              workshops, :clients, :amt_paid
              (added : before amt_paid)

              Best of Luck
              GLS
              < e d i t > I see scott beat me to the punch < / e d i t >
              Last edited by GLS400; October 23, 2012, 12:41 PM.
              The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

              Comment


              • #8
                Re: Error 42703

                I'm "piggybacking" onto this post, as I'm getting the same error with the following query:

                Code:
                WITH AssetListCTE as
                (SELECT Mast.FMTAGNUM, Mast.FMLOCATION, Rem.FARMSEQNUM,  Rem.FARMREMARK 
                	FROM			FXALIB.FAMASTER     AS Mast 
                		LEFT OUTER JOIN	FXALIB.FAREMARK     AS Rem 
                				ON Rem.FARMTAGNUM   = Mast.FMTAGNUM)
                SELECT Mast.FMTAGNUM, Mast.FMLOCATION, Rem.FARMSEQNUM, [B]Rem.FARMREMARK[/B] 
                FROM AssetListCTE
                The result: SQL State: 42703, Vendor Code: -206, Message: [SQL0206] Column or global variable FARMREMARK not found.

                Interestingly (frustratingly), if I rearrange the SELECT columns, it's always the last column that's flagged as not found:
                Code:
                WITH AssetListCTE as
                (SELECT Mast.FMTAGNUM, Mast.FMLOCATION, Rem.FARMSEQNUM,  Rem.FARMREMARK 
                	FROM			FXALIB.FAMASTER     AS Mast 
                		LEFT OUTER JOIN	FXALIB.FAREMARK     AS Rem 
                				ON Rem.FARMTAGNUM   = Mast.FMTAGNUM)
                SELECT Mast.FMTAGNUM, Rem.FARMSEQNUM, Rem.FARMREMARK, [B]Mast.FMLOCATION[/B] 
                FROM AssetListCTE
                The result: SQL State: 42703, Vendor Code: -206, Message: [SQL0206] Column or global variable FMLOCATION not found.

                The column _is_ present; the following query runs OK:
                Code:
                WITH AssetListCTE as
                (SELECT Mast.FMTAGNUM, Mast.FMLOCATION, Rem.FARMSEQNUM,  Rem.FARMREMARK 
                	FROM			FXALIB.FAMASTER     AS Mast 
                		LEFT OUTER JOIN	FXALIB.FAREMARK     AS Rem 
                				ON Rem.FARMTAGNUM   = Mast.FMTAGNUM)
                SELECT * 
                FROM AssetListCTE
                FWIW, I get this on green-screen (STRSQL) too. Anthing obviously wrong?

                Comment


                • #9
                  Re: Error 42703

                  FWIW, I found the answer. The error seems to be caused by qualifying the column names in the second SELECT clause. This works OK:
                  Code:
                  WITH AssetListCTE as
                  (SELECT Mast.FMTAGNUM, Mast.FMLOCATION, Rem.FARMSEQNUM,  Rem.FARMREMARK 
                  	FROM			FXALIB.FAMASTER     AS Mast 
                  		LEFT OUTER JOIN	FXALIB.FAREMARK     AS Rem 
                  				ON Rem.FARMTAGNUM   = Mast.FMTAGNUM)
                  SELECT [B]FMTAGNUM, FMLOCATION, FARMSEQNUM, FARMREMARK[/B] 
                  FROM AssetListCTE
                  I'm not sure I understand why this works (or why SQL wants unqualified column names), but then I'm not real familiar with Common Table Expressions. (It's just something I found on the Internet somewhere.)

                  Comment


                  • #10
                    Re: Error 42703

                    Originally posted by Jerry G View Post
                    (or why SQL wants unqualified column names)
                    It's not that it wants unqualified names, but rather that it wants a valid qualifier. In your query, the appropriate qualified name should be AssetListCTE.FARMREMARK since AssetListCTE is what you reference in the FROM clause. You don't have anything in the FROM clause for "Rem" nor for "Mast", therefore they aren't valid qualifiers in that SELECT list.

                    You do have those in the other SELECT, but that's in a different scope.
                    Tom

                    There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

                    Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

                    Comment


                    • #11
                      Re: Error 42703

                      Thanks very much, Tom. This helps my understanding of CTEs a lot!

                      Comment

                      Working...
                      X