• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7568
  • Last Modified:

How to do an if/else in an Informix SQL Query

I am doing a simple select, but I am not sure of the syntax for an if/else statement in informix.
I want the mobile number to be from A2.phone but if A2.phone is null and A4.phone is not null then I want A4.phone to also be the mobile number.

Here is the select:
select I.id,
        pzfirstname(I.fullname) fname,
        pzlastname(I.fullname) lname,
        A.phone office,
        A2.phone mobile,
        A3.line1 || A3.line2 email,
        A4.phone mobile2,
        "Staff" contact_type
from id_rec I
0
seahawk9
Asked:
seahawk9
1 Solution
 
flutophilusCommented:
You can't do an IF/ELSE in informix sql, you must write a stored procedure.
Here's an example of an IF/ELSE from the documentation that compares two strings that are passed in
CREATE PROCEDURE str_compare (str1 CHAR(20), str2 CHAR(20))
RETURNING INT;
DEFINE result INT;
IF str1 > str2 then
result =1;
ELIF str2 > str1 THEN
result = -1;
ELSE
result = 0;
END IF
RETURN result;
END PROCEDURE -- str_compare

You can create a stored procedure by using dbaccess - you can't create a stored procedure using isql.
You can run a stored procedure by using the EXECUTE PROCEDURE .. INTO ... syntax.
Check out the Informix Guide to SQL : Reference and Informix Guide to SQL : Syntax, available free online via your favourite search engine.
0
 
Geoffers99Commented:
Rather than a stored procedure you could simply use a CASE expression.

select I.id,
        pzfirstname(I.fullname) fname,
        pzlastname(I.fullname) lname,
        A.phone office,
        CASE
        WHEN A2.phone is null
              THEN A4.phone
         ELSE
               A2.phone
         END mobile,
        A3.line1 || A3.line2 email,
        A4.phone mobile2,
        "Staff" contact_type
from id_rec I
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now