Data Integration

 View Only
  • 1.  CP4D DataStage 4.0.5 - Filter Stage

    Posted Fri March 11, 2022 09:58 AM
    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


  • 2.  RE: CP4D DataStage 4.0.5 - Filter Stage

    IBM Champion
    Posted Mon March 14, 2022 01:48 AM
    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
    ------------------------------



  • 3.  RE: CP4D DataStage 4.0.5 - Filter Stage

    Posted Mon March 14, 2022 07:45 PM
    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
    ------------------------------