help needed in this SQL

[kush_anuj]

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

[marist89]

there's this wonderful thing called IN. learn it, love it.

SELECT * FROM foo WHERE myDate in ('01-aug-2006','08-aug-2006')

[RBARAER]

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 :

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' :

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 .

[simply_dba]

Making your case statement generic

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;

[RBARAER]

Making your case statement generic

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

[gtcol]

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

[simply_dba]

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"