How to write SQLRPGLE query with many select criteria

895 pts.
Tags:
RPGLE
SQL
SQLRPGLE
I have a 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?        



Software/Hardware used:
AS400

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Nutangujar
    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.
    895 pointsBadges:
    report
  • Nutangujar
    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.
    895 pointsBadges:
    report
  • Nutangujar
    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
    895 pointsBadges:
    report
  • BigKat
    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
    
    8,210 pointsBadges:
    report
  • Nutangujar
    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.
    895 pointsBadges:
    report
  • BigKat
    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
    
    8,210 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following