Oracle case inside where clause
Solution 1
I think this is the best way to solve your problem:
select *
from dual
where (1 = 1)
and (sysdate + 1 > sysdate)
and case
when i_value = 'S'
then
case
when (25 < 35)
then 1
else 0
end
when i_value = 'T'
then
case
when (30 > 40)
then 1
else 0
end
end = 1;
Of course, you could use Dynamic SQL, but it'd be more difficult and less effective.
Solution 2
SELECT * FROM dual
WHERE (1 =1)
AND (SYSDATE+1 > SYSDATE)
AND CASE WHEN i_value = 'S' THEN 1 ELSE CASE WHEN (30 > 40) THEN 1 ELSE 0 END END = 1
AND CASE WHEN i_value = 'T' THEN 1 ELSE CASE WHEN (25 < 35) THEN 1 ELSE 0 END END = 1;
Solution 3
Why so ser use case
?
SELECT * FROM dual
WHERE (1 =1)
AND ( SYSDATE+1 > SYSDATE )
AND ( ((30 > 40) and i_value <> 'S') or i_value = 'S' )
AND ( ((25 < 35) and i_value <> 'T') or i_value = 'T' );
ajmalmhd04
I am , currently hooked with Oracle; flowing with computer and the technologies since after my schools, joined for bachelors degree specialised in ǝɔuǝıɔs ɹǝʇndɯoɔ and tasted basics of the programming languages like c, c++ to java, It includes basics of perl , python, and some sharp knowledges in html and css. Before leaving from my college,I have learned some tactics with the .Net platform which include SqlServer, as backend, and languages like c# and visualbasic. And so badged another Certification on Web Applications, hence Certified as Microsoft Technology Specialist.(MCTS) My previous certifications on Graphic Designing which comprises( Photoshop,Coreldraw,Illustrator). :) My first earning came into my pocket before my college ends, on a financial year ending , I got to work in a private bank, for some data entry project and Interest calculating issues. So I used MS Access as a simple tool. My average plus typing speed above 40wpm for the data entry help alot for the earlier completion for the project. I had a little work for creating website in my current company for developing website that make use of wordpress themes and so I learned about the language in PHP, and the database MySql. As my interest is and still wandering on data and database, I moved on the database team and currently my graph is stepping on Oracle, working with sql queries and plsql. Familiarizing with the Oracle Spatial and slight insight in Sql Loader tools. SELECT chr(ajmalmhd04 +4 +50-25-12-13) ||chr(ajmalmhd04+2+150+-25+4-25+4-9+5-0-1-2) ||chr(ajmalmhd04 -3) ||chr(ajmalmhd04 +18) ||chr(ajmalmhd04 +1) ||chr(ajmalmhd04 -68) ||chr(ajmalmhd04 +2) ||chr(ajmalmhd04 +17) ||chr(ajmalmhd04 +10) ||chr(ajmalmhd04 -67) ||chr(ajmalmhd04 -10-68+5+7-2+5-3-2-3+4-3-1+3) ||chr(ajmalmhd04 -42) ||chr(ajmalmhd04 -59) FROM (SELECT 1000/10 ajmalmhd04 FROM dual)
Updated on September 11, 2020Comments
-
ajmalmhd04 over 3 years
This is a simple question, I've read some details about using
CASE
inWHERE
clause, but couldn't able to make a clear idea how to use it. The below is my sample query:1 SELECT * FROM dual 2 WHERE (1 =1) 3 AND (SYSDATE+1 > SYSDATE) 4 AND (30 > 40) 5 AND (25 < 35);
I have a procedure
i_value
as in parameter. I need to ignore the 4th line if i_value is 'S' and I need to ignore the 5th line if i_value is 'T'.Thanks in advance.