Can I put an IF statement in a WHERE clause in SQL?-CodePudding

Is it possible to put an IF statement in a WHERE CLAUSE?

Here is what I am trying to achieve:

WHERE QUANTITY NOT IN ('0') IF COLOUR NOT LIKE ('RED')

I am basically trying to exclude all orders with a quantity of 0 for all colours besides RED. Anyone know how I can achieve this in Snowflake? My above syntax doesn't work.

CodePudding user response：

IF statements are only valid in SELECT clauses. I'd recommend something like:

SELECT * FROM orders
WHERE other_condition
  AND (quantity != 0 OR colour = 'red')

The above will get you exactly what you need, with the option to add other conditions as required.

CodePudding user response：

As noted but others OR is the way to go:

SELECT *
FROM VALUES
    (0,'Red'), 
    (1,'Red'), 
    (0,'Blue'), 
    (1,'Blue')
    t(QUANTITY, COLOUR)
WHERE (t.COLOUR = 'Red' OR t.QUANTITY > 0);

gives:

QUANTITY	COLOUR
0	Red
1	Red
1	Blue

to be silly, to prove you can use IFF in the WHERE clause this also works...

SELECT *
FROM VALUES
    (0,'Red'), 
    (1,'Red'), 
    (0,'Blue'), 
    (1,'Blue')
    t(QUANTITY, COLOUR)
WHERE IFF(t.COLOUR = 'Red' OR t.QUANTITY > 0, true, false);

QUANTITY	COLOUR
0	Red
1	Red
1	Blue

And even the LIKE and IN logic can be used..

But given only one value is in the quantity <> or != probably should be used, then there is also the point, quantity aka a number should be a number not text, and thus should not be compared to a string/text '0'. And LIKE is not case sensitive, thus perhaps to want ILIKE which is insenitive.

SELECT *
FROM VALUES
    (0,'Red'), 
    (1,'Red'), 
    (0,'Blue'), 
    (1,'Blue')
    t(QUANTITY, COLOUR)
WHERE (COLOUR LIKE ('Red') OR QUANTITY NOT IN ('0'))