DI Studio - union - How to product separate select statements

[duaneep1]

This has been raised previously, marked as solved, but really wasn't.

The SAS DI join transformation has a union option.  This should allow multiple selections and where clauses to produce a single result.  I can find no way to do this.

If I want a list of distinct names from two tables the SQL it should produce would look like:

Select name from employee where dept = 'abc'

union

select name from prospect where city = 'denver'

It only allows one map, one select, and one where clause.

I don't really want to use the set operator as I then want this to be a sub-query in the join transformation, not separate.

How can I do this?

Thanks.

[Patrick]

The SQL JOIN Transformation lets you define a UNION JOIN. This is NOT the same like a UNION set operation. 

Please refer to the discussion here and the links provided in the answer marked as solution.

If you really want to go for a UNION JOIN configure your SQL Transformation in a way so it creates code as follows:

select distinct
  coalesce(l.name,r.name) as name 
from 
  employee l 
  union join
  prospect r
where l.dept = 'abc' or r.city = 'denver'

Alternatively - and easier to setup and maintain imho - use the SQL Set transformation and create a VIEW as target. Then use this view in your SQL Transformation.

This way the SQL Set generated logic will only get executed as part of the code generated by your SQL Join transformation.