How to use if statement within where clause in INFORMIX?

Question

I have the following query and the database is INFORMIX: Does anyone know how to realis it with example or references? I searched in the Informix doc and google but I can't find anything useful to solve this issue.

select i.doc_nr, i.doc_mut, i.year, i.mode, i.tb, i.patientid 
from tdba.include as i, t3
where i.year = t3.year
AND i.mode= t3.mode
AND i.tb=t3.tb
AND i.doc_mut= t3.doc_mut
-- here I wanna add another AND condition namely i.patientid= t3.patientid based on the following condition as a subquery.
EXISTS(SELECT omw_nr FROM tdba.aggromw AS omw WHERE omw.omw_nr = i.doc_mut)

Answer 1

You should be able to use the CASE expresion within the WHERE clause.

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqlt.doc/ids_sqt_080.htm

Something like this:

CREATE TABLE tdba.include (doc_nr int, doc_mut int, year int, mode int, tb int, patientid int);
CREATE TABLE t3( year int, mode int, tb int, doc_mut int, patientid int);
CREATE TABLE tdba.aggromw (omw_nr int);

INSERT INTO tdba.include  VALUES (1,1,1,1,1,13);
INSERT INTO t3  VALUES (1,1,1,1,14);

Without data in the 'subselect' table:

SELECT i.doc_nr, i.doc_mut, i.year, i.mode, i.tb, i.patientid 
FROM tdba.include as i, t3
WHERE i.year = t3.year
AND i.mode= t3.mode
AND i.tb=t3.tb
AND i.doc_mut= t3.doc_mut
AND i.patientid = CASE when EXISTS(SELECT omw_nr FROM tdba.aggromw AS omw WHERE omw.omw_nr = i.doc_mut)
                       then t3.patientid 
                       else  i.patientid
                  END
                ;  


>                               ;

-------
     doc_nr     doc_mut        year        mode          tb   patientid

          1           1           1           1           1          13

1 row(s) retrieved.

With a row into the 'aggromw' table the EXISTS returns 'true' which will 'force' the 'i.patientid=t3.patientid' filter

INSERT INTO tdba.aggromw  VALUES (1);

SELECT i.doc_nr, i.doc_mut, i.year, i.mode, i.tb, i.patientid 
FROM tdba.include as i, t3
WHERE i.year = t3.year
AND i.mode= t3.mode
AND i.tb=t3.tb
AND i.doc_mut= t3.doc_mut
AND i.patientid = CASE when EXISTS(SELECT omw_nr FROM tdba.aggromw AS omw WHERE omw.omw_nr = i.doc_mut)
                       then t3.patientid 
                       else  i.patientid
                  END
                ;  

-------
    doc_nr     doc_mut        year        mode          tb   patientid


No rows found.

>