Dynamic Where condition from other table/file

[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

[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.

[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?

[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.

[userasif]

Thanks Ray, But i am not Premium member.

[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.

[ray.wurlod]

Thanks Ray, But i am not Premium member.

That is very easily remedied.

[Kryt0n]

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...