Midrange News for the IBM i Community


Posted by: Ajit B
Dynamic SQL
has no ratings.
Published: 04 Oct 2012
Revised: 23 Jan 2013 - 1729 days ago
Last viewed on: 17 Oct 2017 (3262 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Dynamic SQL Published by: Ajit B on 04 Oct 2012 view comments(4)

Declare C1 Scroll Cursor for                              
(Select A.FLD1, A.FLD2, '  ' AS XXX,                    
DATE(01/01/01), '        ', '   ', '   ', CURRENT TIMESTAMP       
from FILE1 A WHERE A.FLD1 >= Trim(:Position1) and          
       (:Filter1 = ' ' or TRIM(A.FLD1) Like TRIM(:Filter1)) and
       (:Filter2 = ' ') And                                       
       (:Mode ='A'  or  :Mode='B'))                               
UNION                                                             
(Select A.FLD1, A.FLD2, A.FLD3,                          
DATE(A.TIME), CHAR(TIME(A.TIME)), A.FLD4,                   
A.FLD5, A.FLD6                                                
FROM FILE2 A WHERE A.FLD1 >= Trim(:Position1) and          
       (:Filter1 = ' ' or TRIM(A.FLD1) Like TRIM(:Filter1)) and
       (:Filter2 = ' ' or TRIM(A.FLD4) Like TRIM(:Filter2)) and 
       (A.FLD5   = 'XXX' Or A.FLD5 = 'YYY' Or :Mode = 'C'))   
Order By 1 For fetch only  

--- above code works perfectly well with Declare statement. But if I want to use it with SQL Prepare  to buid a dynamic SQL statement ... it wont. How to buid the text statement, particulary how to include :Filter1 = ' ' in it. (Position1 and Filter1 are subfile Position and Filter fields. Thanks in advance.

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Dynamic SQL
Posted: 5 years 14 days 1 hours 20 minutes ago

You need to double up the quotation marks.

If you say

TRIM(A.FLD1) = ''

in prepare you would type:

TRIM(A.FLD1) = '' ''

 

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Dynamic SQL
Posted: 5 years 14 days 33 minutes ago
Edited: Thu, 04 Oct, 2012 at 10:58:41 (1840 days ago)

I think you're also going to need parameter markers in place of the host variables. Simplified version:

// Stmt is a normal char variable of sufficient length, such as 200A.
// You could use INZ, but you're statement is pretty long; calc is easier
Stmt = 'SELECT A.FLD1, A.FLD2'
     + ' FROM FILE1 A'
     + ' WHERE A.FLD1 >= TRIM(?)'
     + ' AND (? = '' '' OR TRIM(A.FLD1) LIKE TRIM(?))';

Exec SQL PREPARE S1 FROM :Stmt;

Exec SQL DECLARE C1 SCROLL CURSOR FOR S1;

Exec SQL OPEN C1 USING :Position1, :Filter1, :Filter1;

The parameter marker takes the place of the host variable in your SQL statement. The USING on the OPEN (in this case) supplies the host variables to replace the parameter markers. You need to supply a field for each '?' in the prepared statement. And, no, you can't reuse them even if the same host variable is needed more than once. Parameter markers are replaced by the USING fields in order, from left to right.

Posted by: ajitb400
Premium member *
Comment on: Dynamic SQL
Posted: 5 years 14 days 30 minutes ago

I want to build a text string for above SQL Union and then want to use SQL Prepare. Position1, Filter1 and Filter2 are fields on my Display file (Subfile position and filter fields),

(Bnk are blank fileds)

 

SQL1 = '(Select A.FLD1, A.FLD2,' + Quote + Bnk2 + Quote +
' AS XXX, DATE(01/01/01),' + Quote + Bnk8 + Quote + ',' +   
Quote + Bnk3 + Quote + ',' + Quote + Bnk3 + Quote +            
', CURRENT TIMESTAMP from FILE1 A WHERE A.FLD1 >= ' +   
'Trim(:Position1) and (:Filter1 = ' + Quote + Bnk1 + Quote +   
' or TRIM(A.FLD1) Like TRIM(:Filter1)) and (:Filter2 = ' +  
Quote + Bnk1 + Quote + ') and :Mode = ' + Quote + cE + Quote + 
' or :Mode = ' + Quote + cP + Quote + '))';                    

 

SQL2 = '(Select A.FLD1, A.FLD2, A.FLD3, DATE(A.FLD4),' +
' CHAR(TIME(A.FLD4)), A.FLD5, A.FLD6, A.FLD7 ' +          
'FROM FILE2 A WHERE A.FLD1 >= Trim(:Position1) and ' +     
'(:Filter1 = ' + Quote + Bnk1 + Quote +                           
' or TRIM(A.FLD1) Like TRIM(:Filter1)) and ' +                 
'(:Filter2 = ' + Quote + Bnk1 + Quote +                           
' or TRIM(A.FLD5) Like TRIM(:Filter2)) and ' +                  
'(A.FLD6 = ' + Quote + cPND + Quote +                           
' or A.FLD6 = ' + Quote + cREP + Quote + ' Or :Mode = ' +       
Quote + cH + Quote + '))';                            

 

SQLST = %TRIM(SQL1) + ' UNION ' + %TRIM(SQL2)      

                                                     
EXEC SQL PREPARE MAINSELECT FROM :SQLST;                 
EXEC SQL DECLARE MAINCURSOR SCROLL CURSOR FOR MAINSELECT;
EXEC SQL OPEN MAINCURSOR;                                
EXEC SQL FETCH NEXT FROM MAINCURSOR INTO :DS1;                  

Giving -501 error,

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Dynamic SQL
Posted: 5 years 13 days 20 hours 59 minutes ago

Are you allowed to use a field named SQLST? That is so close to SQLSTT (SQL State) that I wouldn't use a field that begins with the letters "SQL" if I were you.