where clause containing view parameter and iif

[cfsjohn]

I have a pretty complex VFP query with which I am having an issue.

SELECT Debit_pymts.cid,;
		Products.cproduct_groups_id,;
		Bill_codes.cbill_code_cats_id,;
		VL_JD_VNDRACCTPERPRODGRPBILLCDCAT.CPRODGRPBILLCDCATCOMBOS_ID,;
		sum(Debit_pymts.yamtpaid) AS yamtpaid,;
		Products.cproduct_name,;
		Inmates.cinmate_number,;
		Inmates.cso,;
		PADR(ALLTRIM(Inmates.clast)+","+" "+ALLTRIM(Inmates.cfirst)+" "+ALLTRIM(Inmates.cmiddle),50) AS cname,;
		Purchases.mnotes,;
		"C" as ctype,;
		Debit_pymts.cpurchases_id,;
		Purchases.cinmates_id;
	FROM inmatetrustfund!debit_pymts;
		INNER JOIN inmatetrustfund!account_debits;
			ON Debit_pymts.caccount_debits_id = Account_debits.cid;
		INNER JOIN inmatetrustfund!purchases;
			ON Debit_pymts.cpurchases_id = Purchases.cid;
		INNER JOIN inmatetrustfund!bill_codes;
			ON Purchases.cbill_codes_id = Bill_codes.cid;
		INNER JOIN inmatetrustfund!products;
			ON Purchases.cproducts_id = Products.cid;
		INNER JOIN v_inmatetrustfund!VL_JD_VNDRACCTPERPRODGRPBILLCDCAT;
			ON Products.cproduct_groups_id =;
				VL_JD_VNDRACCTPERPRODGRPBILLCDCAT.cproduct_groups_id AND;
				Bill_codes.cbill_code_cats_id =;
				VL_JD_VNDRACCTPERPRODGRPBILLCDCAT.cbill_code_cats_id;
		INNER JOIN inmatetrustfund!inmates;
			ON Purchases.cinmates_id = Inmates.cid;
	WHERE (EMPTY(Debit_pymts.cjdi_lineitems_id) OR;
		ISNULL(Debit_pymts.cjdi_lineitems_id)) AND;
		VL_JD_VNDRACCTPERPRODGRPBILLCDCAT.cvendor_accts_id =;
		?VP_cvendor_accts_id AND;
		TTOD(debit_pymts.ttxndatetm) <= ?VP_dcollectthru AND;
		iif(?vp_specialvafilter,;
		debit_pymts.cpurchases_id in (select;
		vl_jdinv_PursToIncl.cdp_purchases_id;
	FROM v_inmatetrustfund!vl_jdinv_PursToIncl) OR;
		purchases.cinmates_id in (select vl_jdinv_InmsToIncl.cinmates_id;
	FROM v_inmatetrustfund!vl_jdinv_InmsToIncl),.t.);
	GROUP BY Debit_pymts.cpurchases_id;
	ORDER BY Products.cproduct_name, Inmates.cinmate_number

The where clause is the issue. I recently added the view parameter vp_specialvafilter. It is a logical value (True/False).
At the same time I added to the where clause:
AND iif(?vp_specialvafilter,debit_pymts.cpurchases_id in (select vl_jdinv_PursToIncl.cdp_purchases_id FROM v_inmatetrustfund!vl_jdinv_PursToIncl) OR purchases.cinmates_id in (select vl_jdinv_InmsToIncl.cinmates_id FROM v_inmatetrustfund!vl_jdinv_InmsToIncl),.t.)

The issue:
Before any modifications I was getting many records.
Now, and with vp_specialvafilter set to false, I am getting 0 records.

If vp_specialvpfilter is false, I should get the records that I got before these modifications because the IIF would always return .t.
If vp_specialvpfilter is true, it would filter out some of the records.

If I change the iif portion to iif(?vp_specialvafilter,.t.,.t.) I get many records.

All that tells me that even though the 1st expression in my iif is not getting evaluated, it is somehow keeping me from getting any records.

Any help appreciated,
John

 

[Chris Miller]

I doubt [pre]value in (sql-query)[/pre] is a valid expression. The expressions in IIF are computed separatelly, not as if they become part of the overall query.
I am also not sure whether you want to use the OR after the first clause, because it won't matter whether any other condition is true or false.

