DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: help needed in this SQL

  1. #1
    Join Date
    Oct 2006
    Posts
    1

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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

  3. #3
    Join Date
    May 2005
    Location
    France
    Posts
    34
    Quote 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

  4. #4
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

  5. #5
    Join Date
    May 2005
    Location
    France
    Posts
    34
    Quote 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

  6. #6
    Join Date
    Oct 2006
    Posts
    175
    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

  7. #7
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Quote 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
  •  


Click Here to Expand Forum to Full Width