max(case when ...) syntax issue-CodePudding

select 
complaint_id
,complaint_type
,COMMUNICATION_ID
,max(case when delivery_type='deliver_once' then '1' else '0' end as IS_ADDON_REFUND) 
from 
complaints_order_status 
group by 1,2,3

what's the issue with it?

CodePudding user response：

You can't name your column inside the MAX function. Also use integers for 1 and 0. Also, if you could paste in the error message you're getting it would be helpful. But try:

select complaint_id ,
       complaint_type ,
       COMMUNICATION_ID ,
       max(case when delivery_type='deliver_once' then 1 else 0 end) as IS_ADDON_REFUND 
from complaints_order_status 
group by complaint_id ,complaint_type ,COMMUNICATION_ID

CodePudding user response：

I have changed two things from your code:

Use the alias in Group by. 1,2,3 don't work on all platforms.
Give alias outside max clause bracket. There is no need to give a name to case when clause when this is not your final output.

Here is the final code:

  select complaint_id ,complaint_type ,COMMUNICATION_ID ,
       max(case when delivery_type='deliver_once' 
                then '1' else '0'
       end )as IS_ADDON_REFUND                                            
from complaints_order_status 
group by complaint_id,
         complaint_type,
         COMMUNICATION_ID