-
help needed in this SQL
how do i implement this thing
select id,date,pact
from case
if the below conditions gives a matching record then don,t go to the 2nd condition
1)where pact='abcd'
and date='11-oct-2006'if the 1) condition doesn,t give any record then implement this below one
2)[COLOR=MediumTurquoise]where pact='abcd'
and date='16-oct-2006'[/COLOR]
it is kind of if else analogy to be implemented on select query
thanks in advance
-
there's this wonderful thing called IN. learn it, love it.
SELECT * FROM foo WHERE myDate in ('01-aug-2006','08-aug-2006')
Jeff Hunter
-
Originally Posted by marist89
there's this wonderful thing called IN. learn it, love it.
SELECT * FROM foo WHERE myDate in ('01-aug-2006','08-aug-2006')
IN is undoubtedly useful, maybe "wonderful", but it does not directly give the result the OP is asking for, though .
If there can be only one record matching one criteria or the other, then this will do what you want :
Code:
rbaraer@Ora10g> CREATE TABLE t (pact VARCHAR2(30), aDate DATE, sometext VARCHAR2(100));
Table created.
rbaraer@Ora10g> INSERT INTO t(pact, adate, sometext) values ('abcd', to_date('2006/10/11','YYYY/MM/DD'), 'text 11');
1 row created.
rbaraer@Ora10g> INSERT INTO t(pact, adate, sometext) values ('abcd', to_date('2006/10/16','YYYY/MM/DD'), 'text 16');
1 row created.
rbaraer@Ora10g> INSERT INTO t(pact, adate, sometext) values ('abcd', to_date('2006/10/18','YYYY/MM/DD'), 'text 18');
1 row created.
rbaraer@Ora10g> SELECT CASE V1.Cnt
2 WHEN 1 THEN (SELECT sometext FROM t WHERE adate = to_date('2006/10/11','YYYY/MM/DD'))
3 ELSE (SELECT sometext FROM t WHERE adate = to_date('2006/10/16','YYYY/MM/DD'))
4 END Sometext
5 FROM
6 (SELECT COUNT(*) Cnt FROM t WHERE adate = to_date('2006/10/11','YYYY/MM/DD')) V1
7 ;
SOMETEXT
----------------------------------------------------------------------------------------------------
text 11
rbaraer@Ora10g> DELETE FROM t WHERE adate = to_date('2006/10/11','YYYY/MM/DD');
1 row deleted.
rbaraer@Ora10g> SELECT CASE V1.Cnt
2 WHEN 1 THEN (SELECT sometext FROM t WHERE adate = to_date('2006/10/11','YYYY/MM/DD'))
3 ELSE (SELECT sometext FROM t WHERE adate = to_date('2006/10/16','YYYY/MM/DD'))
4 END Sometext
5 FROM
6 (SELECT COUNT(*) Cnt FROM t WHERE adate = to_date('2006/10/11','YYYY/MM/DD')) V1
7 ;
SOMETEXT
----------------------------------------------------------------------------------------------------
text 16
rbaraer@Ora10g>
This is very specific though, so maybe this more generic solution may match your needs. The idea is to order by date and then select the first row matching pact = 'abcd' :
Code:
SELECT sometext FROM
(
SELECT *
FROM t
WHERE pact = 'abcd'
ORDER BY adate ASC
)
WHERE ROWNUM = 1;
HTH & Regards,
rbaraer
Edit : BTW kush_anuj, if as I hope, "date" is a column with DATE datatype, you should use TO_DATE each time you are converting a string to a DATE or TIMESTAMP, as well as TO_CHAR for the other way. Implicit are really bad... problems waiting to arise. Furthermore, reserved words such as "date" should not be used as column names. There are good reasons why they are reserved .
Last edited by RBARAER; 10-18-2006 at 07:35 AM.
Reason: Implicit convertions / reserved words
-
Making your case statement generic
Code:
SELECT CASE V1.Cnt
WHEN 0 THEN (SELECT sometext FROM t WHERE adate = to_date ('2006/10/16','YYYY/MM/DD'))
ELSE (SELECT sometext FROM t WHERE adate = to_date('2006/10/11','YYYY/MM/DD'))
END Sometext
FROM
(SELECT nvl(COUNT(*),0) Cnt FROM t WHERE adate = to_date('2006/10/11','YYYY/MM/DD')) V1;
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Originally Posted by simply_dba
Making your case statement generic
Code:
SELECT CASE V1.Cnt
WHEN 0 THEN (SELECT sometext FROM t WHERE adate = to_date ('2006/10/16','YYYY/MM/DD'))
ELSE (SELECT sometext FROM t WHERE adate = to_date('2006/10/11','YYYY/MM/DD'))
END Sometext
FROM
(SELECT nvl(COUNT(*),0) Cnt FROM t WHERE adate = to_date('2006/10/11','YYYY/MM/DD')) V1;
You're right, it is more generic this way. Thanks for the improvement.
Are you sure you need the NVL on count(*) ? If there is no match the count will simply return 0, won't it ?
Regards,
rbaraer
-
You can use the 'CASE...WHEN...ELSE' SELECT Clause in a situation like this. 'CASE...WHEN...ELSE' clause works similar to C 'IF...THEN...ELSE' clause.
gtcol
-
Originally Posted by RBARAER
You're right, it is more generic this way. Thanks for the improvement.
Are you sure you need the NVL on count(*) ? If there is no match the count will simply return 0, won't it ?
Regards,
rbaraer
Well I havn't tested it. I added NVL fearing that it would return "No Data found"
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|