Toad World® Forums

Where Statement with a Case


#1

Hi group!

I’m a newbie SQL coder. I’m trying to insert a case statement within a WHERE statement. Here’s the code I’ve written:

Where a16.FULL_DATE between
(Case when to_char((Select sysdate-4 From Dual),‘mm’) <> to_char((Select sysdate From Dual),‘mm’)
Then trunc(ADD_MONTHS((LAST_DAY((Select SysDate-4 From Dual))),-1)+1)
Else trunc(ADD_MONTHS((LAST_DAY((Select SysDate From Dual))),-1)+1)
End)

and

(Case when to_char((Select sysdate-4 From Dual),‘mm’) <> to_char((Select sysdate From Dual),‘mm’)
Then add_months(trunc((Select SysDate-4 from dual)),-1)
Else TRUNC((Select SysDate from Dual)-2)
End)

I’m trying to compare the current month number against the current month of today’s date minus 4 days. If these two don’t match, I need for it to report the prior months information from the 1st to today’s date minus 4. However the above code brings nothing back. I’ve tried it with and without “From Dual” with no effect. I am not getting any kind of error messages. Does anyone have any thoughts on how I can accomplish this?

In advance, thanks for your help.

Don


#2

So - If the date is the 1st, 2nd, 3rd, or 4th, you want to return last month, otherwise, return this month? The query below demonstrates that. In my example below I joined to dba_objects. So on the 1st through 4th, it will return a list of objects created the month before (of any year), but any other day of the month, it will return a list of objects created that month (of any year).

By the way, in your example, you say “select sysdate from dual” a lot. FYI, you can substitute just “sysdate” for all of that.

with the_month_I_want as

(select case when to_char(sysdate, ‘dd’) in (‘01’, ‘02’, ‘03’, ‘04’) then

to_char(add_months(sysdate, -1), ‘mm’)

else

to_char(sysdate, ‘mm’)

end as the_month

from dual)

select *

from dba_objects d, the_month_I_want

where to_char(d.created, ‘mm’) = the_month_I_want.the_month


#3

John,

You are correct as I do want to get the previous months data if we are in a new month and the date is the first through the forth. I did know that I could drop the “From Dual”. But because it wouldn’t return data, I tried modifying the code with that in it to see if I could get data back.

Does this code get inserted as part of my WHERE clause? If it’s not, I’m guessing you’re inserting the CASE into the SELECT clause and using that to limit what I need to return.

Thanks for you help.

Don


#4

The nice thing about the “With” clause at the top of your query is that you can use in any part of the main query.

Use what I wrote above in your query the same way I used it above. So leave everything above “Select *” as I wrote it above. Substitute in your query for “Select *”, and join your date column as I joined it above. And If you care about the year, you should change the ‘mm’ to ‘mmyyyy’ everywhere you see it.


#5

John,

You’re brilliant! Thank you for the help!! I didn’t know that a WITH clause existed. I’ve not taken the next course in SQL. I’ll do that after the first of the year. So you’ve moved me a step ahead!

To build on what you gave me, here’s what I did:

WITH GETTING_MONTH as

(select

case when to_char(sysdate, ‘dd’) in (‘01’, ‘02’, ‘03’, ‘04’)

then to_date(last_day(add_months(sysdate, -2)))+1

else to_date(last_day(add_months(sysdate, -1)))+1

end as BEG_DATE,

case when to_char(sysdate, ‘dd’) in (‘01’, ‘02’, ‘03’, ‘04’)

then to_date(last_day(add_months(sysdate, -1)))

else to_date(sysdate-2)

end as END_DATE

from dual)

SELECT a11.*

From crmmart.F_RES_RM_NT_NET_RATE_PLAN a11,

donacwil.GETTING_MONTH d19

WHERE a11.FULL_DATE between TO_DATE(d19.BEG_DATE) AND TO_DATE(d19.END_DATE)

I’ve tested this and it worked perfectly!! You are the man!

Thanks again for the help! This is AWESOME!

Don (the newbie)


#6

Yeah “With” can be really handy for simplifying a query. Glad I could help.

By the way, you can take out the to_date() that’s around LAST_DAY. LAST_DAY is already a date. Same with “SYSDATE - 2”. Also, in your select, you might want to change “donacwil.GETTING_MONTH” to just “GETTING_MONTH”. You don’t need a schema name there since you are just referencing the subquery in the WITH clause.