Need help with selecting dates... 2

[bujin]

HELP!!! I've got a rather difficult, if not impossible question for you.

I am using a large Oracle-based database which has a report generator to build up queries (rather than enter the SQL manually). The problem I have is that I want to select records where the enrolment start date is greater than:

either 31st July (this year - 4)
or 31st July (this year - 3),

depending on the current month - if < 8, do the first one, otherwise do the second one.

For example, if I take this month (10), I wish to select all of the records where the enrolment start date is greater than 31 July 1997, but last April (say), it would have returned all records since 31 July 1996.

Please let me know if this is possible, and if so, HOW???

BTW, I am not really an IT professional! I am an Admin Assistant who has been assigned the task of creating some reports from the database. I DON'T know that much about programming!!!

Any help would be greatly appreciated. [sig][/sig]

 

[carp]

Bujin -
Fortunately, Oracle provides the non-ANSI-standard DECODE, which acts as an in-line if-then-else statement.

Try something like the following:

select * from your_table
where your_date_column > (SELECT DECODE(to_char(sysdate,'MM'),
1,to_date('31-JUL-'||to_char(to_number(to_char(sysdate,'YYYY'))-4), 'DD-MON-YY'))
2,to_date('31-JUL-'||to_char(to_number(to_char(sysdate,'YYYY'))-4), 'DD-MON-YY'))
3,to_date('31-JUL-'||to_char(to_number(to_char(sysdate,'YYYY'))-4), 'DD-MON-YY'))
4,to_date('31-JUL-'||to_char(to_number(to_char(sysdate,'YYYY'))-4), 'DD-MON-YY'))
5,to_date('31-JUL-'||to_char(to_number(to_char(sysdate,'YYYY'))-4), 'DD-MON-YY'))
6,to_date('31-JUL-'||to_char(to_number(to_char(sysdate,'YYYY'))-4), 'DD-MON-YY'))
7,to_date('31-JUL-'||to_char(to_number(to_char(sysdate,'YYYY'))-4), 'DD-MON-YY'))
8,to_date('31-JUL-'||to_char(to_number(to_char(sysdate,'YYYY'))-4), 'DD-MON-YY'))
to_date('31-JUL-'||to_char(to_number(to_char(sysdate,'YYYY'))-3), 'DD-MON-YY'))
FROM DUAL); [sig][/sig]

 

[THoey]

Whew... I thought I went through all of that coding, the computer locking up, etc, just to have Carp beat me to the punch. But then I saw the part where Bujin was wanting the date set to 31-JUL of either three or four years ago. So, even though the guts of the answer are still the same, I am going to still post my response...

The DECODE statement is very powerful magic, but used very little because it is kind of cryptic. The format for it is:

DECODE(Field_To_Test, 
       First_Value_To_Compare_It_To, 
       Value_To_Assign_If_Equal,
       Next_Value_To_Compare_It_To,
       Value_To_Assign_If_Equal,
       Default_Value_If_Nothing_Else_Matched
)

So, I suggest you use:

SELECT * 
FROM TableName
WHERE  EnrollDate > (DECODE(TO_NUMBER(TO_CHAR(sysdate, 'MM')),
                         1,Add_Months(TO_DATE('31-JUL-' || TO_CHAR(sysdate, 'YY')), -48),
                         2,Add_Months(TO_DATE('31-JUL-' || TO_CHAR(sysdate, 'YY')), -48),
                         3,Add_Months(TO_DATE('31-JUL-' || TO_CHAR(sysdate, 'YY')), -48),
                         4,Add_Months(TO_DATE('31-JUL-' || TO_CHAR(sysdate, 'YY')), -48),
                         5,Add_Months(TO_DATE('31-JUL-' || TO_CHAR(sysdate, 'YY')), -48),
                         6,Add_Months(TO_DATE('31-JUL-' || TO_CHAR(sysdate, 'YY')), -48),
                         7,Add_Months(TO_DATE('31-JUL-' || TO_CHAR(sysdate, 'YY')), -48),
                         Add_Months(TO_DATE('31-JUL-' || TO_CHAR(sysdate, 'YY')), -36)))

Hope this helps...

<<grumble>> That Carp guy is too quick... ;-)
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[bujin]

Thank you both very much. I'll try that out when I get to work tomorrow! Unfortunately, the report I'm writing is now throwing up more errors which I've got to try an solve, but your information will certainly help with the dates aspect of the report. [sig][/sig]

 

[MalcolmW]

