Hi all,
From my Question, you will find me as a new user of DS.
I have to load the subset of a source table data into target table but in where condition i want to get data dynamically e.g
SELECT * FROM TABLE1
WHERE TABLE1.COL1 IN (1,2,3,4,5)
I want to get the values of COL1 dynamically from another table TABLE2. How i can to this?
I will appreciate your help.
Thanks and Best Regards,
aachi
Dynamic Where condition from other table/file
Moderators: chulett, rschirm, roy
TABLE2 in the same db as TABLE1? Just use sql:
Otherwise, you got more 'splaining to do.
Code: Select all
SELECT * FROM TABLE1
WHERE TABLE1.COL1 IN
(SELECT X FROM TABLE2
WHERE BLAH-BLAH)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Not in a parameter or in a file, but perhaps in a hashed file or UniVerse table in the project account. Then, to do the lookup, access that hashed file using a UniVerse stage, which uses SQL. Just don't expect it to be fast. It won't be. Indexing the search column will help.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Dynamic Where condition from other table/file
Can you use ODBC (to allow multiple returned results)? I did something of the sort but with a between instead of in... unfortunately that was an old project and we haven't got ODBC set up here for me to try test what I did.userasif wrote: I have to load the subset of a source table data into target table but in where condition i want to get data dynamically e.g
SELECT * FROM TABLE1
WHERE TABLE1.COL1 IN (1,2,3,4,5)
Thanks and Best Regards,
aachi
Use your table2 as the source, and define a number of key columns related to the values to be used to lookup to table1. Alter the SQL within the ODBC stage to be where col1 in (:1, :2, :3, :4, :5). Worked fine for my between but no chance to play to find out.
Having written that, there is a PIVOT stage in Server, extract your columns, PIVOT them to generate multiple rows, then lookup against TABLE1 to return the relevant rows... after lookup, run the update against each row returned...
Sounds like what you are after anyway...