Home » SQL & PL/SQL » SQL & PL/SQL » SELECT statement with IF/ELSe or CASE
SELECT statement with IF/ELSe or CASE [message #431811] Thu, 19 November 2009 11:19 Go to next message
sayal
Messages: 12
Registered: November 2009
Junior Member
Hi,

i want to create a variable select statement, which varies in one point depending on fullfilling a condition or not. I tried two different statements using ELSE/IF and CASE, but they do not work. Probably there are some errors in it. I am sorry for that, but i am new to Oracle. Ok, here are the statements. Can u tell me what is wrong with them and whether this kind of syntax is allowed? Thank you!

a)
if to_char(sysdate,'D') not in ('1','2') then
SELECT t1.TR_ID
FROM FILTERDB.CFT_INTERFACE t1
LEFT JOIN FILTERDB.CFT_TROUBLE_MOD t2 on t1.TR_ID = t2.TR_ID
WHERE t1.CONTRIBUTOR <> '---' 
AND t2.DUE_DATE < to_char(sysdate -2,'DD/MM/YYYY')
go
else
SELECT t1.TR_ID
FROM FILTERDB.CFT_INTERFACE t1
LEFT JOIN FILTERDB.CFT_TROUBLE_MOD t2 on t1.TR_ID = t2.TR_ID
WHERE t1.CONTRIBUTOR <> '---' AND t2.DUE_DATE < to_char(sysdate -4,'DD/MM/YYYY')
go
end if;


b)
SELECT t1.TR_ID
FROM FILTERDB.CFT_INTERFACE t1
LEFT JOIN FILTERDB.CFT_TROUBLE_MOD t2 on t1.TR_ID = t2.TR_ID
CASE WHEN to_char(sysdate,'D') not in ('1','2') THEN WHERE t1.CONTRIBUTOR <> '---' 
AND t2.DUE_DATE < to_char(sysdate -2,'DD/MM/YYYY')
ELSE WHERE t1.CONTRIBUTOR <> '---' A
ND t2.DUE_DATE < to_char(sysdate -4,'DD/MM/YYYY')
go

[Updated on: Thu, 19 November 2009 11:22] by Moderator

Report message to a moderator

