Sql case statement not working

I want ‘None’ to replaced with ‘boss’ in just the manager column but this is not working in the case statement please advise? I do not want to create a new column either.

trying out the case statement from this mission

        emp.title as 'Title',
        emp.first_name || ' ' || emp.last_name AS 'Employee',
        mgr.first_name || ' ' || mgr.last_name AS 'Manager'
    , CASE 
    WHEN mgr.first_name || ' ' || mgr.last_name  = 'None' then 'boss'
    else mgr.first_name || ' ' || mgr.last_name  
    end as 'm'
         

FROM 
    employee emp
    --enforce join condition that mgr id of 1st table = id of 2nd
LEFT JOIN 
    employee mgr ON mgr.employee_id = emp.reports_to 

ORDER BY manager
1 Like

A new column appears because it appeared in the SELECT statement, either from a raw column in a queried table(s), or an invented column (usually with CASE). If you don’t want to create it, don’t write it in SELECT.

You can either wrap an outer query to process the Manager column, or immediately apply CASE statement processing in the current query on the 3rd column. Currently, there is no processing of None to boss at all in mgr.first_name || ' ' || mgr.last_name AS 'Manager' , just string concatenation.

Also, does = 'None' check really work? How can the Left hand side ever be ='None'? It already has a space hardcoded in, should never match.

2 Likes

Hi

thanks, but I already worked out after looking at case statements online.

Also, does = 'None' check really work? How can the Left hand side ever be ='None' ? It already has a space hardcoded in, should never match.

Reason why I checked for that was because I thought ‘None’ was an actual string
since that is the text in the ouput above in manager column,
however I see that it is actually a blank or a space as you say which is why there was no match.

 %%sql

SELECT 
        emp.title as 'Title',
        emp.first_name || ' ' || emp.last_name AS 'Employee',
       
        
    CASE 
    WHEN emp.reports_to  <>'' then mgr.first_name || ' ' || mgr.last_name
    else 'is the boss'
    
    end as 'manager'   
 
        
         

FROM 
    employee emp
    --enforce join condition that mgr id of 1st table = id of 2nd
LEFT JOIN 
    employee mgr ON mgr.employee_id = emp.reports_to 

ORDER BY manager desc
1 Like

None in the pandas Dataframe is not a string type blank or space (although you can put a string None and it will be visually indistinguishable), to be specific its a python NoneType None object (which is also not a np.nan). It comes from a NULL value in the database.

The space I am talking about is not “None in the dataframe HTML display is actually a NULL value in the database”, i’m refering to the space in the middle of this 3 part string concatenation. mgr.first_name || ' ' || mgr.last_name AS 'Manager'.

This hardcoded space means it will not match ‘None’ even if None was an actual string. If you didn’t do the 3 part concatenation but only used mgr.first_name or mgr.last_name (the 1st and 3rd parts of the concatenation), then there is a chance it is equal string ‘None’.

1 Like

thanks I shall try that