Search code examples
sqlu-sql

How to convert this usql to valid sql query

SELECT DISTINCT 
d.id AS id, 
CASE
    WHEN (c.a = 'UNK' ? NULL : c.a) IS NOT NULL THEN c.a
    WHEN (b.a = 'UNK' ? NULL : b.a) IS NOT NULL THEN b.a
    WHEN (c.e = 'UNK' ? NULL : c.e) IS NOT NULL THEN c.e
    ELSE b.e
END AS output,
FROM 
   db.table
...random irrelevant joins..

I have traced the problem down to the case end part of my code. I get a parse exception error. I am assuming (c.a = 'UNK' ? NULL : c.a) is not supported. How would I convert this?

Solution

  • If I understand the logic correctly, a simple comparison will do:

    SELECT DISTINCT d.id AS id, 
       (CASE WHEN c.a <> 'UNK' THEN c.a
             WHEN b.a <> 'UNK' THEN b.a
             WHEN c.e <> 'UNK' THEN c.e
             ELSE b.e
        END) AS output,
FROM . . .

    NULL fails the <> comparison, so this should be semantically equivalent.