Re: SELECT statement with IF/ELSe or CASE [message #431813 is a reply to message #431811] Thu, 19 November 2009 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database SQL Reference
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

You cannot imagine what could be the syntax, you have to study it.

Regards
Michel
Re: SELECT statement with IF/ELSe or CASE [message #431817 is a reply to message #431813] Thu, 19 November 2009 11:27 Go to previous messageGo to next message
sayal
Messages: 12
Registered: November 2009
Junior Member
Ok, thank you!
Re: SELECT statement with IF/ELSe or CASE [message #431819 is a reply to message #431811] Thu, 19 November 2009 11:28 Go to previous messageGo to next message
sayal
Messages: 12
Registered: November 2009
Junior Member
And sorry for posting 10000 times, but i did not get any confirmation. Sorry!

@Michel: Can you give me a hint?

[Updated on: Thu, 19 November 2009 11:32]

Report message to a moderator

Re: SELECT statement with IF/ELSe or CASE [message #431829 is a reply to message #431819] Thu, 19 November 2009 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A hint? but the whole stuff is wrong.
"go" what is this?
"CASE WHEN to_char(sysdate,'D') not in ('1','2') THEN WHERE t1.CONTRIBUTOR <> '---'
AND t2.DUE_DATE < to_char(sysdate -2,'DD/MM/YYYY')
ELSE WHERE t1.CONTRIBUTOR <> '---' A
ND t2.DUE_DATE < to_char(sysdate -4,'DD/MM/YYYY')"
invalid syntax what is generated by a case is a value not a clause.

Regards
Michel
Re: SELECT statement with IF/ELSe or CASE [message #431830 is a reply to message #431811] Thu, 19 November 2009 11:44 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
go is not valid oracle syntax.
Selects should with a semi-colon.
Re: SELECT statement with IF/ELSe or CASE [message #431832 is a reply to message #431819] Thu, 19 November 2009 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Can you give me a hint?
We don't know what you have.
We don't know what you want.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: SELECT statement with IF/ELSe or CASE [message #431837 is a reply to message #431811] Thu, 19 November 2009 11:58 Go to previous messageGo to next message
sayal
Messages: 12
Registered: November 2009
Junior Member
I am sorry, the go is just a command in Data Studio. I forgot, of course it does not belong to the Oracle syntax.

I try to be more specific:
I have 2 different select statements. I want to use this first one, if the day the statement is used is a monday or tuesday, and the other one on the other days. I could solve this easily in php, but i am told to do this in sql. I read about the case expression and googled for an example which shows me the syntax i need, but i could not find one. That is way i ask you guys. I mean regardless of the data and tables i need, the question is clear, isn't it?
Re: SELECT statement with IF/ELSe or CASE [message #431843 is a reply to message #431811] Thu, 19 November 2009 12:07 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
So why doesn't your first attempt solve your issue once you've fixed up the syntax?

As for CASE - the documentation can be found here - It contains examples.
Re: SELECT statement with IF/ELSe or CASE [message #431845 is a reply to message #431837] Thu, 19 November 2009 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
IF day=monday OR day=tuesday THEN
query1
ELSE
query 2
END IF;

Regards
Michel

[Updated on: Thu, 19 November 2009 12:08]

Report message to a moderator

Re: SELECT statement with IF/ELSe or CASE [message #431851 is a reply to message #431811] Thu, 19 November 2009 12:16 Go to previous messageGo to next message
sayal
Messages: 12
Registered: November 2009
Junior Member
Great, i will try!

Thanks!
sayal
Re: SELECT statement with IF/ELSe or CASE [message #431853 is a reply to message #431811] Thu, 19 November 2009 12:27 Go to previous messageGo to next message
sayal
Messages: 12
Registered: November 2009
Junior Member
I have tried my first attempt after fixing, and it still does not work. I have the feeling that it is an Aqua Data Studio thing. For "normal" statements i have to start it with "go", but here it does not work. If you say that my code looks fine, i will do some research about Aqua Data Studio commands. Thanks!

IF to_char(sysdate,'D') NOT IN ('1','2') THEN
SELECT t1.TR_ID FROM FILTERDB.CFT_INTERFACE t1 LEFT JOIN FILTERDB.CFT_TROUBLE_MOD t2 ON t1.TR_ID = t2.TR_ID WHERE t1.CONTRIBUTOR = '---' AND t2.DUE_DATE < to_char(sysdate -2,'DD/MM/YYYY')
ELSE
SELECT t1.TR_ID FROM FILTERDB.CFT_INTERFACE t1 LEFT JOIN FILTERDB.CFT_TROUBLE_MOD t2 ON t1.TR_ID = t2.TR_ID WHERE t1.CONTRIBUTOR = '---' AND t2.DUE_DATE < to_char(sysdate -4,'DD/MM/YYYY')
END IF;
Re: SELECT statement with IF/ELSe or CASE [message #431859 is a reply to message #431853] Thu, 19 November 2009 12:40 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
IF
THEN
ELSE

is a PL/SQL construct & not valid as pure SQL.
Re: SELECT statement with IF/ELSe or CASE [message #431860 is a reply to message #431853] Thu, 19 November 2009 12:43 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Moreover, IF ELSE END IF should be used in PL/SQL, not in SQL.
So, it should be enclosed by BEGIN and END.
And SELECT is a SQL statement.
In this case you should use SELECT ... INTO....

So, as mentioned by Michel, please read the reference manuals.

By
Vamsi
Re: SELECT statement with IF/ELSe or CASE [message #431881 is a reply to message #431811] Thu, 19 November 2009 14:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
sayal wrote on Thu, 19 November 2009 12:19

AND t2.DUE_DATE < to_char(sysdate -2,'DD/MM/YYYY')
[/code]


If t2.DUE_DATE is a DATE datatype, then this syntax is incorrect as you are comparing a DATE to a STRING. And if it is a VARCHAR2, then '19/11/2009' is less than '20/01/2008'.
Re: SELECT statement with IF/ELSe or CASE [message #431920 is a reply to message #431811] Fri, 20 November 2009 01:50 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
Hope this is what you are looking for..

SELECT T1.TR_ID
  FROM FILTERDB.CFT_INTERFACE T1
  LEFT JOIN FILTERDB.CFT_TROUBLE_MOD T2 ON T1.TR_ID = T2.TR_ID
 WHERE T1.CONTRIBUTOR = '---'
   AND T2.DUE_DATE < TO_CHAR(SYSDATE - 2, 'DD/MM/YYYY')
   AND TO_CHAR(SYSDATE, 'D') NOT IN ('1', '2')
UNION ALL
SELECT T1.TR_ID
  FROM FILTERDB.CFT_INTERFACE T1
  LEFT JOIN FILTERDB.CFT_TROUBLE_MOD T2 ON T1.TR_ID = T2.TR_ID
 WHERE T1.CONTRIBUTOR = '---'
   AND T2.DUE_DATE < TO_CHAR(SYSDATE - 4, 'DD/MM/YYYY')
   AND TO_CHAR(SYSDATE, 'D') IN ('1', '2');
Re: SELECT statement with IF/ELSe or CASE [message #431980 is a reply to message #431811] Fri, 20 November 2009 05:04 Go to previous messageGo to next message
sayal
Messages: 12
Registered: November 2009
Junior Member
Thank you! I will try...

Greetz
sayal
Re: SELECT statement with IF/ELSe or CASE [message #431985 is a reply to message #431811] Fri, 20 November 2009 05:17 Go to previous messageGo to next message
sayal
Messages: 12
Registered: November 2009
Junior Member
I have read about the UNION ALL querry and it is not what i need. Thank you very much anyway! As far as i understand UNION ALL combines the results of two querries, but i want to use different querries in two different cases.

@vamsi:
Thanks for you tips, but i do not understand what SELECT INTO does for my problem. I have read about it and it says that it is used creating copies of tables.

Regards
sayal
Re: SELECT statement with IF/ELSe or CASE [message #431998 is a reply to message #431985] Fri, 20 November 2009 05:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In pure SQL there is no way to conditionally execute one of two different queries - you need to use Pl/SQL for that.

Your two queries are similar enough that you can use a CASE statement to distinguish between them. You just need to learn what the syntax for writing queries in SQL actually is:
SELECT t1.TR_ID
FROM FILTERDB.CFT_INTERFACE t1
     LEFT JOIN FILTERDB.CFT_TROUBLE_MOD t2 on t1.TR_ID = t2.TR_ID
WHERE t1.CONTRIBUTOR <> '---' 
AND   t2.DUE_DATE < to_char(sysdate - CASE WHEN to_char(sysdate,'D' NOT IN ('1','2') THEN 2 ELSE 4 END
                           ,'DD/MM/YYYY')


There are several other problems with your code.

1) The results of To_Char(SYSDATE,'D') depend on your NLS region

2) You are comparing a date (DUE_DATE) to a char. If the DUE_DATE column is actually a date, you should rewrite the where clause as:
AND   t2.DUE_DATE < sysdate - CASE WHEN to_char(sysdate,'D' NOT IN ('1','2') THEN 2 ELSE 4 END
Re: SELECT statement with IF/ELSe or CASE [message #432015 is a reply to message #431811] Fri, 20 November 2009 07:26 Go to previous messageGo to next message
sayal
Messages: 12
Registered: November 2009
Junior Member
Thank you so much JRowbottom!

This is exactly what i was looking for! I tested it and it works fine!

Regards
sayal
Re: SELECT statement with IF/ELSe or CASE [message #432028 is a reply to message #432015] Fri, 20 November 2009 08:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do pay attention to the two points I raised - they concern things that will cause trouble at some point, and they are good habits to get into.
Re: SELECT statement with IF/ELSe or CASE [message #432067 is a reply to message #431811] Fri, 20 November 2009 17:38 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
I am not answering you but instead just pointingout what is wrong with your code:


  1. What number is Sunday? Is it D=0 or D=7? Or maybe D=9999? You never know if you do not set your locale.
  2. Is 'ABC' > to_char(sysdate,'YYYY') ? Yes, it is! Because 'A'>'2'.
  3. Is 01/02/03 January, February or March? Read about ISO-8601.
  4. What would happen if you wish to change your "action days"? You would need to re-compile your code because you have hard-coded the values there.
  5. What if the tables would change? You would need to modify whole code - possibly bilions of changes in multiple procedures. Try to separate your SQL from the code logic. And avoid "if"s whenever possible - instead use "for variable in (...)".
  6. How is 2+2*2? Is it 8? No - it is 6. Use brackets.
  7. What would happen if the schema names were changed? You would need to re-compile a lot of code...
  8. Are you sure that you wish to use sysdate? Keep in mind that the data might be defined in different time-zones - some time zones might be shifted by ex. +3 hours and 51 minutes Very Happy

Previous Topic: Trigger Help
Next Topic: How can i lock a table to restrict from selection for another user (merged)
Goto Forum:
  


Current Time: Sat Dec 03 18:03:49 CST 2016

Total time taken to generate the page: 0.07152 seconds