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,260

Re: Help with simple SQL Join expression

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,260

Re: Help with simple SQL Join expression

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,260

Re: Help with simple SQL Join expression

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.

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

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