Chriss

 

[cfsjohn]

Hi Chris and Thank You.

I logged back in to add to my post. I should have informed that I am using VFP9 and SET ENGINEBEHAVIOR 70

Now, as for your reply, I can not say that I have ever done a "value in (sql-query)" in a where clause but looking at VFP help it appears it is legitimate to do so. Do you know for sure that it is not valid? This is a big project and I have implemented this same view parameter and where clause in about 20 views after about 2 months work so I really need to know that it will not work before giving up.

Just to test multiple things, I tried changing the iif to be:
iif(?vp_specialvafilter,debit_pymts.cpurchases_id in (select vl_jdinv_PursToIncl.cdp_purchases_id FROM v_inmatetrustfund!vl_jdinv_PursToIncl),.t.)
Be aware I am still passing False as the value for vp_specialvafilter so it really shouldn't matter what I do in expression 2 of the iif.
Anyways, that simply took the OR part out. I get the same bad results, 0 records.

I tried:
iif(?vp_specialvafilter,debit_pymts.cpurchases_id in (select cid FROM inmatetrustfund!syscodes),.t.)
syscodes is just a table that has nothing to do with this issue but just so you know, would never return any cid's matching debit_pymts.cpurchases_id, so my test was to just try doing the select on a table rather than an underlying view.
I got records but the grouping/sum did not work correctly. I could not begin to say why it would have had ANY effect and especially why it would have returned any records because again, I am passing false for vp_specialvafilter so it really shouldn't matter what I do in expression 2 of the iif.

Thanks for any input,
John

 

[Chris Miller]

value in (query) is a valid WHERE clause, but it doesn't work standalone.

Again, IIF is not embedding your code in the overall query. The expressions in the IIF function have to work on their own, out of the context of the query. So this kind of IIF usage won't work.

Chriss

 

[cfsjohn]

Chris,
If I understand you correctly, this boils down to the fact that my expressions are not returning a true/false value.
You are correct. I was unaware of that, but they are not.

Trying to think logically (and sometimes that is a challenge), I assumed that "field1 in (select fieldx from tablea)" would return a true/false value. Of course, it does not.

So, I am in the command window. I am trying to find syntax where "field1 in (select fieldx from tablea)" WOULD return a true/false value.
I have tried:
?select fieldx from tablea where fieldx=field1
Nope.
?exists (select fieldx from tablea where fieldx=field1)
Nope.
and a few other things with _TALLY, etc but none of them worked.

So, I believe the question becomes, is it possible to return a true/false value from a select statement?
If yes, any suggestions?
if no, any suggestions?

Thanks again,
John

 

[cfsjohn]

Chris,
I did some more "try this" and "try that". I believe I have it working albeit I will be the first to say it looks squirelly and it will need a lot of testing to ensure it always works properly.

(iif(?vp_specialvafilter,.t.,.f.) AND;
(debit_pymts.cpurchases_id in (select vl_jdinv_PursToIncl.cdp_purchases_id FROM v_inmatetrustfund!vl_jdinv_PursToIncl) OR;
purchases.cinmates_id in (select vl_jdinv_InmsToIncl.cinmates_id FROM v_inmatetrustfund!vl_jdinv_InmsToIncl)) OR;
NOT ?vp_specialvafilter);

Short answer, I guess what I have done is taken the expression out of the iif statement.
Pseudocode: If vp is true and the table's value is in either lookup table or if the vp is false, include the table's record.

If you see something wrong, please don't hesitate to say but otherwise this is solved unless I run into problems testing in which I will no doubt be back for assistance.

Thanks again,
John

 

[TamarGranor]

Seems to me at this point, you can get rid of the IIF() entirely. It's not adding anything, I think.

Tamar

 

[cfsjohn]

Well, I am certainly not going to argue with someone I consider one of the most prolific VFP programmers ever. We have never corresponded but I have known of you and your many "white papers" for 30 years. I have read and used many of them. I stay so busy I never really communicated much with the "foxpro alumni". Years ago I was in daily contact with Charles Hankey and had some contact with the Feltman's as I did and still use the VFE framework.

The iif vp combination is supposed to cause all the records to be included unless the vp is set true in which case only the records that have a value in one of the 2 lookups are included. If you don't think that is what I will get, please do let me know.

Thanks,
John

 

[Chris Miller]

