CODEWITHSUNDEEP
mysqlcase
SQL learner
SQL learner

Reputation: 11

SQL Case Statement: To Filter records inside WHERE Clause

I have an Input Table as shown below with 14 records.

Desired Results : I want the query to shows records present in Output Table.

Current SQL Query : I have used CASE statement to achieve the result. However, I am wandering is there a better way to achieve the result ? Please advise.

Input Table

Output Table

I have used the below query to get to the desired OUTPUT Table.

Select * from Table WHERE

((Case WHEN Type = 'BLN' THEN AMOUNT end ) > 70000

OR (Case WHEN Type = 'CRM' THEN AMOUNT end) > 200000

OR (CASE WHEN Type = 'CY' THEN AMOUNT END) > 90000

OR

(CASE WHEN Type = 'DAN' THEN AMOUNT END) > 450000

OR (CASE WHEN Type = 'EAN' THEN AMOUNT END) > 100000

OR (CASE WHEN Type = 'EPL' THEN AMOUNT END) > 70000

OR (CASE WHEN Type = 'FPL' THEN AMOUNT END) > 30000

OR (CASE WHEN Type NOT IN ('BLN','CRM','CY','DAN','EAN','EPL','FPL') THEN AMOUNT END) > 200000

)

Upvotes: 0

Views: 70

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

It's more compact written this way:

SELECT * FROM Table WHERE
CASE Type
WHEN 'BLN' THEN AMOUNT >  70000
WHEN 'CRM' THEN AMOUNT > 200000
WHEN 'CY'  THEN AMOUNT >  90000
WHEN 'DAN' THEN AMOUNT > 450000
WHEN 'EAN' THEN AMOUNT > 100000
WHEN 'EPL' THEN AMOUNT >  70000
WHEN 'FPL' THEN AMOUNT >  30000
ELSE AMOUNT > 20000
END

But in fact I don't expect it to be an improvement in performance. Both solutions will do a table-scan.

If you want to use indexes to optimize the query, you'd need to create an index on (Type, Amount) and do a series of queries, one for each Type value, then combine them together with UNION.

Upvotes: 1

Related Questions