Link to home
Create AccountLog in
Avatar of MilburnDrysdale
MilburnDrysdale

asked on

DB2 Stored Procedure - use IN variable in global temp SQL

Hello - I have used stored procedures for awhile now with success that pass variables from a web page back to the SP to filter results. This works as long as the parameters get passed in a SQL statement within a cursor. The issue I am now having is how to make the IN variable "available" to a SQL statement that builds a global temporary table (not in a cursor statement). Here is an example;

CREATE PROCEDURE LTL400TAF1/HD_OPEN (IN startdat DATE, IN enddat DATE)      
 RESULT SETS 1                                                              
 LANGUAGE SQL                                                               
                                                                            
 P1 : BEGIN                                                                 
                                                                            
DECLARE GLOBAL TEMPORARY TABLE BILLS1 AS                                    
(SELECT PUDATE,  PANUM, PATID, PAPCS, PAWGT                                              
FROM DSP090                                                  
WHERE PASTS<>'0' AND PABCD IN ('0259788','0264682')   
AND PUDATE BETWEEN startdat AND enddat)                        
WITH DATA WITH REPLACE;                                                     

Open in new window


The error I'm getting is "startdat is not in table DSP090".
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

try

CREATE PROCEDURE LTL400TAF1/HD_OPEN (IN startdat DATE, IN enddat DATE)      
 RESULT SETS 1                                                              
 LANGUAGE SQL                                                              
                                                                           
 P1 : BEGIN                                                                
                                                                           
DECLARE GLOBAL TEMPORARY TABLE BILLS1 AS                                    
(SELECT PUDATE,  PANUM, PATID, PAPCS, PAWGT                                              
FROM DSP090                                                  
WHERE PASTS<>'0' AND PABCD IN ('0259788','0264682')  
AND PUDATE BETWEEN :startdat AND :enddat)                        
WITH DATA WITH REPLACE;
Avatar of MilburnDrysdale
MilburnDrysdale

ASKER

momi_sabag - thanks for the reply. I had already tried that...here is the error I get with that syntax;

Token : was not valid. Valid tokens: <IDENTIFIER>.
which db2 and which version are you using?
OS:V5R4
Avatar of Member_2_2484401
I believe the problem stems from the fact that you can't use a variable in the DECLARE GLOBAL TEMPORARY TABLE statement.

As a work-around, you can declare the temporary table by specifying the column definitions manually ... and then inserting into the temporary table.

HTH,
DaveSlash
ASKER CERTIFIED SOLUTION
Avatar of Member_2_2484401
Member_2_2484401
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi,

Try a dynamic statement. In LUW is like this:

CREATE PROCEDURE LTL400TAF1_HD_OPEN (IN startdat DATE, IN enddat DATE)      
 RESULT SETS 1                                                              
 LANGUAGE SQL                                                               
                                                                            
 P1 : BEGIN

DECLARE STMT STATEMENT;

PREPARE STMT FROM 'DECLARE GLOBAL TEMPORARY TABLE BILLS1 AS '                                   
  || '(SELECT PUDATE,  PANUM, PATID, PAPCS, PAWGT '                                             
  || 'FROM DSP090 '                                                 
  || 'WHERE PASTS<>''0'' AND PABCD IN (''0259788'',''0264682'') '  
  || 'AND PUDATE BETWEEN ? AND ?) '
  || 'DEFINITION ONLY '
  || 'WITH REPLACE';

EXECUTE STMT USING startdat, ENDDAT;

end p1@

Open in new window

Hello AngocA - tried your solution and got the following;

STATEMENT in *LIBL type *SQLUDT not found.
As I said, it works in LUW, but it seems that you are using DB2 z/OS or iSeries, and I am not an expert in those domains. However, try to see how a dynamic statement can be written in a stored procedure in the DB2 you are using, and in this way you could execute any kind of query.
daveslash - Thanks! This approach worked for me, but I still don't understand why the IN variable cannot be used in a Global Temp table statement...