CASE WHEN in request

Question

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?

You said you wanted "the value from the hitdatetime field". Did you try writing `hitdatetime`? Did it do what you hoped? — Dan Getz, Jun 14 '22 at 18:30
Its probblay you set the null values as string , remove the quotations around null — eshirvana, Jun 14 '22 at 18:31
Also, be careful that `NULL` and `'NULL'` are different values, make sure you know which one you want. — Dan Getz, Jun 14 '22 at 18:31
`else 'null'` is not needed 1st because if you're using a `case` statement and there's no `else` then when it doesn't find a match it returns `null`. 2nd it's `null` not `'null'` — Shmiel, Jun 14 '22 at 18:33
Thank you all. I just didn't understand the condition initially. There all the values were in quotation marks. When I removed them, everything worked — Roman Bolshevik, Jun 14 '22 at 18:36

score 1 · Answer 1 · answered Jun 14 '22 at 18:38

1

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

answered Jun 14 '22 at 18:38

lemon

14,875
6
18
38

1

*as sql provides a whole set of functions that can handle NULL values in a better way* Is not really the reason not to use `'null'`. It's just that `null` is a **non value**, while `'null'` is a string. – Shmiel Jun 14 '22 at 18:49
That's a nice description improvement over the third bullet, thanks for sharing. – lemon Jun 14 '22 at 18:51

score 0 · Answer 2 · answered Jun 15 '22 at 00:20

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

CASE WHEN in request

2 Answers2