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

Help with simple SQL Join expression

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

Help with simple SQL Join expression

Working in an SQL Join transformation in DI Studio, I need an expression which says that "All fields in this column should have the same value as the field where Current=1".

Only the rows with Current=1 has "disability_flag" filled out, and I need to copy it to those with Current=0.

In example, this input table:

PKDisability_FlagCurrent
10
111
20
20
201

Should become this output table:

PKDisability_FlagCurrent
110
111
200
200
201

For some reason I can't think of what expression to use. I'd appreciate any advice, thanks.


Accepted Solutions
Solution
‎01-08-2013 04:19 AM
Super User
Posts: 5,435

Re: Help with simple SQL Join expression

Posted in reply to TurnTheBacon

Join the table onto itself (aliases lets say a and b), on PK.

mapping:

b.disability_flg

Where:

b.current = '1'

Data never sleeps

View solution in original post


All Replies
Solution
‎01-08-2013 04:19 AM
Super User
Posts: 5,435

Re: Help with simple SQL Join expression

Posted in reply to TurnTheBacon

Join the table onto itself (aliases lets say a and b), on PK.

mapping:

b.disability_flg

Where:

b.current = '1'

Data never sleeps
Frequent Contributor
Posts: 89

Re: Help with simple SQL Join expression

Thanks Linus. I know how to join tables to themselves if I can drag-and-drop them from the folders menu into the SQL Join "Create" window, but in this case the input table is a previous SQL Join. How can I make a copy of it in the DI Studio SQL Join Create window?

Skjermbilde.PNG

I had hoped to resolve the problem another way. So far I've been grouping by the PK and used expressions to select for example the min(ValidFrom_DTTM) and max(ValidTo_DTTM) to combine 2-3 rows into one. My aim is that the combined row should have the most updated/current disability_flag value.

Super User
Posts: 5,435

Re: Help with simple SQL Join expression

Posted in reply to TurnTheBacon

Just map it twice in the job diagram, the second mapping prompts you for specifying an alias.

Data never sleeps
Frequent Contributor
Posts: 89

Re: Help with simple SQL Join expression

Perfect, now all the other pieces fell into place as well. Thank you. Smiley Happy

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 243 views
  • 3 likes
  • 2 in conversation