ETL Filter

[gatorfe]

We have a sales order table in our erp system that we are extracting from in DM.  We dont want to extract line 1,3,5 for order number 23565.  What is the syntax to use to accomplish this?  Thank you in advance for any suggestions.

[MFGF]

You can either add a filter expression on the Filter tab of your datastream with an expression such as

(OrderNumber = 23565 and OrderLineNumber not in (1,3,5)) or (OrderNumber <> 23565)

or you could add the same logic as a predictate (WHERE clause) to the SQL of the data source.

Cheers!

MF.

[gatorfe]

Thank you for the input, we prefer to do it at the Query tab of the Data Source Properties.  This is the syntax we have:
select
soi.slord as ORDER_NO,
soi.slline as ORDER_LINE_NO
from
erp.svkincd
where
(slord <> 249486 and slline not in (1,3,5))

When we run it it does not show us any line 1,3,5 at all.  We just dont want to see it for order 249486.  Is there a way to get around that?

[MFGF]

Thank you for the input, we prefer to do it at the Query tab of the Data Source Properties.  This is the syntax we have:
select
soi.slord as ORDER_NO,
soi.slline as ORDER_LINE_NO
from
erp.svkincd
where
(slord <> 249486 and slline not in (1,3,5))

When we run it it does not show us any line 1,3,5 at all.  We just dont want to see it for order 249486.  Is there a way to get around that?

Hi,

I already gave you the logic?

(OrderNumber = 23565 and OrderLineNumber not in (1,3,5)) or (OrderNumber <> 23565)

The only difference is your item names...

where
(slord = 249486 and slline not in (1,3,5)) or (slord<> 249486 )

Cheers!

MF.

[gatorfe]

Sorry about that, I tried your complete logic((slord = 249486 and slline not in (1,3,5)) or (slord<> 249486 )) but its not working.  Before the logic there are 1863 records.  When I add your logic it goes to 20000 rows.  Any ideas why that would be?

[MFGF]

What other filtering is being done in the WHERE clause? You would need to take account of this also...

MF.