Home » SQL & PL/SQL » SQL & PL/SQL » case statement in where clause
case statement in where clause [message #214730] Wed, 17 January 2007 16:08 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
I have a table containing several columns with one of them is a date datatype.
I want to pull the data from this table if the date matches certain criteria
1) If there is data in the table with date = sysdate-1 then pull these records
2) If there are no records for "sysdate-1" pull the last date value (max date value) in the table and display all the records for this date.

I was thinking of approaching this using case in my where clause, but am not able to proceed. version is 9.2.0.6
Any leads would help.

Regards
Re: case statement in where clause [message #214733 is a reply to message #214730] Wed, 17 January 2007 17:06 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Will that table have rows where that date column in question would have sysdate ( or later ) as its column value ?
Re: case statement in where clause [message #214734 is a reply to message #214730] Wed, 17 January 2007 17:14 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
IMO, no "CASE" is required.
WHERE DATE_CREATED < TRUNC(SYSDATE)
AND DATE_CREATED = (max date value)
Re: case statement in where clause [message #214905 is a reply to message #214733] Thu, 18 January 2007 08:44 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Hi,
It might have SYSDATE as the value, but no dates later than SYSDATE.


Regards,
Re: case statement in where clause [message #214906 is a reply to message #214734] Thu, 18 January 2007 08:46 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
"WHERE DATE_CREATED < TRUNC(SYSDATE)
AND DATE_CREATED = (max date value) "
This query will pickup all values that matches < TRUNC(SYSDATE) and (max date value).
What I require is
If there are rows for SYSDATE-1, only pull those records that matches this criteria. If there are no rows for SYSDATE-1, then pull the max date value.

Regards,
Re: case statement in where clause [message #214914 is a reply to message #214906] Thu, 18 January 2007 09:50 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
I tried using this query, but it retrieves rows for sysdate-1 and sysdate-2 as well.
(PS. here I have hard coded the values for the else clause to SYSDATE-2 to check whether the case statement works or not)

SELECT * FROM seosdata WHERE TRUNC(timstamp,'dd') =
CASE WHEN TRUNC(timstamp,'dd') = TRUNC(SYSDATE-1,'dd') THEN TRUNC(SYSDATE-1,'dd') ELSE TRUNC(SYSDATE-2,'dd') END


I tried the following logic but group by expression is not allowed in the case statement in this case.
SELECT * FROM seosdata WHERE TRUNC(timstamp,'dd') =
CASE WHEN COUNT(CASE WHEN TRUNC(timstamp,'dd') = TRUNC(SYSDATE-2,'dd') THEN 1 ELSE NULL END) >1 THEN TRUNC(SYSDATE-2,'dd') ELSE TRUNC(SYSDATE-4,'dd') END


Any leads will help.
Re: case statement in where clause [message #214929 is a reply to message #214914] Thu, 18 January 2007 11:53 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
select * from test_dummy

NAME	VALUE	CDATE
A	1	1/1/2007
B	2	1/2/2007
C	3	1/3/2007
D	4	1/3/2007

select * from test_dummy
where decode(cdate,sysdate-1,cdate,cdate)=decode(cdate,sysdate-1,cdate,(select max(cdate) from test_dummy))
Re: case statement in where clause [message #214932 is a reply to message #214929] Thu, 18 January 2007 12:01 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Even your query retrieves data for both dates. For eg.

CREATE TABLE SEODAT
( A CHAR(1),
TIMSTAMP DATE);

INSERT INTO SEODAT VALUES ( '1',TO_DATE('1/16/2007','MM/DD/YYYY'));
INSERT INTO SEODAT VALUES ( '2',TO_DATE('1/16/2007','MM/DD/YYYY'));
INSERT INTO SEODAT VALUES ( '3',TO_DATE('1/17/2007','MM/DD/YYYY'));
INSERT INTO SEODAT VALUES ( '4',TO_DATE('1/17/2007','MM/DD/YYYY'));
INSERT INTO SEODAT VALUES ( '6',TO_DATE('1/17/2007','MM/DD/YYYY'));

--sysdate is 1/18/2007
SELECT * FROM seodat WHERE
DECODE(TRUNC(timstamp,'dd'),TRUNC(SYSDATE-1,'dd'),TRUNC(timstamp,'dd'),TRUNC(timstamp,'dd')) = DECODE(TRUNC(timstamp,'dd'),TRUNC(SYSDATE-1,'dd'),TRUNC(timstamp,'dd'),TRUNC(SYSDATE-2,'dd'))

O/p is dates for both 1/17 and 1/16. In case rows for 1/17 is present, then only display that, dont go for 1/16 .

Regards
Re: case statement in where clause [message #214936 is a reply to message #214932] Thu, 18 January 2007 12:23 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member

select * from test_dummy 

NAME	VALUE	CDATE
A	1	1/1/2007
B	2	1/2/2007
C	3	1/3/2007
D	4	[B]1/17/2007[/B]


remember i have used decode and

select * from test_dummy
where decode(cdate,sysdate-1,cdate,cdate)
	  =decode(cdate,sysdate-1,cdate,(select max(cdate) from test_dummy))

NAME	VALUE	CDATE
D	4	1/17/2007

is this not what you desire????



NAME	VALUE	CDATE
A	1	1/1/2007
B	2	1/2/2007
C	3	1/16/2007
D	4	1/16/2007


select * from test_dummy
where decode(cdate,sysdate-1,cdate,cdate)
	  =decode(cdate,sysdate-1,cdate,(select max(cdate) from test_dummy))

output 

NAME	VALUE	CDATE
C	3	1/16/2007
D	4	1/16/2007

in decode i have kept max and not sysdate-2



If there are no rows for SYSDATE-1, then pull the max date value.

[Updated on: Thu, 18 January 2007 12:29]

Report message to a moderator

Re: case statement in where clause [message #214938 is a reply to message #214936] Thu, 18 January 2007 12:29 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Hi,
I am sorry, I completely mis-typed the last part of the query
Yes, you query satisfies the requirement...

Thanks for the help.

Regards,
Re: case statement in where clause [message #214939 is a reply to message #214938] Thu, 18 January 2007 12:30 Go to previous message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
You are most welcome
Previous Topic: Question about SNAPSHOT
Next Topic: How to upload data from excel sheet to databasetable
Goto Forum:
  


Current Time: Tue Dec 06 10:22:06 CST 2016

Total time taken to generate the page: 0.09761 seconds