SQL0312 - not found or not usable

[64waves]

Good afternoon, trying to build a report program with embedded SQL and it won't see my work variable in my statement, SQL0312 -not found or not usable. It is field @Comp (for Company) . I send in 20160401, 20160415, 'HLT', 'Test Company Name'.

( Side Note - I have already tried to post all the field names in the SQL statement, and the statement only goes to length 1021 in debug, it won't fit all the text.)



here is my code:

 *                                    
D SQLRange        S             50    
D SQLStmt         S            500    
 *                                    
 *                                                           
D @Cnt            S              3  0                        
D @X              S              3  0                        
D X               S              3  0                        
D @Comp           S              3a                          
D @CName          S             30a                          
 *                                                           
 * --------------------------------------------------------- 
D Main            pr                  extpgm('PR3501A')      
D PRFromDate                     8s 0                        
D PRToDate                       8s 0                        
D PRCompany                      3a                          
D PRCompanyname                 30a                          
                                                             
D Main            pi                                         
D PIFromDate                     8s 0                        
D PIToDate                       8s 0                        
D PICompany                      3a                          
D PICompanyname                 30a                          
                                                             
                                                                              
                @Comp    = PICompany    ;                                     
                PCompany = PICompany    ;                                     
                Pconame  = PICompanyname;                                     
                PFRDATE   = %dec(%char(%date(PIFROMDATE:*iso):*mdy0):6:0);    
                PTODATE   = %dec(%char(%date(PITODATE:*iso):*mdy0):6:0);      
                Write Header;                                                 
                                                                              
                                                                              
 /End-free                                                                    
 * Declare the pointer                                                        
C/Exec SQL                                                                    
C+ DECLARE HDRCSR SCROLL CURSOR FOR REFSTMT                                   
C/End-Exec                                                                    
 *                                                                            
 /Free                                                                        
        SQLStmt = 'Select * +                                                 
                     from PRCHGLOG where LCOCDE = :@Comp and +                
                       LCHGTYP <> ''APP'' and LCHGDT >= :PIFromDate and +     
                         LCHGDT <= :PITodate' +                          
                           ' ORDER BY LPORT, LTIMESTAMP ';               
                                                                         
 /End-free                                                               
C/Exec SQL                                                               
C+ PREPARE REFSTMT FROM :SQLStmt                                         
C/End-Exec                                                               
                                                                         
 * Open the pointer                                                      
C/Exec SQL                                                               
C+ OPEN HDRCSR                                                           
C/End-exec                                                               
         
...............                                                                

Any help would be appreciated. Thank you.

[64waves]

Re: SQL0312 - not found or not usable

Ok, I got the first parm to work by doing this in the statement:

'''+ @Comp +''' ,

now I am trying to get the 8,0 fields to work


Thanks.

[64waves]

Re: SQL0312 - not found or not usable

I got past it, used Range:

     SQLRange = 'LCHGDT between ' +     
              %Char(pifromdate) +       
              ' and ' +                 
              %Char(pitodate) ;         
                                        
                                                                
  SQLStmt = 'Select * +                                         
        from PRCHGLOG where LCOCDE =  '''+ @Comp +'''  and +    
        LCHGTYP <> ''APP'' and ' + %trim(SQLRange) +            
                ' ORDER BY LPORT, LTIMESTAMP ';                 
                                                                                                                          

[B.Hauser]

Re: SQL0312 - not found or not usable

Why bother do you want to use with dynamic SQL?
In your examples it is not necessary at all.

And never ever use variables in (embedded) SQL Statements that start with SQL or SQ, because those variables are reserved for the SQL precompiler, i.e. SQLCA.
Even though your statement works today correctly, with the next release IBM may add a variable in the SQLCA with the same name as your host variable, which may cause a lot of trouble.

And avoid special characters (such as @, $, #) in variable names because these are NOT international. In my German source code I cannot use the @ sign, but have to use the § sign instead.

Birgitta

[64waves]

Re: SQL0312 - not found or not usable

Thanks for the info Birgitta, I will adjust my code to avoid any issues.

[kitvb1]

Re: SQL0312 - not found or not usable

To add to what little Biggie said:
Furthermore, on a German keyboard, a ?@? character is typed by pressing the AltGr + Q. In RDi, this brings up an option to open a source member and cannot be typed. It has to be copied and pasted from another application.

[DeadManWalks]

Re: SQL0312 - not found or not usable

You also dont have to step out of /free to issue the SQL commands.