Hi Walter,

I want to be able to use this view as part of database and be able to modify it in VFP View Designer. With variable where condition I would not be able to do that. In such case I may better go with the CursorAdapter (our original inclination). But I spent too much energy and time already trying to make it work.

Today I tried this variation:

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

It doesn't use my index on LastName. If I remove the first part and write it
where Names.cl_Name LIKE ?vp_cLast, it uses my index. I am curious, how does VFP9 behave in this regard. I'm going to test it right now.

Could you please advise, how can I use variable Where clause and be able to save my view in the View Designer? It also should work the same in VFP8 and in VFP9. If this is achivable, I'll switch.




>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.