Dynamic Select with "IN"

[Guest.Visitor]

Your cursor definition looks okay but I didn't see any code to open, fetch and close the cursor.

[bibarnes@yahoo.com]

The fetch is in the program as well as the open and close of the cursor. The fetch returns and EOF on the first time. The problem is in the SELECT_IN field but I don't know what the problem is. Thanks for the help. I am going to do one more thing and look at the hidden fields that SQL creates that don't show up in a debug listing. maybe that will shed some light on this. Bill

[dacust]

How is the SELECT_IN field defined? When you describe the contents, does it include the quotes and the commas? I bet it has something to do with the way SQL is putting the statement together, and is probably looking at it like it is one string instead of seperate values. Something like that? Maybe try doing it with a dynamically prepared statement and cat the value of the SELECT_IN field into the statement.

[DougCMH]

I don't think you can use a host variable for IN that way. You would need a separate host variable for each value in the IN clause. Your best bet would be to construct the SQL statement with SELECT_IN as is, and prepare it using dynamic SQL.

[bibarnes@yahoo.com]

I will accept that it doesn't work that way. What I need to know now is how do I construct the dynamic SQL statement. The problem is this is a small shop and we just got SQL. I know more than the rest of the shop and that's scary. I appreciate the info. Bill

[Guest.Visitor]

Use a prepare statement with a string variable. The sting variable will have to the actual values in it e.g. 'txdate between 20040201 and 20040229' The :select_in can be created seperaterly and added in like this: string = string1 + select_in + ')' C/exec sql C+ Prepare P1 from :String C/end-exec C/exec sql C+ declare c1 cursor from P1 C/end-exec The dynamic is in defining the string variable.

[dacust]

The following code snippet shows the basic command. Source type should be SQLRPGLE (or SQLRPG). The SQL syntax is the same for both. You can also use the "fetch" SQL command to read records one at a time for RPG processing. The field :sqfld1 was defined as 1024 long and is the variable that has the command to execute. It was built with a string of constants and had variables cat'd amongst them. If this isn't enough, I can post the whole program, it doesn't have too much extraneous code, about 250 lines.

---

Code

[bibarnes@yahoo.com]

Stuart; If it wouldn't be too much trouble I would appreciate the whole enchilada. I think I understand what you are saying, but I am a good plagerizer. TIA Bill Barnes

[dacust]

Well, you asked for Stuart's, but here's mine just in case it helps. -dan

---

Code

[Guest.Visitor]

This is based on the book "Desktop Guide to SQL" by James Coolbaugh

---

Code