SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DI Studio - UNION

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

DI Studio - UNION

[ Edited ]

Hi,

 

I have a couple of problems I need some help with:

 

I've two tables with identical in columns that I want to UNION together - I'm using the SQL JOIN transformation, however I'm stumped on the Mappings tab.  It appears you can only map one of the sources to the target, thus ignoring that it is a UNION. 

 

I am also having issues with a CASE statement in the Target table - the CASE wizard allows you to select a column in the target table, however this errors when running.  The error states an ambiguity as the same column name is in the two source tables even though I choose the target as the source.

 

I'm using DI Studio 4.9

 

TIA,

 

Gordon

 


Accepted Solutions
Solution
‎01-08-2018 12:49 PM
SAS Employee
Posts: 15

Re: DI Studio - UNION

Posted in reply to RobertLigtenberg

Here is some documentation.

 

The union in the join transformation performs a Union Join: http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#p1bk7i6jqseje7n1li...

 

The union in the Set Operators transformation performs a “stacking” of tables and is probably what you are looking for: http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n0vo2lglyrnexwn14e...

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,227

Re: DI Studio - UNION join

May I start by suggesting a look at SQL and what that means:

https://www.w3schools.com/sql/

As your use of UNION and JOIN makes no sense.  They are two very different means of putting data together.  Union is the process of putting data underneath another dataset, a join means to merge the data together by means of identifying variables.

 

With this: "The error states an ambiguity as the same column name is in the two source tables even though I choose the target as the source." - what this means is that the variable appears in both tables, and the compiler cannot decide which to take.  I don't know DI and its UI, but in SQL you would assign an alias to each table and then utilse the alias to indicate to the compiler which to take:

select case when A.ID=1 then 1 else 2 end as RESULT
from MYTABLE A

The A after the dataset here is the alias.

New Contributor
Posts: 2

Re: DI Studio - UNION

[ Edited ]

I'm familiar with how to do it in SQL - in fact it's what I'm rebuilding in DI (for data lineage purposes).

 

UNION is under the SQL "Join" transformations in DI StudioDI Union.JPG

 

The SELECT node above is where columns are mapped, obviously in a UNION we have both sources populating a single column in the target table, which DI appears not to allow.

Highlighted
SAS Employee
Posts: 15

Re: DI Studio - UNION

The SQL Union is available in the "Set Operators" transformation which is in the "SQL" group on the Transformations tab.

Solution
‎01-08-2018 12:49 PM
SAS Employee
Posts: 15

Re: DI Studio - UNION

Posted in reply to RobertLigtenberg

Here is some documentation.

 

The union in the join transformation performs a Union Join: http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#p1bk7i6jqseje7n1li...

 

The union in the Set Operators transformation performs a “stacking” of tables and is probably what you are looking for: http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n0vo2lglyrnexwn14e...

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 343 views
  • 3 likes
  • 3 in conversation