Ok, IIF would not work, because it would not use my indexes (as I just tested). I guess, I have to go with my original idea now, which is already working.

>>This is my best/worst view, depending on how you look at it. All it does is create a list of records that meet six fully optional specified conditions. The last two options pull records from other views (or not).
>>

>>SELECT Enroll.enrollid;
>> FROM ;
>>     smagic!enroll;
>> WHERE ( ( ( ( (  IIF(EMPTY(?vp_scheduleid),.T.,Enroll.scheduleid=(?vp_scheduleid)) ;
>>   AND  IIF(EMPTY(?vp_programid),.T.,Enroll.programid=(?vp_programid))  );
>>   AND  IIF(EMPTY(?vp_startdateid),.T.,Enroll.startdateid=(?vp_startdateid))  );
>>   AND  IIF(EMPTY(?vp_enddateid),.T.,Enroll.enddateid=(?vp_enddateid))  );
>>   AND  (  EMPTY(?vp_actionid) ;
>>   OR  Enroll.enrollid IN (SELECT enrollid FROM v_actionflag) ) );
>>   AND  (  EMPTY(?vp_hasflag) ;
>>   OR  Enroll.enrollid IN (SELECT enrollid FROM v_mustflag) ) );
>>   AND  Enroll.enrollid NOT IN (SELECT enrollid FROM v_notflag)
>>

>
>I tried your idea, but it didn't work for me. It returns 0 records and it is slow. I have several records starting with "GREEN". Do you see, what did I do wrongly here?
>

>vp_cClient_Account_Number = ""
>vp_cLast = "Green%"
>vp_cFirstName = ""
>vp_cMiddle = ""
>vp_cSSN = ""
>vp_cAreaCode = ""
>vp_cExchange = ""
>vp_cFour = ""
>vp_cExchange_Four = ""
>SELECT MIN(Trans.ctrans_pk) AS ctrans_pk, ;
>		Trans.cclient_account_number, ;
>		MIN(Patients.ddob) AS ddob, ;
>		MIN(Trans.tadmit_date) AS dos, ;
>		MIN(PADR(NVL(ALLTRIM(Names.cl_name)+", ", "")+NVL(ALLTRIM(Names.cf_name)+" ", "")+NVL(Names.cm_initial, ""), 50)) AS cpatient_name, ;
>		MIN(Product_lines.cproduct_line) AS cproduct_line, ;
>		MIN(Trans.cresolution_codes_fk) AS cresolution_codes_fk, ;
>		MIN(Trans_status_codes.cstatus_codes_fk) AS cstatus_codes_fk, ;
>		MIN(PADR(NVL(Phones.carea_code, "")+NVL(Phones.cexchange, "")+NVL(Phones.clast_four, ""), 10)) AS cphone ;
>	FROM mmviscollect!patients INNER ;
>		JOIN mmviscollect!trans ;
>				ON Patients.cpatients_pk = Trans.cpatients_fk ;
>		LEFT JOIN mmviscollect!phones ;
>				ON Patients.cpatients_pk = Phones.cpointer_fk INNER ;
>		JOIN mmviscollect!trans_status_codes ;
>				ON Trans.ctrans_pk = Trans_status_codes.ctrans_fk INNER ;
>		JOIN mmviscollect!product_lines ;
>				ON Product_lines.cproduct_lines_pk = Trans.cproduct_lines_fk INNER ;
>		JOIN mmviscollect!names ;
>				ON Patients.cpatients_pk = Names.cpointer_fk ;
>	WHERE IIF(EMPTY(?vp_cClient_Account_Number),.t., Trans.cclient_account_number LIKE ( ?vp_cClient_Account_Number )) ;
>		AND Trans_status_codes.iactive_flag = ( 1 ) ;
>		AND IIF(EMPTY(?vp_cLast),.t.,Names.cl_name LIKE ( ?vp_cLast )) ;
>		AND IIF(EMPTY(?vp_cFirstName),.t.,Names.cf_name LIKE ( ?vp_cFirstName )) ;
>		AND IIF(EMPTY(?vp_cMiddle),.t.,Names.cm_initial LIKE ( ?vp_cMiddle )) ;
>		AND IIF(EMPTY(?vp_cSSN),.t.,Patients.cssn LIKE ( ?vp_cSSN )) ;
>		AND IIF(EMPTY(?vp_cAreaCode),.t.,Phones.carea_code LIKE ( ?vp_cAreaCode )) ;
>		AND IIF(EMPTY(?vp_cExchange_Four),.t.,( Phones.cexchange+Phones.clast_four ) LIKE ( ?vp_cExchange_Four )) ;
>	GROUP BY Trans.cclient_account_number