Using keyword IN in Dynamic SQL WHERE clause

0 pts.
Tags:
RPG
SQL
In an RPGLE program I am using embedded SQL. I would like a Where clause that says: WHERE BankCode in (:BankList) and I want BankList to be a dynamic list of banks selected by the user. BankCode is defined as 4,0 decimal and BankList is defined as Character since it may have several banks (1000, 2000, 3000). When I try to compile it I get Comparison operator = operands not compatible. Any ideas on how I would go about this if it is possible at all? Thanks!
ASKED: November 29, 2004  10:39 AM
UPDATED: December 2, 2004  4:00 PM

Answer Wiki

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

Hi

You would need to make use of the PREPARE statement to achieve this.

For example if BankList contains ’0001, 0010, 0137′ you would use something like the following:

Eval Text=’SELECT field1,field2 FROM file WHERE BankCode In(‘ + %Trim(BankList) + ‘)’

/Exec SQL
PREPARE S1 FROM :Text
/End-Exec

/Exec SQL
Declare C1 CURSOR FOR S1
/End-Exec

and then read it as you would normally.

Hope it helps

All the best

Jonathan

Discuss This Question: 9  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
  • Averageprogrammer
    You could just dynamically code your imbedded sql SELECT and use a bunch of where clauses. D MYSTMT S 500A c EVAL MYSTMT = 'SELECT * FROM myfile WHERE' c .......just add you where's and then do the following c/exec sql c+ prepare sel from :mystmt c+ declare mycrsr scroll cursor for sel c+ open mycrsr c/end-exec c .........then fetch and close later when ready If you can't get the IN to work, it is a viable work around.
    0 pointsBadges:
    report
  • StuDog
    If you IN statements looks like Where BankCode IN ('0100', '0200',...) then you will have a problem. Your where statement should look lik Where BankCode IN (0100, 0200, ...) This can be done buy using an edit code in building the IN string. InString = (%edtiC(bankcode(1):'X') + ',' %editC(bankCode(2):'X') + ','....) Of course you can use a do loop to handle this.
    0 pointsBadges:
    report
  • WaltZ400
    When you use WHERE BankCode In (1000,2000,3000) you are comparing a numeric field against a list of numeric values. When you coded WHERE BankCode in (:BankList) you are actually comparing a numeric field to a character field and that is the reason for the error. Your best bet is to create a variable that contains the entire SQL statement. Concatentate the character form of the bank codes separated by commas within the statement. For example: Eval SQL_STATEMENT = 'Update File Set Field = Value Where BankCode In (' + Bnk1 + ',' + Bnk2 + ',' + Bnk3 + ')' Then to run the UPDATE all you need to do is execute the following: EXECUTE IMMEDIATE :SQL_STATEMENT If you are using a FETCH to read all the records and your SQL statement you prepared was a SELECT, you would use a PREPARE statement like so: PREPARE S1 From :SQL_STATEMENT Then when you declare the cursor just before you open the cursor for your FETCH loop, execute the declare like this: DECLARE CursorName CURSOR FOR S1 OPEN CursorName Hope this helps....
    655 pointsBadges:
    report
  • BigKat
    You will need to build a character string concatenating the individual values into the IN clause, then either use a PREPARE statement FROM :string and then DECLARE or EXECUTE it or just EXECUTE IMMEDIATE :string.
    7,585 pointsBadges:
    report
  • Micjones
    As a rule, I only declare variables as numeric only if they're used in calculations. Otherwise, I define them as text. BTW, how are you having users create BankList?
    0 pointsBadges:
    report
  • carlosdl
    The compiler is right because BankCode is numeric and BankList is character type. I have not worked with RPGLE and I don't know if there is a way to build dynamic sql queries. If so, you must build your query in a string and then prepare and execute it. Something like this: myQuery = 'select .... where BankCode in (' + BankList + ')' And then prepare and execute myQuery. I'm using '+' to concatenate strings, you use the correct operator if it is not. If you use 'in', then the BankList must contain at least one bank code.
    65,110 pointsBadges:
    report
  • RolandT
    Assuming that you have a managable number of possible elements, then you could use: Bankcode in (:bank1, :bank2, :bank3, :bank4, ....) and Bankcode not = 0
    0 pointsBadges:
    report
  • naturegal
    Thank you for your help everyone! I was able to get this to work using the Prepare statement.
    0 pointsBadges:
    report
  • spintreebob
    1) Try defining the members of the in-list as decimal: WHERE DECIMAL-BANK-CODE IN (1000,2000,3000) and the 1000, 2000,3000 are decimal numbers displayed to humans as unpacked. The commas in such a list are understood to be part of the language and not part of the numeric data. Likewise in character data ('1000','2000','3000') the quotes and commas are part of the language; not part of the data. If the data is defined as character datatype, then it should be represented as character inside quotes. It is best to compare like datatypes... decimal to decimal and not decimal to character. 2) Size 4,0 is probably a quick example and not the real length of a bank code. If the length of the bank code could be an odd number such as 5,0 or 7,0, etc the Decimal datatype will find it more friendly. 3) Since Java might not handle DECIMAL, consideration might be given to using SMALLINT or INTEGER which can be handled by all languages, even Java. For better or worse, Java is the flavor of the month.
    0 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