I don't work in Oracle, so DECODE is not part of my lexicon. I'm am curious why you would use DECODE instead of a CASE statement, which appears to be more suited to this type of comparison.
The following example is my guess of how that might look in Oracle syntax
select * from your_table
where your_date_column >
CASE
WHEN to_number(sysdate,'MM') < 8 THEN
to_date('31-JUL-'||to_char(to_number(to_char(sysdate,'YYYY'))-4
ELSE
to_date('31-JUL-'||to_char(to_number(to_char(sysdate,'YYYY'))-3
END [sig][/sig]

 

[carp]

Well, the MAIN reason is that CASE is not part of Oracle SQL. I don't know if it is a feature of some new ANSI standard, but I have never seen CASE used with SQL. [sig][/sig]

 

[MalcolmW]

CASE is part of SQL 92 and SQL 99 - I'm not sure if it was part of SQL 89. It is basically similar in function to the DECODE function, but is more flexible/powerful.

CASE has been around for quite a while in SQL databases - first time I ran into it was with DB2 v2.1.

Thanks for the answer - I am very surprised Oracle doesn't yet support CASE, and that would certainly be sufficient cause not to use it - the CASE statement, that is [sig][/sig]

 

[carp]

Holy Toledo! Case DOES work in Oracle!
Thank-you very much for enlightening me - I guess I've been doing decode so long I didn't realize this was even available. New horizons are opened!! [sig][/sig]

 

[MikeLacey]

And *I've* been *avoiding* DECODE on the basis that it makes my head hurt -- I can do CASE though (|> [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. &lt;grin&gt;[/sig]

 

[THoey]

Again, Ditto Carp's post. I will have to give &quot;case&quot; a try next time I need to write something...

Thanks for the info Malcolm!!
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[MikeLacey]

I can't get it to work though <narrowed eyes....> [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. &lt;grin&gt;[/sig]

 

[carp]

Mike -

I had to do a little simplifying myself (although I think the problem stemmed from to_number instead of to_char at the date conversion):

1 select
2 CASE
3 WHEN to_char(sysdate,'MM') < 8 THEN
4 'Less Than 8'
5 ELSE
6 'Greater Than Or Equal To 8'
7 END
8* from dual;

CASEWHENTO_CHAR(SYSDATE,'M
--------------------------
Greater Than Or Equal To 8


If you STILL can't get this to work, it MAY be new in 8.1.

I found CASE Expressions on p. 5-13 of the 8.1.5 SQL Reference. There IS one note to be aware of:

&quot;The maximum number of arguments in a CASE expression is 255, and each WHEN ... THEN pair counts as two arguments. To avoid exceeding the limit of 128 choices, you can nest CASE expressions. That is expr1 can itself be a CASE expression.&quot; [sig][/sig]

 

[carp]

Just came up with a caveat of my own - This doesn't seem to work in PL/SQL!

SQL> SELECT
2 CASE
3 WHEN to_char(sysdate,'MM') < = 3 THEN
4 'First Quarter'
5 WHEN to_char(sysdate,'MM') < = 6 THEN
6 'Second Quarter'
7 WHEN to_char(sysdate,'MM') < = 9 THEN
8 'Third Quarter'
9 ELSE
10 'Fourth Quarter'
11 END
12 FROM dual;

CASEWHENTO_CHA
--------------
Fourth Quarter

Great! Now try the same thing in PL/SQL:

1 DECLARE
2 the_string varchar2(50);
3 BEGIN
4
5 SELECT
6 CASE
7 WHEN to_char(sysdate,'MM') < = 3 THEN
8 'First Quarter'
9 WHEN to_char(sysdate,'MM') < = 6 THEN
10 'Second Quarter'
11 WHEN to_char(sysdate,'MM') < = 9 THEN
12 'Third Quarter'
13 ELSE
14 'Fourth Quarter'
15 END
16 INTO the_string FROM dual
17* END
SQL> /
CASE
*
ERROR at line 6:
ORA-06550: line 6, column 6:
PLS-00103: Encountered the symbol &quot;CASE&quot; when expecting one of the following:
( * - + all mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current distinct max min prior sql stddev sum
unique variance execute the forall time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string>

Oh well - maybe in 9i!! [sig][/sig]

 

[bujin]

It's all very well using a CASE statement, but in the database I'm using, it gives you a series of forms to build up the query by which it searches the database. The resulting query is so messy that I couldn't understand it if I tried, and the forms (which list all of the available functions don't mention anything about CASE!!! Ah well, I'll just have to get to grips with decode!!! [sig][/sig]

 

[MalcolmW]

Carp,
I have profound ignorance of Oracle, which is only superceded by my ignorance of PL/SQL, but this doesn't stop me from suggesting this...
Maybe in PL/SQL you need to provide a column identifier, which is an optional part of the CASE statement.
I'm not sure what the Oracle syntax for that is, but the ANSI syntax (which I believe is NOT supported by Oracle) is:
CASE WHEN ..... THEN ......
ELSE ....
END AS SomeName
Based on the error message you got, I suspect the column identifier would have to precede the CASE statement, rather than follow it as in this syntax.
[sig][/sig]

 

[carp]

It is possible that Oracle has thrown its own kink into the works. However, I suspect that the PL/SQL for 8i went out the door not knowing about the CASE statement. This isn't the first time Oracle has done something like this.
I may have to do some investigation and then file a bug if necessary. [sig][/sig]

 

[bujin]

Just thought I'd let you know, I got the DECODE thing working in the database and it seems to give me the results I want. Thank you all for your help! [sig][/sig]

 

[karluk]

I see that this question has been resolved satisfactorily. However it may be worth pointing out that neither case nor decode is absolutely essential here. Oracle has powerful enough date functions to do the calculation directly.

There are always exactly 153 days in the last six months of the year. Therefore sysdate+153 will be either in the current year or next year depending on whether it's before Aug. 1. This allows one to truncate to Jan 1 and then subtract 42 months to get to July 31 of the desired year.

select last_day(add_months(trunc(sysdate+153,'yyyy'),-42))
from dual [sig][/sig]

 

[carp]

Very nice! And fast to boot! Experimenting, I find the average times (in ascending value):
Karluk's query.
CASE Statement.
DECODE Statement.

The only downside I can see to Karluk's query is it forces you to think a little bit!
The CASE allows more flexibility than DECODE in defining conditions, runs faster (due to that flexibility, I think: less conditions to evaluate). Unfortunately, it doesn't seem to work with PL/SQL (yet).

DECODE, though cryptic and apparently slower, DOES work in PL/SQL and functions well as an if-then-else type of operator.

Much thanks, Karluk!! [sig][/sig]