Page 1 of 1
Dynamic Where condition from other table/file
Posted: Sat Feb 14, 2009 3:46 am
by userasif
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
Posted: Sat Feb 14, 2009 8:19 am
by chulett
TABLE2 in the same db as TABLE1? Just use sql:
Code: Select all
SELECT * FROM TABLE1
WHERE TABLE1.COL1 IN
(SELECT X FROM TABLE2
WHERE BLAH-BLAH)
Otherwise, you got more 'splaining to do.
Posted: Sun Feb 15, 2009 12:11 am
by userasif
Thanks Graig,
I dont want to use subquery but i want to load these values of subquery in a Parameter or in a file and then want to use that Parameter or file
How?
Posted: Sun Feb 15, 2009 1:06 am
by ray.wurlod
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.
Posted: Sun Feb 15, 2009 2:11 am
by userasif
Thanks Ray, But i am not Premium member.
Posted: Sun Feb 15, 2009 9:07 am
by chulett
In a Sequence, write the values to a file or USERSTATUS in one job. Use a Routine Activity stage to retrieve and pass it as a Job Parameter to the job that needs it.
Posted: Sun Feb 15, 2009 2:40 pm
by ray.wurlod
userasif wrote:Thanks Ray, But i am not Premium member.
That is very easily remedied.
Re: Dynamic Where condition from other table/file
Posted: Tue Feb 17, 2009 1:33 am
by Kryt0n
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
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.
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...