1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help me to solve this query~~~ thanks

Discussion in 'SQL PL/SQL' started by virusx1984, Oct 7, 2010.

  1. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    I have a table like:

    col_A col_B
    333 4/29/2010
    444 4/29/2010
    55 5/1/2010
    66 4/29/2010
    77 5/30/2010

    how to write the sql and get the result like:

    col_A col_B
    0 4/29/2010
    0 4/29/2010
    55 5/1/2010
    0 4/29/2010
    77 5/30/2010

    if col_B < 5/1/2010 then col_A =0
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What have you written to solve this?
     
  3. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    I have worked through it.

    Code (SQL):
    SELECT *
      FROM (SELECT 0 AS Col_a, col_b FROM test WHERE col_b < '1-may-2010')
    UNION ALL (SELECT * FROM test WHERE col_b >= '1-may-2010');
    but I still want to see if there is a sql that is more simple than that.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
     
    SQL> SELECT
      2          CASE WHEN col_b <  to_date('05/1/2010','MM/DD/RRRR') THEN 0 ELSE col_a END col_a,
      3          col_b
      4  FROM sampletest;
         COL_A COL_B
    ---------- ---------
             0 29-APR-10
             0 29-APR-10
            55 01-MAY-10
             0 29-APR-10
            77 30-MAY-10
    SQL>

     
     
    virusx1984 likes this.
  5. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Thank you very much!