Hello,

I am using CP4D DataStage 4.0.5. I am trying to find out if I can use datastage to do the sql statement

WHERE STATE IN ('TX', 'IL', 'OK', 'FL')

Is there a way in DataStage to do this filter? I tried to use the language in the Filter stage but I get an error.

------------------------------
Vaibhav Chattree
------------------------------

#DataIntegration

In filter stage in where you have to avoid WHERE clause. I didn't remember if IN is supported - you could change it into STATE='TX' OR STATE='IL' …

For complex filtering you should use constraints in transformer. You have to use DS function instead of SQL but IN clause could be simulated by

Index('~TX~IL~OK~FL~','~':link_name.STATE:'~')>0

Regards
Marcin

------------------------------
Marcin Molak
------------------------------

Hi

I will use Lookup Stage to allow no hard coding.

Lookup table will store all the codes you need and you can add or remove codes by just insert/update/delete the rows in the table with no impact to your ETL jobs.

IN = Use the Normal Link
NOT IN = Use the REJECT Link

------------------------------
HOW MING YONG
------------------------------