Any ideas about getting rid of the IIF() in the SQL below. The Customer and LineItem tables may or may not have a relation to WorkOrder table and the view parameters for Customer and LineItem may be "%" or some text.

If I just compare the Customer or LineItem and the view parameters are "%" and there is no related record that seems to bust the left outer join and drop the record because the cursor field is then NULL

I try to avoid using IIF() in SQL, although maybe it's not that big a deal don't really know.

TIA,
Cris

SELECT Workorder.*, Customer.cname AS ccustname,;
  Woitem.cname AS citemname, Customer.ctermsreffullname;
 FROM ;
     appdata!workorder ;
    LEFT OUTER JOIN appdata!customer ;
   ON  Workorder.ccustomerid = Customer.cid ;
    LEFT OUTER JOIN appdata!woitem ;
   ON  Workorder.cwoitemid = Woitem.cid ;
    where WorkOrder.cId in (select distinct WorkOrder.cId FROM ;
     appdata!workorder ;
    LEFT OUTER JOIN appdata!customer ;
   ON  Workorder.ccustomerid = Customer.cid ;
    LEFT OUTER JOIN appdata!woitem ;
   ON  Workorder.cwoitemid = Woitem.cid ;
   LEFT OUTER JOIN appdata!LineItem ;
   ON  Workorder.cId = LineItem.cWorkOrderId ;
 WHERE Workorder.cnumber LIKE ( ?vp_cNumber );
   AND  Upper(Workorder.cdesc) LIKE Upper(( ?vp_cDesc )) ;
   AND  Upper(Workorder.cJobNumber) LIKE Upper(( ?vp_cJobNumber )) ;
   AND  Upper(Workorder.cInvNumber) LIKE Upper(( ?vp_cInvNumber )) ;
   AND  Upper(Workorder.cLease) LIKE Upper(( ?vp_cLease )) ;
   AND  Workorder.clstunitid LIKE ( ?vp_cLstUnitId ) ;
   AND  Workorder.clstjobtypeid LIKE ( ?vp_cLstJobTypeId )  ;
   AND  Iif(?vp_cCustomerId == "%", .t., Customer.cId = ( ?vp_cCustomerId )) ;
   AND  Iif(?vp_cwoLineItemId == "%", .t., LineItem.cwoItemId = ( ?vp_cwoLineItemId )) ) ;
 ORDER BY Workorder.cjobnumber