Case Statement

Question

Hi,
I have the following code that doesn't quite work as I need it to, and wondered where I am going wrong

select      
distinct  
substring(apps.app_code,3,6) as app_code,  
apps.app_name,  
max(case when comp_data.comp_id = '1' and comp_data.comp_rec = '2' then substring(comp_data.comptext,3,7) else substring(apps.app_code,3,6) end)   
over (partition by apps.app_code) as parent_app_code  
  
from apps   
left join comp_data  
on apps.app_code = comp_data.comptext  

This is my output from the code used above:

But this is the output that I am hoping to achieve:

If I change the code to this below, I get the correct parent_app_code, but the rest of the records where they don't have a parent_app_code, it's just left with a blank value

 select      
    distinct  
    substring(apps.app_code,3,6) as app_code,  
    apps.app_name,  
    max(case when comp_data.comp_id = '1' and comp_data.comp_rec = '2' then substring(comp_data.comptext,3,7) else '' end)   
    over (partition by apps.app_code) as parent_app_code  
      
    from apps   
    left join comp_data  
    on apps.app_code = comp_data.comptext  

I don't really understand why this doesn't work, can someone please point me in the right direction as to how I resolve. This is the sort of output that I need (app_code in green signals that it's the parent_app_code):

Answer 1

Hi @Bone_12

If I change the code to this below, I get the correct parent_app_code, but the rest of the records where they don't have a parent_app_code, it's just left with a blank value

How about changing this blank value to NULL and then use ISNULL function to turn NULL value to 'app_code'.
Like this:

select distinct  
       substring(apps.app_code,3,6) as app_code,  
       apps.app_name,  
       ISNULL(max(case when comp_data.comp_id = '1' and comp_data.comp_rec = '2'then substring(comp_data.comptext,3,7) else NULL end) over(partition by apps.app_code)  
             ,substring(apps.app_code,3,6)) as parent_app_code  
from apps left join comp_data on apps.app_code = comp_data.comptext  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.