help needed in this SQL

[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 .