Home » SQL & PL/SQL » SQL & PL/SQL » Can I use between and case in a where clause?
Can I use between and case in a where clause? [message #231000] Fri, 13 April 2007 12:13 Go to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
In my select statement below, can I use in the where clause a between statement with a case in it to find the ranges in dates based on a certain month? thanks for your help!

SELECT m.memb_appeal_code, m.memb_type_code, 'AMT_PD_FY_YTD' AS AMT_TYPE, Sum(m.MEMB_AMT_PAID) AS AMT
FROM MEMB m INNER JOIN MEMB_ENTITY me ON m.MEMB_NUMBER = me.MEMB_NUMBER
WHERE m.start_date
BETWEEN (case when to_number(to_char(m.start_date,'MM')) < 7 then
to_date('0701' || to_char(sysdate,'YYYY'), 'mm/dd/yyyy')
end)
AND sysdate

AND m.start_date
BETWEEN (case when to_number(to_char(m.start_date,'MM')) >= 7 then
to_date('0701' || to_number(to_char(sysdate,'YYYY')-1), 'mm/dd/yyyy')
end)
AND sysdate
GROUP BY m.memb_appeal_code, m.memb_type_code
Re: Can I use between and case in a where clause? [message #231013 is a reply to message #231000] Fri, 13 April 2007 12:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't keep us in suspense any longer.
What happened when you tried it?
Re: Can I use between and case in a where clause? [message #231015 is a reply to message #231013] Fri, 13 April 2007 12:48 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
Well, I got nothing returned so I tried it this way cause our fiscal year starts july 1st. I got some returned but not anything after dec.

SELECT m.memb_appeal_code, m.memb_type_code, m.start_date, m.MEMB_AMT_PAID AS AMT
FROM MEMB m INNER JOIN MEMB_ENTITY me ON m.MEMB_NUMBER = me.MEMB_NUMBER
WHERE m.start_date
BETWEEN (case when to_number(to_char(m.start_date,'MM')) < 7 then
to_date('0701' || to_char(sysdate,'YYYY'), 'mm/dd/yyyy')
when to_number(to_char(m.start_date,'MM')) >= 7 then
to_date('0701' || to_number(to_char(sysdate,'YYYY')-1), 'mm/dd/yyyy')
end)
AND sysdate
order by m.start_date DESC
Re: Can I use between and case in a where clause? [message #231019 is a reply to message #231000] Fri, 13 April 2007 12:58 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
It might work better if the mask in TO_DATE actually matched the string being provided.
Re: Can I use between and case in a where clause? [message #231020 is a reply to message #231019] Fri, 13 April 2007 13:01 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
Won't it change it from the string to a date format in mm/dd/yyyy with the to_date function?
Re: Can I use between and case in a where clause? [message #231022 is a reply to message #231000] Fri, 13 April 2007 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Won't it
IT? which IT? what IT?
>change it
It? which IT? what it?
>from the string to a date format in mm/dd/yyyy with the to_date function?

Please explain/show/describe where in the string you provide contains any "slash" character; which exists in the mask.
icon14.gif  Re: Can I use between and case in a where clause? [message #231023 is a reply to message #231019] Fri, 13 April 2007 13:11 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
OK. I finally got it to work. Here is my SQL:

SELECT m.memb_appeal_code, m.memb_type_code, m.start_date, m.MEMB_AMT_PAID AS AMT
FROM MEMB m INNER JOIN MEMB_ENTITY me ON m.MEMB_NUMBER = me.MEMB_NUMBER
WHERE m.start_date
BETWEEN (case when to_number(to_char(m.start_date,'MMDD')) < 0701 then
to_date('0101' || to_char(sysdate,'YYYY'), 'mm/dd/yyyy')
when to_number(to_char(m.start_date,'MMDD')) >= 0701 then
to_date('0701' || to_number(to_char(sysdate,'YYYY')-1), 'mm/dd/yyyy')
end)
AND sysdate
order by m.start_date DESC

THanks for viewing!
Re: Can I use between and case in a where clause? [message #231025 is a reply to message #231019] Fri, 13 April 2007 13:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
anacedent wrote on Fri, 13 April 2007 20:09
>Won't it
IT? which IT? what IT?
>change it
It? which IT? what it?
>from the string to a date format in mm/dd/yyyy with the to_date function?


Easy...

anacedent wrote on Fri, 13 April 2007 19:58
It might work better if the mask in TO_DATE actually matched the string being provided.

It might?? which IT? what IT??
Re: Can I use between and case in a where clause? [message #231030 is a reply to message #231025] Fri, 13 April 2007 13:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What anacedent tried to point out was that you construct your date as 0701||YYYY, but you do a to_date using mask dd/mm/yyyy.
Note the slashes in the format that are not there in your date; the two don't match
Re: Can I use between and case in a where clause? [message #231032 is a reply to message #231030] Fri, 13 April 2007 13:45 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
oh, ok. i see. If I still get back results that are correct, what would that mean then? Should i change it anyway?
Re: Can I use between and case in a where clause? [message #231033 is a reply to message #231032] Fri, 13 April 2007 13:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It's better to change it. One good result does not guarantee that it always will work.
Plus, it's a matter of good coding..
Re: Can I use between and case in a where clause? [message #231035 is a reply to message #231033] Fri, 13 April 2007 13:48 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
ok. thanks for the advice.
So, I would do:
to_date('01/01/' || to_char(sysdate,'YYYY'), 'mm/dd/yyyy')
Right?
Re: Can I use between and case in a where clause? [message #231038 is a reply to message #231035] Fri, 13 April 2007 13:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
yes, or, trunc(sysdate, 'YYYY')
no need for to_char, concatenation or to_date.
trunc(sysdate, 'YYYY') returns 01-jan of this year
add_months(trunc(sysdate, 'YYYY'), 05) returns 07-JUL of last year
Re: Can I use between and case in a where clause? [message #231040 is a reply to message #231038] Fri, 13 April 2007 13:58 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
oh, ok. thanks alot!
Re: Can I use between and case in a where clause? [message #231053 is a reply to message #231000] Fri, 13 April 2007 15:52 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
shouldn't it be

add_months(trunc(sysdate, 'YYYY'), -6)

>select add_months(trunc(sysdate,'yyyy'),-6) from dual;

ADD_MONTH
---------
01-JUL-06
Re: Can I use between and case in a where clause? [message #231083 is a reply to message #231053] Sat, 14 April 2007 01:22 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You're right, Bill.
That's what you get from doing things from the top of your head without checking them. (12 months in a year, so to get to month 7 you need to subtract 5. Forgot that you start at month 1 and not 12 Smile)
Previous Topic: EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES?
Next Topic: ORA-03113 Error - Help!
Goto Forum:
  


Current Time: Wed Dec 07 18:22:31 CST 2016

Total time taken to generate the page: 0.09678 seconds