CODEWITHSUNDEEP
sqloracle11g
Rookie007
Rookie007

Reputation: 1219

Switch where conditions in a SQL statement based on data

I need a query to switch in the where clause depending on dynamic data.

For example:

SELECT * 
FROM 
    (SELECT * 
     FROM DOGS 
     WHERE DOG_ID ='1234' ) x, ANIMALS y

IF X.DOG_ID != '3456' THEN  --  SO HERE WHERE CONDITION SWITCH BASE ON DATA FROM DOGS WHICH ID IS '1234'
    WHERE X.DOG_ID = Y.ANIMAL_ID AND (SOME OTHER CONDITION) 
ELSE 
    WHERE X.DOG_ID = Y.ANIMAL_ID

My if condition would not be from the result set.

Is it possible to switch in a where clause with SQL like above?

Upvotes: 0

Views: 56

Answers (3)

Jeroen
Jeroen

Reputation: 63719

Just nest AND and OR conditions:

SELECT *
FROM   (SELECT * 
        FROM   DOGS 
        WHERE  DOG_ID ='1234') x, 
       ANIMALS y
WHERE  (X.DOG_ID != '3456' AND X.DOG_ID = Y.ANIMAL_ID /*AND (SOME OTHER CONDITION)*/)
       OR 
       (X.DOG_ID = Y.ANIMAL_ID)

Notice that you could also be benifited by a CASE perhaps? Check out the docs for more info.

Finally, it seems to me you're actually after a JOIN, perhaps something like this:

SELECT *
FROM   (SELECT * 
        FROM   DOGS 
        WHERE  DOG_ID ='1234') x, 
       JOIN ANIMALS y ON x.DOG_ID = y.ANIMAL_ID
WHERE  (X.DOG_ID != '3456' /*AND (SOME OTHER CONDITION)*/)

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

Use ANSI JOIN

select x.*,y.* from dogs as x inner join animals y
on 
( 
(x.dog_id != y.animal_id and x.dog_id = y.animal_id and (some other condition))
       or x.dog_id = y.animal_id
)
where dog_id ='1234'

Upvotes: 0

Dean
Dean

Reputation: 678

Try this, the CASE method

SELECT * FROM (SELECT * FROM DOGS WHERE DOG_ID ='1234' ) X, ANIMALS y
WHERE CASE WHEN X.DOG_ID != Y.ANIMAL_ID 
           THEN X.DOG_ID = Y.ANIMAL_ID AND (SOME OTHER CONDITION) 
           ELSE X.DOG_ID = Y.ANIMAL_ID 
      END

Upvotes: 0

Related Questions