Need guidance in forming a query in snowflake

Question

SELECT id, login_id, count, case when count = 0 then 'Cat_A' WHEN count between 1 and 10 then 'Cat_B' WHEN count > 10 then 'Cat_C' WHEN count IS NULL THEN 'Cat D' END as Category FROM ( select id,login_id,min(ord_count) AS count FROM table_1 X JOIN table_2 Y ON X.id_col = Y.id_col WHERE date = '2022-02-02' AND login_id = 'True' group by id,login_id )A LEFT JOIN ( SELECT id,COUNT(X.ord_no) AS count_of_orders FROM table_1 X WHERE X.date = '2022-02-02' group by id )B ON A.id=B.id

When I join these two tables, I'm getting NULL values for the unmatched records. I need to replace those NULL records to some hardcoded value say 'XYZ'.

Any guidance on how to achieve this please?

You can do something like NVL( COLUMN_TO_CHECK , 'XYZ' ) unless there's more that needs to happen here. — Greg Pavlik
– Greg Pavlik, Commented Feb 16, 2022 at 19:11
This query won't run in its current state as column id is present in both a and b aliased derived tables. As far as I can tell you are only using columns from alias a, so why even join? At any rate, it's not clear what you mean "NULL values for the unmatched records". It's hard to talk about data and records when all we can are given is broken sql. Please share sample data and desired results. — JNevill
– JNevill, Commented Feb 16, 2022 at 19:14

Simeon Pilgrim · Accepted Answer · 2022-02-16 20:23:34Z

So the top level select needs to name which ID it is using (other DB's don't require this snowflake does), given you are selecting from A and b.id might be missing, it should be a.id
count_of_orders is not used, so currently the LEFT JOIN to B is pointless, given your question is about LEFT JOIN this must be the column you a referring to??
The replace NULL values can be done via COALESCE or NVL or ZEROIFNULL, given the only null thing is a count, zeroifnull seems to make sense here.

which all make me think your SQL needs to look like:

SELECT a.id, a.login_id, a.count, case WHEN a.count = 0 then 'Cat_A' WHEN a.count between 1 and 10 then 'Cat_B' WHEN a.count > 10 then 'Cat_C' WHEN a.count IS NULL THEN 'Cat D' END as Category, ZEROIFNULL(b.count_of_orders) as count_of_orders FROM ( SELECT id, login_id, min(ord_count) AS count FROM table_1 AS X JOIN table_2 AS Y ON X.id_col = Y.id_col WHERE date = '2022-02-02' AND login_id = 'True' group by id,login_id ) as A LEFT JOIN ( SELECT x.id, COUNT(X.ord_no) AS count_of_orders FROM table_1 as X WHERE X.date = '2022-02-02' group by x.id )as B ON A.id=B.id

The A sub-select really should use the aliases you named X, Y so we know which tables id, login_id, ord_count, & date all come from.

Collectives™ on Stack Overflow

Need guidance in forming a query in snowflake

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related