Quote Originally Posted by wenowd View Post
My query islike below.

SELECT DISTINCT CASE
WHEN esc.x1 = Substr(inp.y, 0, 3)
AND esc.x2 = Substr(inp.y, 4, 2)THEN esc.cc
WHEN esc.mcc = Substr(inp.y, 0, 3)
AND esc.mnc = Substr(inp.y, 4, 3)THEN esc.cc
END
FROM xxx inp,
yyy esc
WHERE CASE
WHEN esc.x1 IN( Substr(inp.y, 0, 3) )
AND esc.x2 IN( Substr(inp.y, 4, 2) )THEN esc.cc
WHEN esc.x1 IN( Substr(inp.y, 0, 3) )
AND esc.x2 IN( Substr(inp.y, 4, 3) )THEN esc.cc
END IS NOT NULL

Now the query is returning more than one row because it is satisfying both the case statement. what i want is if the case statement is NULL then only it should go for second one.Otherwise only the output of first case should be the output.Pls help me to modify the query accordingly.

Thnx in advance.
Please clarify, I am only seeing ONE case statement with two "WHEN" conditions.
What do you mean by: "what i want is if the case statement is NULL then only it should go for second one." which is the first and which is the second?