Hi nadya,

Any reason you don't want to use a flexible where clause (e.g. WHERE &?cWhere ). From a performance point of view it might be way better (LIKE() is not rushmore optimizable in VFP).

Walter,

>Hi everybody,
>
>I have a view which I am trying to use in the Search form. Here is a view definition:
>
>

>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 OUTER 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 ( ( ( ( ( (  Trans.cclient_account_number LIKE ( ?vp_cClient_Account_Number );
>   AND  Trans_status_codes.iactive_flag = ( 1 ) );
>   AND  Names.cl_name LIKE ( ?vp_cLast ) );
>   AND  Names.cf_name LIKE ( ?vp_cFirstName ) );
>   AND  Names.cm_initial LIKE ( ?vp_cMiddle ) );
>   AND  Patients.cssn LIKE ( ?vp_cSSN ) );
>   AND  (  Phones.carea_code LIKE ( ?vp_cAreaCode );
>   OR  Phones.carea_code IS NULL ) );
>   AND  (  ( Phones.cexchange+Phones.clast_four ) LIKE ( ?vp_cExchange_Four );
>   OR  ( Phones.cexchange+Phones.clast_four ) IS NULL );
> GROUP BY Trans.cclient_account_number

>
>(BTW, why View designer puts "(" this strange way and how can I fix it?)
>
>I want to be able to search by any of the field. The patient may not have Phones, hence the LEFT JOIN and IS NULL clause. However, if I'm searching by Phone, this view returns empty records as well, which I do not need. I can modify my OR condition to OR (vp_cAreaCode="%" and Phones.cArea_Code IS NULL), but I do not know, if this is a "kosher" approach? Anyone has a better advice for me?
>
>Thanks a lot in advance.