Can you use a alias from a case statement within another case statement? I have the following statement

    CASE
    WHEN ROW_NUMBER()
         OVER(PARTITION BY s.sco_cand, aw.award_id
              ORDER BY
                  s.sco_cand, aw.award_id, ai.a_instalment_id
         ) = 1 THEN
            SUM(vt.net_amount - sl18.fee_paid)
END AS DIFFERENCE

This works out the difference between two values while suppressing a repeat value that is reported (but that's not important tbh).

I want to use the above statement to create a "Comments" column that tests the output found in the above statement and gives the user a a recommendation back. I have created this column successfully, however I have to paste the entire code above in to my new case statement to make it work

CASE
           
            WHEN CASE
                    WHEN ROW_NUMBER ()
                         OVER (
                           PARTITION BY S.SCO_CAND,AW.AWARD_ID
                     ORDER BY
                    S.SCO_CAND,
                    AW.AWARD_ID,
                    AI.A_INSTALMENT_ID) = 1
                    THEN
                       SUM (VT.NET_AMOUNT - SL18.FEE_PAID)
                  
                 END = 0
            THEN
               'OK'
            WHEN CASE
                    WHEN ROW_NUMBER ()
                         OVER (
                            PARTITION BY S.SCO_CAND,AW.AWARD_ID
                       ORDER BY
                    S.SCO_CAND,
                    AW.AWARD_ID,
                    AI.A_INSTALMENT_ID) = 1
                    THEN
                       SUM (VT.NET_AMOUNT - SL18.FEE_PAID)
                   
                 END
                    IS NULL
            THEN
               'TRANSACTION MISSING'
            WHEN CASE
                    WHEN ROW_NUMBER ()
                         OVER (
                          PARTITION BY S.SCO_CAND,AW.AWARD_ID
                         ORDER BY
                    S.SCO_CAND,
                    AW.AWARD_ID,
                    AI.A_INSTALMENT_ID) = 1
                    THEN
                       SUM (VT.NET_AMOUNT - SL18.FEE_PAID)
                
                 END > 0
            THEN
               'OVER PAYMENT'
             WHEN CASE
                    WHEN ROW_NUMBER ()
                         OVER (
                          PARTITION BY S.SCO_CAND,AW.AWARD_ID
                         ORDER BY
                    S.SCOT_CAND,
                    AW.AWARD_ID,
                    AI.A_INSTALMENT_ID) = 1
                    THEN
                       SUM (VT.NET_AMOUNT - SL18.FEE_PAID)
                    
                 END < 0
                 AND SCY.CCHANGE IS NOT NULL 
            THEN
               'UNDERPAYMENT'
               ELSE 'INVESTIGATE'
               
         END
            COMMENTS

I will have about 11 case statements using the initial block of code (once the full list of tests are completed). Is there anyway I can alias the initial block and then reference the alias further on to stop the code becoming cumbersome to read (I'm have to be mindful that less experienced staff would have to follow this code too).

CodePudding user response：

Sure you can by using WITH..AS clause such as

WITH t AS
(
 SELECT CASE
          WHEN ROW_NUMBER() OVER(PARTITION BY S.SCO_CAND,
                    AW.AWARD_ID ORDER BY S.SCO_CAND,
                    AW.AWARD_ID,
                    AI.A_INSTALMENT_ID) = 1 THEN
           SUM(VT.NET_AMOUNT - SL18.FEE_PAID)
        END AS case_stmt
   FROM t0
)
SELECT CASE
         WHEN case_stmt = 0 THEN
          'OK'
         WHEN case_stmt = 1 THEN
          SUM(VT.NET_AMOUNT - SL18.FEE_PAID) 
         WHEN case_stmt = 1 AND ... THEN 
          SUM(VT.NET_AMOUNT - SL18.FEE_PAID) 
         WHEN case_stmt = 1 AND ... THEN 
          SUM(VT.NET_AMOUNT - SL18.FEE_PAID)       
         ....     
         ....
         ELSE 'INVESTIGATE'       
       END AS comments
  FROM t

CodePudding user response：

The simplest way is an outer select

select .. 
   case 
     when DIFFERENCE = 0 then 'OK'
     when DIFFERENCE > 0 then 'OVER PAYMENT' 
     when DIFFERENCE < 0 then 'UNDER PAYMENT' 
     else 'INVESTIGATE' 
   end COMMENTS
 from (
   select ..
      CASE
        WHEN ROW_NUMBER()
         OVER(PARTITION BY s.sco_cand, aw.award_id
              ORDER BY
                  s.sco_cand, aw.award_id, ai.a_instalment_id
         ) = 1 THEN
            SUM(vt.net_amount - sl18.fee_paid)
      END AS DIFFERENCE
    from ..
  ) t