Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Unanswered: SQL Informix if statement possible?

    Hi I am new to informix and SQL but need to know if its possible to perform an if statement or similar using informix SQL,

    I have two fields in a table, one qty and the other palletqty

    what I need to do is something like

    If qty < palletqty then return qty else return palletqty

    Its an older version of informix running on Sco Openserver 5

    any help would be appreciated.

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,

    you case use "case when condition then statements... else statements end case" syntax

    Not sure in which version it has been introduced. Just test.

    Else you can always write a specific stored procedure that will format your result according to your needs, doing roughly the following
    and call it from your sql statement like this

    select myprocedure(qty ,palletqty)
    from table
    where .....

    and put the procedural code in the stored procedure

    Which informix version are you running?

    E.

  3. #3
    Join Date
    Sep 2012
    Posts
    5
    Hi thank you for taking the time to reply, I am new to both SQL and Informix

    the version I am running is IDS.2000 9.21.UC2

    I will try and explain what I want better.

    I have a table called customers, there are many fields within this table, the only one's I am interested in are qty and palletqty, not sure if case is supported in the version I have but I need something that will look at both values if qty is less than palletqty return qty AS 'required' else return palletqty AS 'required'

    Just not sure of the correct syntax to use.

    Again many thanks for any assistance.
    Last edited by Firefox2k2; 09-18-12 at 13:00.

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    You can find a good description of the syntax here

  5. #5
    Join Date
    Sep 2012
    Posts
    5
    Thanks for the help, getting there I now have the following that does what I require:

    case when qty < packs*parts THEN qty ELSE (parts*packs) END

    Thats does it however it doesnt give it a field name, I need to return it AS something however when I try the below it throws up a syntax error.


    case when qty < packs*parts THEN qty ELSE (parts*packs) AS newqty END

  6. #6
    Join Date
    Oct 2012
    Posts
    1
    case
    when qty < packs*parts THEN qty
    ELSE (parts*packs)
    END newqty

Posting Permissions

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