Tamars observation is quite simple: An IIF (var,.t.,.f.) is the same as var alone, becasue it returns .T. when var is .T. and .F. when var is .F.

So the expression...

?vp_specialfilter AND (condition)

...will be the same as (condition) when vp_specialfilter is .T., but on the other side will be .F., when vp_specialfilter is .F., becasue .F. AND anything is .F.

What you want is an optional where-clause and macro substitution is better for that.

Chriss

 

[mjcmkrsr]

hi

I had a similar problem with IIF in a SQL statement and I found a work around by using variables for user input. You have of course to check that the values of the variables are correct and replace the ?xxxx in your code

LOCAL lcVP_cvendor_accts_id as C, ldVP_dcollectthru as D (I guess), llVP_specialvafilter as L
*!* Please choose the appropriate variable types

lcVP_cvendor_accts_id = yourvalue
ldVP_dcollectthru = yourvalue
llVP_specialvafilter = .F.

SELECT Debit_pymts.cid,;
		Products.cproduct_groups_id,;
		Bill_codes.cbill_code_cats_id,;
		VL_JD_VNDRACCTPERPRODGRPBILLCDCAT.CPRODGRPBILLCDCATCOMBOS_ID,;
		sum(Debit_pymts.yamtpaid) AS yamtpaid,;
		Products.cproduct_name,;
		Inmates.cinmate_number,;
		Inmates.cso,;
		PADR(ALLTRIM(Inmates.clast)+","+" "+ALLTRIM(Inmates.cfirst)+" "+ALLTRIM(Inmates.cmiddle),50) AS cname,;
		Purchases.mnotes,;
		"C" as ctype,;
		Debit_pymts.cpurchases_id,;
		Purchases.cinmates_id;
	FROM inmatetrustfund!debit_pymts;
		INNER JOIN inmatetrustfund!account_debits;
			ON Debit_pymts.caccount_debits_id = Account_debits.cid;
		INNER JOIN inmatetrustfund!purchases;
			ON Debit_pymts.cpurchases_id = Purchases.cid;
		INNER JOIN inmatetrustfund!bill_codes;
			ON Purchases.cbill_codes_id = Bill_codes.cid;
		INNER JOIN inmatetrustfund!products;
			ON Purchases.cproducts_id = Products.cid;
		INNER JOIN [highlight #FCE94F]v_inmatetrustfund[/highlight]!VL_JD_VNDRACCTPERPRODGRPBILLCDCAT;
			ON Products.cproduct_groups_id =;
				VL_JD_VNDRACCTPERPRODGRPBILLCDCAT.cproduct_groups_id AND;
				Bill_codes.cbill_code_cats_id =;
				VL_JD_VNDRACCTPERPRODGRPBILLCDCAT.cbill_code_cats_id;
		INNER JOIN inmatetrustfund!inmates;
			ON Purchases.cinmates_id = Inmates.cid;
	WHERE (EMPTY(Debit_pymts.cjdi_lineitems_id) OR;
		ISNULL(Debit_pymts.cjdi_lineitems_id)) AND;
		VL_JD_VNDRACCTPERPRODGRPBILLCDCAT.cvendor_accts_id =;
		[highlight #73D216]lcVP_cvendor_accts_id[/highlight] AND;
		TTOD(debit_pymts.ttxndatetm) <= [highlight #8AE234]ldVP_dcollectthru[/highlight] AND;
		iif([highlight #8AE234]llVP_specialvafilter[/highlight],;
		debit_pymts.cpurchases_id in (select;
		vl_jdinv_PursToIncl.cdp_purchases_id;
	FROM v_inmatetrustfund!vl_jdinv_PursToIncl) OR;
		purchases.cinmates_id in (select vl_jdinv_InmsToIncl.cinmates_id;
	FROM v_inmatetrustfund!vl_jdinv_InmsToIncl),.t.);
	GROUP BY Debit_pymts.cpurchases_id;
	ORDER BY Products.cproduct_name, Inmates.cinmate_number

In addition you have to group by all non aggregated fields - otherwise the SQL engine throws an error

...
Group by 1,2,3,5,6,7,8,9,11,12,13
...

Furthermore you may want to test the SQL statement without the where clause and then gradually add filter conditions

Btw is v_inmatetrustfund a second database or is it a typo?

hth

MarK