BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
spuckle
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
RLigtenberg
SAS Employee

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

spuckle
Calcite | Level 5

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.

RLigtenberg
SAS Employee

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

RLigtenberg
SAS Employee

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...

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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