CASE WHEN in request-CodePudding

I have a "user_activity_log" table that contains the fields "id", "client_id", "hitdatetime", and "action".

id	client_id	hitdatetime	action
2661715	17	2020-09-18 11:30:43	visit
2661716	17	2020-09-18 11:30:54	registration
2661717	17	2020-09-18 11:31:16	visit

It is necessary to output:

"client_id", from the input table
"visit_dt", that is associated to the "hitdatetime" field when the "action" equals to 'visit', otherwise it is null
"is_registration", that is associated to 1 if "action" equals to 'registration', otherwise it is 0

The CASE statement is mandatory for this query.

I've started writing the query, but I don't know what to put in place of the signs ???.

SELECT client_id, 
      CASE WHEN action = 'visit' THEN ???  ELSE 'NULL' END as visit_dt, 
      CASE WHEN action = 'registration' THEN '1' ELSE '0' END as is_registration 
FROM  user_activity_log;

Can you provide help?

CodePudding user response：

Try with the following one:

SELECT client_id, 
       CASE WHEN action = 'visit' 
            THEN hitdatetime END AS visit_dt, 
       CASE WHEN action = 'registration' 
            THEN 1 
            ELSE 0           END AS is_registration 
FROM user_activity_log;

Side notes:

if the ELSE clause of the CASE statement should evaluate to NULL, you are not required to specify it as it is default value
use numeric values in place of strings if the nature of your input should be numeric
always prefer using NULL instead of the corresponding "NULL" string, as sql provides a whole set of functions that can handle NULL values in a better way

CodePudding user response：

is_registration should really be boolean. Also makes the query simpler:

SELECT client_id
     , CASE WHEN action = 'visit' THEN hitdatetime END AS visit_dt
     , action = 'registration' AS is_registration  -- !
FROM   user_activity_log;

If action can be NULL, so can be is_registration. If you want false instead of null, use one of these:

action IS NOT DISTINCT FROM 'registration' AS is_registration

Or:

COALESCE(action = 'registration', false) AS is_registration