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!

Advanced Condition select statment

Discussion in 'SQL PL/SQL' started by Mohamed_Khateeb, Sep 10, 2014.

  1. Mohamed_Khateeb

    Mohamed_Khateeb Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    id Type Amount
    ----------------------------------------
    1 Prepayment 10
    2 Prepayment 0
    3 Standard 0
    4 Standard 20


    I need to select All except Standard Invoice which has amount (except ID 4)
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Try something like:

    Code (SQL):
    SELECT * FROM your_table_name y
    WHERE y.TYPE != 'Standard' OR y.amount > 0;
    If this is not what you require, then please provide more details on your table ...
     
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The OP indicated except ID 4. I take that to mean he wants all of the rows except 4 -- where the type was 'Standard' and the value 20. The 'OR amount > 0' in your example would return that row. If I am understanding his request, the condition would need to be 'OR y = 0'. That would include row 3 but exclude 4.

    Code (Text):
    SELECT *
    FROM   your_table_name y
    WHERE  y.type   != 'Standard'
    OR     y.amount = 0;
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Yes you're right Matthew (typo error ;).
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    alternative ...
    Code (SQL):

    WITH
    your_table AS
    (
    SELECT 1 id, 'Prepayment' TYPE , 10 amount FROM dual UNION ALL
    SELECT 2 ,'Prepayment' ,0 FROM dual UNION ALL
    SELECT 3 ,'Standard', 0 FROM dual UNION ALL
    SELECT 4, 'Standard', 20 FROM dual
    )
    SELECT * FROM your_table y
    WHERE 0 = CASE WHEN y.TYPE = 'Standard' THEN sign(y.amount) ELSE 0 END ;

    SQL>
     
            ID TYPE           AMOUNT
    ---------- ---------- ----------
             1 Prepayment         10
             2 Prepayment          0
             3 Standard            0