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

[TurnTheBacon]

Data Integration Studio

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

INPUT 1:

SOFTWARE_ID	VERSION_ID	STATUS_CODE
1234	100	AAA
1234	101	AAE
1234	102	ABB
1234	103	ABF

INPUT 2:

SOFTWARE_ID	NUMERIC_VALUE
1234	1250,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_ID	VERSION_ID	STATUS_CODE	NUMERIC_VALUE
1234	100	AAA	0
1234	101	AAE	0
1234	102	ABB	0
1234	103	ABF	1250,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_ID	VERSION_ID	STATUS_CODE	NUMERIC_VALUE
1234	100	AAA	0
1234	101	AAE	1250,25
1234	102	ABB	0
1234	103	ABF	0

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

Thanks so much for your time.

[Tom · Accepted solution]

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

[TurnTheBacon]

Perfect. Thanks Tom.