Dynamic operator on WHERE statement

50 pts.
Tags:
IBM iSeries
SQLRPGLE
I have a file that has 2-fields that will be used to select records from file B. File A, field 1 = date_a & field 2 = Operator. I want to select records from File B and based on the value within the operator ( EQ, LT, LE, GT, GE) and the date_a. Ex: If in File A, the value of the field Operator is: 'GE' and the Date_A is 20130410, then the outcome will be this: Translate: Where Date_A >= Date_B - OR if the value of Operator is 'EQ', then Where Date_A = Date_B I need to use all 5-operator values in the WHERE Statement. Is this doable? Dynamic? Thank you Experts!

Software/Hardware used:
SQLRPGLE - ISERIES

Answer Wiki

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

Discuss This Question: 12  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
  • TomLiotta
    Yes, dynamic. . It doesn't seem like worth doing though. . Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Dynamic sql would work but it takes a chunk of run-time processing. Alternative, in this case, would be a select statement with 5 static SQL statements. Since this could return more than one row it needs to create a cursor. Phil
    49,960 pointsBadges:
    report
  • TomLiotta
    Five static SQL statements won't do it unless there is only ever a single column to compare. If that's the case, then there's no need to have the column name be variable. . The number of static statements would be the number of operators (5) times the number of columns. If there are three columns, then 15 static statements are needed. . I almost posted essentially the same advice except with a single static SQL statement with a basic :hostvariable, but I re-read the question and remembered the variable columns. So I posted this comment instead. . Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    I guess I'm having trouble reading the original request .. still looks to me like... The request file has a date and an operator 20100101 GE 20110303 EQ and the only difference in the SQL statements are based on the Operator I think that the 5 sql statements could be reduced to 1. Either with a case statement .. although I'm having a bit of a problem with that. Or A Union .. which contains 5 sql statements .. 4 of which return no records for any specific case. Phil
    49,960 pointsBadges:
    report
  • BigKat
    this isn't exactly you're solution, but it should show you how to go about it. If I can get it to paste in reasonably well. [code] H indent('|') option(*nodebugio:*srcstmt) ********************************************************* * Data Conversion -- Convert from older numbering formats * to new format of XXnnnnnnnn by inserting "leading" * zeros after the inital two alpha characters. ********************************************************* * Convert XXnnnnn to XX000nnnnn * Convert XXnnnnnn to XX00nnnnnn * Convert XXnnnnnnn to XX0nnnnnnn ********************************************************* * Note: this must be done to converted field in ALL * existing system files. ********************************************************* * Kevin C. Ketzler -- Affiliated Resource Group ********************************************************* D wk_fld s 10a D field s 10a D file s 10a D stmt s 32765a C eval file = 'filename1' C eval field = 'fieldname1' C exsr convert C eval file = 'filename2' C eval field = 'fieldname2' C exsr convert C eval file = 'filename3' C eval field = 'fieldname3' C exsr convert C eval *inlr = *on ********************************************************* C convert begsr C exsr PrepUpd C exsr OpenCsr C exsr FtchCsr * When compiled as SQLRPG or SQLRPGLE, two data structures * are automatically created (SQLCA and SQLDA) and SQLCOD * is part of SQLCA. The actual compiled code replaces the * SQL statements with code to use the data structures and * the API's to interface with the query optimizer. C dow sqlcod = 0 * manipulate the wk_fld string to insert '0' after first * two characters until string is ten characters long. C if %subst(wk_fld:10:1) = ' ' C dou %subst(wk_fld:10:1) <> ' ' C eval wk_fld = %subst(wk_fld:1:2) + '0' + C %subst(wk_fld:3:7) C enddo C exsr ExecUpd C endif C exsr FtchCsr C enddo C endsr ********************************************************* C OpenCsr begsr C eval stmt = 'SELECT ' + %trimr(field) C + ' FROM ' + %trimr(file) C + ' FOR UPDATE OF ' + %trimr(field) C/exec sql C+ PREPARE CSR_STR FROM :stmt C/end-exec C/exec sql C+ DECLARE CSR CURSOR FOR CSR_STR C/end-exec C/exec sql C+ OPEN CSR C/end-exec C endsr ********************************************************* C FtchCsr begsr C/exec sql C+ FETCH CSR INTO :wk_fld C/end-exec C endsr ********************************************************* C ClosCsr begsr C/exec sql C+ CLOSE CSR C/end-exec C endsr ********************************************************* C PrepUpd begsr C eval stmt = 'UPDATE ' + %trimr(file) C + ' SET ' + %trimr(field) + ' = ?' C + ' WHERE CURRENT OF CSR' C/exec sql C+ PREPARE UPD_STR FROM :stmt C/end-exec C endsr ********************************************************* C ExecUpd begsr C/exec sql C+ EXECUTE UPD_STR USING :wk_fld C/end-exec C endsr ********************************************************* [/code]
    8,210 pointsBadges:
    report
  • BigKat
    OMG it pasted in clearly! I did an HTML to drop to a new line and I remembered this compiler used [ c o d e] and [ / c o d e ] (without the extra spaces) to layout the programming portion
    8,210 pointsBadges:
    report
  • BigKat
    but it misread the HTML when I tried to space it out to tell what I did: > br <
    8,210 pointsBadges:
    report
  • BigKat
    argghhh! <br>
    8,210 pointsBadges:
    report
  • TomLiotta
    My understanding was that the "driver" file had a ColumnName column and an Operator column. That would mean that one row might contain: EMPNAME GE And a second row might have: DATE EQ . The two resulting clauses would be: WHERE A.EMPNAME >= B.EMPNAME and WHERE A.DATE = B.DATE . The first driver file column contains the name of a column rather than a value. The two files, fileA and fileB both have column names that correspond to the possible values in ColumnName. . But the OP needs to clarify. . Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Okay, I've read it again, and I see what Phil is saying, and I'm back to what I thought the first time I read it. (I think.) Assuming that File_A always contains a DATE value in the first column, instead of a field name such as DATE_A, a single static SQL statement can work.
    Where ( Operator_A = 'EQ' and Date_A = Date_B )
       or ( Operator_A = 'GT' and Date_A > Date_B )
       or ( Operator_A = 'LT' and Date_A < Date_B )
       or ( Operator_A = 'GE' and Date_A >= Date_B )
       or ( Operator_A = 'LE' and Date_A <= Date_B )
    The question could still use some clarity perhaps. But dynamic isn't needed if the description of FileA is understood. The business purpose would be better to describe the need than a technical description.
    .
    Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Tom Looks to me like you've got it. Phil
    49,960 pointsBadges:
    report
  • ibBusy4sure

    Thank you all for your support!

    Tom's response on  May 18, 2013 7:52 AM (GMT) is exactly what i am after.  Thank you Tom.  

    50 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