Home > IT Answers > AS/400 > SQLRPGLE
Nutangujar
895 pts.
 SQLRPGLE
RPGLE, SQL, SQLRPGLE
I've Report to print using SQLRPGLE

The Report criteria screen has around 15-20 select conditions...based on the conditions entered by the user, I build a query of type

Select * from emp1 where val1 = ? and val2 =? and val3 = ? and...........

This is the first time I'm dealing with so many select criteria in my query. At the point of executing this query, when I open cursor with following statement

C/Exec SQL                                                      C+      Open C1 USING :FLD1, :FLD2, :FLD3, .....                                C/End-Exec                                            

How will I check what fields to pass in Using class. I understand that if the field is not blank or zero then I have to pass it but if 20 fields then how to write this code

Thanks,

NG         



Software/Hardware used:
AS400
ASKED: October 14, 2009  10:05 PM
UPDATED: October 21, 2009  3:17 PM
RELATED QUESTIONS
Answer Wiki:
You need to use variables. C+ Open C1 USING :sqlstmt Prior to this command set the variable Here is an example // set up sql statement sqlStmt = 'select report_date, count(*) ' + 'from sqldlog ' + 'where jobnam = ' + qt + 'STARDBE' + qt + 'group by report_date'; /end-free * c/exec sql c+ PREPARE S3 FROM :sqlStmt c/end-exec +++++++++++++ At the time you are going to run the SQL statement, you should have all the parms the users are entering. So, you need to create the variable using that information. <b>--------------------------------------------------------------------------------------------------------</b> I define the select statement for the query as the <i>file_field = ? or ? = 0</i> (the default value) and "AND" the same for all other selection criteria. This causes it to select the record <ul><li>if the file_field is equal to the filter value</li><li>if the filter value is default AND the rest of the filter criteria selections are true</li> </ul> Then in the "OPEN USING..." I always pass <b>ALL </b>of the criteria <b>TWICE</b>. <i>See example code</i> <pre> d sql_str1 s 573a d inz('SELECT BKSOQD.* FROM BKSOQD, BK- d SOQF WHERE (SDVEND = ? or ? = 0) and- d (SDITEM = ? or ? = 0) and SDVEND =- d SFVEND and SDITEM = SFITEM ORDER BY- d SDIDSC, SDVEND, SDITEM, SDWHSE FOR - d READ ONLY') c/exec sql c+ PREPARE CSR_STR1 FROM :SQL_STR1 c/end-exec c/exec sql c+ DECLARE CSR1 CURSOR FOR CSR_STR1 c/end-exec c openCsr1 begsr c/exec sql c+ OPEN CSR1 USING :C01_FLVEND, :C01_FLVEND, :C01_FLITEM, :C01_FLITEM c/end-exec c endsr </pre> BigKat
Last Wiki Answer Submitted:  October 15, 2009  5:07 pm  by  BigKat   7,185 pts.
All Answer Wiki Contributors:  BigKat   7,185 pts. , CharlieBrowne   32,885 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Thanks. I’m sorry may be I’m not understanding you correctly….
here I’m not sure what variables user will populate at runtime…
can i use ‘Open C1 USING :sqlstmt’ in which sqlstmt is the acutal statement for the cursor…
don’t i need only variables or data structure into it…..

thanks.

Nutangujar
 895 pts.
 

Thanks..I’m googling to understand this process in detail…I’ll be implementing for the first time…will post the link if I find something great…
Thanks for your help…
–Nutan.

Nutangujar
 895 pts.
 

hi BigKat,
that sounds a smart solution…Thanks….
Do you mean that my sql query will always be same and I dont have to change the string
sql_str1
based on the user’s selection criteria…?

Do you see any limitations to this solution?

-Nutan

Nutangujar
 895 pts.
 

Yes, the string is always the same, and the query engine is smart enough to handle the defaulted selection = default value in the query without adding overhead. The downside is the more parameters, the longer the query gets and it is more difficult for someone seeing it for the first time to figure out what you were doing if they don’t have any context for it (like the brief description I wrote before the query)

It still looks rather cryptic even when you know what it is doing..

it helps to see the query not in the d-spec layout.

SELECT BKSOQD.* FROM BKSOQD, BKSOQF 
WHERE (SDVEND = ? or ? = 0) 
  and (SDITEM = ? or  ? = 0) 
  and SDVEND = SFVEND and SDITEM = SFITEM 
ORDER BY SDIDSC, SDVEND, SDITEM, SDWHSE
BigKat
 7,185 pts.
 

Hi BigKat…

The solution works just fine except for the blanks where i have to specify default value as ? = *blanks

I tried many a ways to specify blanks but i received error
is that the limitation of this technique?

Thanks,
Nutan.

Nutangujar
 895 pts.
 

I filter for blanks quite often

SELECT BKSOQD.* FROM BKSOQD, BKSOQF
WHERE (SDVEND = ? or ? = ' ')
  and (SDITEM = ? or  ? = ' ')
  and SDVEND = SFVEND and SDITEM = SFITEM
ORDER BY SDIDSC, SDVEND, SDITEM, SDWHSE
BigKat
 7,185 pts.