CODEWITHSUNDEEP
sql
Hagbard
Hagbard

Reputation: 575

How to use CASE in Where statment Oracle SQL?

I have a simple SQL statement to return product data. Basically I can type in name of an item, if the item matches exact then do nothing, if there are no matches for the exact request I want to switch the 'WHERE' statement in my SQL. Currently it looks like:

SELECT 
  Product_ID,
  Product_Name,
  Product_Type,
  Product_Cost
FROM tblProducts
WHERE Product_Name = 'iPhone'

This will obviously only return products called 'iPhone' exactly. iPhone 5 would not be returned.

What I'm trying to do is if the query fetches 0 rows using the first Where clause, then it triggers a second where clause which would be something like:

WHERE Product_Name like '%iPhone%'

So if there are no products called exactly iPhone, return any products which have iPhone somewhere in their name.

I want to then display this on the query result pane, so trying to create an extra field on the output, a sort of flag to say 'if the second Where clause was used set value to 1'. Where 1 = no exact match, 0 = exact match

So i would end up with something like:

ID       Name            Type       Cost  Matched
-----------------------------------------------    
101| New iPhone Case| Electronics | 4.99 | 1

So far i have:

SELECT 
  Product_ID,
  Product_Name,
  Product_Type,
  Product_Cost
CAST(0 AS VARCHAR2(1)) as match
FROM tblProducts
WHERE Product_Name = 'iPhone'

But cant work out how to do a Switch Case on the WHERE, to illustrate:

SELECT 
  Product_ID,
  Product_Name,
  Product_Type,
  Product_Cost
CAST(0 AS VARCHAR2(1)) as match
FROM tblProducts
WHERE Product_Name = 'iPhone'
if (results = 0){
SELECT 
  Product_ID,
  Product_Name,
  Product_Type,
  Product_Cost
CAST(1 AS VARCHAR2(1)) as match
FROM tblProducts
WHERE Product_Name like '%iPhone%'
}

Any ideas how I could achieve this?

Upvotes: 2

Views: 150

Answers (1)

Mureinik
Mureinik

Reputation: 312219

I can't see how this can be done with a case statement either (not in the where clause, at least).

A query to produce non-exact matches would be fairly straight-forward:

SELECT Product_ID,
       Product_Name,
       Product_Type,
       Product_Cost
       CASE Product_Name WHEN `iPhone` THEN 0 ELSE 1 END AS Match
FROM   tblProducts
WHERE  Product_Name LIKE '%iPhone%'

The tricky part is to return non-exact matches only if exact matches do not exist. This can be done with the not exists operator:

SELECT Product_ID,
       Product_Name,
       Product_Type,
       Product_Cost
       CASE Product_Name WHEN `iPhone` THEN 0 ELSE 1 END AS Match
FROM   tblProducts
WHERE  Product_Name = 'iPhone' OR
       (Product_Name LIKE '%iPhone%' AND
        NOT EXISTS (SELECT *
                    FROM   tblProducts
                    WHERE  Product_Name = 'iPhone')
       );

Upvotes: 2

Related Questions