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
seahawk9Asked:
Who is Participating?
 
flutophilusConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.