help converting in Sql to BODS task

dataservices job design
1

H
I need help in converting below query in SQL to BODS task

INSERT INTO User_SPMapping
(Username,UserEmail,UserID, SPID, SPName, timestamp)
Select Username,UserEmail,UserID, SPID, SPName, timestamp
From Salesperson inner join User ON Username = SPName
Where SPID Not exists ( Select DWSPID From User_SPMapping)

Steps BODS

Step 1
Source tables User and Salesperson
----- Query Transform1 (got the matching records)

Step2
---------Query transform2 in Where clause ( NOT (Query1.SPID) IN
(User_SPMapping.DWSPIID )
--------------User_SPMapping

Step 3 : Step 2 result to Temp table

This is not working I am not getting result correctly from Step2 not exist

Any help will great, thanks in advance.

ThanksForHelp (BOB member since 2006-09-25)

2

My understanding is that you want to enrich / read the DWSPID in case the SPID is empty.
Is that correct?

in that case i would do a case transform with 2 rules (SPID is not null and SPID is null) after Query Transform 1.

The SPID it not null will just be passed through a query to a merge transform.

the SPDID is null will be joined in another query with table User_SPMapping (left outer join) and enrich the DWSPID.

Then connect this query also with the merge and then with target table.

barthodo :de: (BOB member since 2012-04-18)

3

The Dataflow cannot do a NOT EXISTS using simply, native functionality. But you can make it do it by using the pushdown_sql() function. Code the entire NOT EXISTS expression and the subquery within the pushdown_sql() and it will work.

eganjp :us: (BOB member since 2007-09-12)