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

Advice on how to modify an SQL Join expression (case statement)

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

Advice on how to modify an SQL Join expression (case statement)

Data Integration Studio


Imagine that you have an SQL Join with the two following input tables:


INPUT 1:

SOFTWARE_IDVERSION_IDSTATUS_CODE
1234100AAA
1234101AAE
1234102ABB
1234103ABF


INPUT 2:

SOFTWARE_IDNUMERIC_VALUE
12341250,25


The SQL Join uses INNER JOIN on input1.SOFTWARE_ID=input2.SOFTWARE_ID


In the output table, the expression used for NUMERIC_VALUE is:


case

  when input1.VERSION_ID = max(input1.VERSION_ID) then input2.NUMERIC_VALUE

  else 0

end

This results in the following output table:

SOFTWARE_IDVERSION_IDSTATUS_CODENUMERIC_VALUE
1234100AAA0
1234101AAE0
1234102ABB0
1234103ABF1250,25

MY PROBLEM:

I want to make sure that for each SOFTWARE_ID (there's many more than in this simplified example), the NUMERIC_VALUE is placed on the max VERSION_ID that has STATUS_CODE either AAA or AAE.

Basically something like this (if this worked):

case

  when input1.VERSION_ID = max(input1.VERSION_ID where input1.STATUS_CODE in ("AAA","AAE")) then input2.NUMERIC_VALUE

  else 0

end

I want the output to be like this:

SOFTWARE_IDVERSION_IDSTATUS_CODENUMERIC_VALUE
1234100AAA0
1234101AAE1250,25
1234102ABB0
1234103ABF0

I'd greatly appreciate advice on how to modify the SQL JOIN expression used for NUMERIC_VALUE.

Thanks so much for your time. Smiley Happy


Accepted Solutions
Solution
‎07-12-2013 08:13 PM
Super User
Super User
Posts: 7,039

Re: Advice on how to modify an SQL Join expression (case statement)

Posted in reply to TurnTheBacon

How about using the if inside of the MAX() function.

You could take advantage of SAS's method of evaluating boolean expressions as 0 or 1 :

case when input1.VERSION_ID = max( input1.VERSION_ID * (input1.STATUS_CODE in ("AAA","AAE")) )

     then input2.NUMERIC_VALUE

     else 0

end

Or just use another CASE statement inside the MAX() function.

case when input1.VERSION_ID = max(

      case when (input1.STATUS_CODE in ("AAA","AAE"))

           then input1.VERSION_ID

           else 0

      end )

    then input2.NUMERIC_VALUE

    else 0

end

View solution in original post


All Replies
Solution
‎07-12-2013 08:13 PM
Super User
Super User
Posts: 7,039

Re: Advice on how to modify an SQL Join expression (case statement)

Posted in reply to TurnTheBacon

How about using the if inside of the MAX() function.

You could take advantage of SAS's method of evaluating boolean expressions as 0 or 1 :

case when input1.VERSION_ID = max( input1.VERSION_ID * (input1.STATUS_CODE in ("AAA","AAE")) )

     then input2.NUMERIC_VALUE

     else 0

end

Or just use another CASE statement inside the MAX() function.

case when input1.VERSION_ID = max(

      case when (input1.STATUS_CODE in ("AAA","AAE"))

           then input1.VERSION_ID

           else 0

      end )

    then input2.NUMERIC_VALUE

    else 0

end

Frequent Contributor
Posts: 89

Re: Advice on how to modify an SQL Join expression (case statement)

Perfect. Thanks Tom. Smiley Happy

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 313 views
  • 2 likes
  • 2 in conversation