could you tell me how to implement SQL "not exist" in DS?
thank you!
Please let me know how to implement "not exist" in
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
Using the Dynamic RDBMS stage you simply add your NOT EXISTS statement into the WHERE clause of the stage properties. You can also add it to the other database stages using user-defined SQL.
Using parallel stages, and not trying to do such an intensive statement on the RDBMS server, you could use the lookup stage with a condition that sends the non joining rows down a reject link. This gives you your NOT EXISTS rows. You can take a similar approach with the join stage where a left outer join brings back both the joins that exist and those that do not exist and a filter stage lets you divert those rows where the joined fields are NULL.
Using parallel stages, and not trying to do such an intensive statement on the RDBMS server, you could use the lookup stage with a condition that sends the non joining rows down a reject link. This gives you your NOT EXISTS rows. You can take a similar approach with the join stage where a left outer join brings back both the joins that exist and those that do not exist and a filter stage lets you divert those rows where the joined fields are NULL.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
vmcburney wrote:Using the Dynamic RDBMS stage you simply add your NOT EXISTS statement into the WHERE clause of the stage properties. You can also add it to the other database stages using user-defined SQL.
Using parallel stages, and not trying to do such an intensive statement on the RDBMS server, you could use the lookup stage with a condition that sends the non joining rows down a reject link. This gives you your NOT EXISTS rows. You can take a similar approach with the join stage where a left outer join brings back both the joins that exist and those that do not exist and a filter stage lets you divert those rows where the joined fields are NULL.
thanks your reply, it works now