Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    London
    Posts
    76

    Unanswered: IF statement in select

    Hi there, I am trying to run an IFstatement within a select statement to replace certain values:
    I have a VAT field which contains the values 17.5, 0 and null, I want to convert these values into 0.175 and 0 in my query. i.e. select account_no, sum(cost*amount) as net_total, if(vate_rate = 17.5, 0.175,0) etc...but this does not work...please help!

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    select Decode( vatrate, 17.5, 0.175,0,0,null,0)

    decode( input, value1, result1, value2, result2,....., otherwise )

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Feb 2004
    Location
    London
    Posts
    76
    will this work in a query from the SQL window in Toad, this query is not in a procedure?

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Yes it will - but why not try it :-)

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    use decode

    Decode(Vat_rate,17.5,0.175,null, 0,0)

    it reads like:
    if vatrate = 17.5 then 0.175 elseif Isnull(vatrate) then 0 else 0

    Regards

    Editted to add: sorry for the late reply, my IE was hanging after "submit post" and took a long time to get going again.... Thus ... my appologies for this...
    Last edited by namliam; 05-25-04 at 07:10.

  6. #6
    Join Date
    Feb 2004
    Location
    London
    Posts
    76
    Thanks all...got it working

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •