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.
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.
You need to double up the quotation marks.
If you say
TRIM(A.FLD1) = ''
in prepare you would type:
TRIM(A.FLD1) = '' ''
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.
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,
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.