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.
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
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.
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
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 NoneTypeNone 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’.
