why is subquery inside iif effecting results?

[cfsjohn]

3 example queries:

SELECT Pl_dscs.cid;
	FROM itfwarehouse!pl_dscs;
	INNER JOIN itfwarehouse!nonbaggedsys_descs ON Pl_dscs.cnonbaggedsys_descs_id = nonbaggedsys_descs.cid;
	WHERE PL_dscs.cnonbaggedsys_descs_id = lcnbsdcid AND;
		iif(.f.,pl_dscs.ldiscontinued=.f.,.t.) AND;
		iif(.f.,.t.,.t.) INTO CURSOR lcresult

SELECT Pl_dscs.cid;
	FROM itfwarehouse!pl_dscs;
	INNER JOIN itfwarehouse!nonbaggedsys_descs ON Pl_dscs.cnonbaggedsys_descs_id = nonbaggedsys_descs.cid;
	WHERE PL_dscs.cnonbaggedsys_descs_id = lcnbsdcid AND;
		iif(.f.,pl_dscs.ldiscontinued=.f.,.t.) AND;
		iif(.f.,.f.,.t.) INTO CURSOR lcresult

SELECT Pl_dscs.cid;
	FROM itfwarehouse!pl_dscs;
	INNER JOIN itfwarehouse!nonbaggedsys_descs ON Pl_dscs.cnonbaggedsys_descs_id = nonbaggedsys_descs.cid;
	WHERE PL_dscs.cnonbaggedsys_descs_id = lcnbsdcid AND;
		iif(.f.,pl_dscs.ldiscontinued=.f.,.t.) AND;
		iif(.f.,NOT pl_dscs.cid in (select cpl_dscs_id FROM itfwarehouse!pl_dscsperfacwsite WHERE pl_dscsperfacwsite.cfacilities_id = ""),.t.) INTO CURSOR lcresult

This is all about the difference between the 2nd example and the 3rd example.

The 1st example returns one record.
The 2nd example returns 1 record.
The 3rd example does not return any records.
The only difference between the 2nd and 3rd examples is the 2nd expression in the last iif is a subquery. Because the 1st expression in that iif is false, the subquery should not get executed. Obviously it is somehow effecting the results but how/why?

 

[Chris Miller]

IIF(.f., <<something>>, .t.) will not run <<something>> ever and always return .t., so yes, I'd not expect the subquery to run at all.

If it hardcoded .F., though? I guess the actual code has something in the first iif parameter that retuns .t. or .f. based on an expression, doesn't it?

Chriss

 

[Chris Miller]

The other point is that VFP SQL does not include expressions like that:

not 'value' in select somefield from sometable

Running that as one expression in an iif could only work, if it also runs standalone, as a separate command line, but VFP will tell you that this command "contains unrecognized phrase keyword". So that's a basic problem of framing subqueries like that as IIF expressions. You think of this as if it would be handled like a conditional mactro substitution, it's not.

What I'm saying is that the expressions within an IIF are not executed in context of the surrounding code, they have to work standalone, so you could only do subqueries that are complete queries in themselves, not such value in query subqueries.

In the end I wonder why this even compiles and you're right, I wouldn't expect a difference with a hardcoded .f. as the first iif expression. If there is a result difference it would need to come from a) dependency of the result of time or - simpler - b) because data was changed between runs of the queries.

In th end I suspect the .f. isn't hardcoded - as I already said - and then in case this expression is evaluted you get no result because the code would error, maybe look into error logs.

To tackle optiopnal macro substitution is far easier by setting the macro substitution variable to the part of code you conditionally wamt or to the empty string or just the string ',t.' in another case and write the query without iif, but simply the &macrovariable which then either is the subquery or nothing or just .t. or whatever you need in case you don't want the subquery. That way the substituted code can really be any subquery code that works in that place an in context of the rest of the query. So in short this IIF construct is a bad choice, even if it works for some cases.

One step harder would be something you need conditionall per record of the main query, i.e. you need a subquery for some records and not for others. I'd put this in very short: That's a situtation to rethink the query rather than to find a technical solution for that demand. You'll not be able to make it, as macrosubstitution always is a step before execution, the steps of macro substitution always are a) substitute, b) compile the resulting "string"=code and then c) execute it. There's no repeated substitution of macro strings and iif is no way to get there, either.

Chriss

 

[Mike Lewis]

I'm afraid your IIF() syntax doesn't make much sense.

[tt]iif(.f.,pl_dscs.ldiscontinued=.f.,.t.)[/tt] will always return .t. Similarly, [tt]iif(.f.,.t.,.t.)[/tt] will always return .T. In fact, any IIF() where the first parameter is .F. will always return the value of the second parameter.

You might want to take five minutes to study the Help page for IIF(). And if that doesn't help, spend another five minutes experimenting in the command window.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[cfsjohn]

Chris and Mike, Thanks. The 1st parameter of both iif's are really view parameters. I had set them to false just to simplify my examples. Here is the real view:

SELECT Pl_dscs.cid;
	FROM itfwarehouse!pl_dscs;
	INNER JOIN itfwarehouse!nonbaggedsys_descs ON Pl_dscs.cnonbaggedsys_descs_id = nonbaggedsys_descs.cid;
	WHERE PL_dscs.cnonbaggedsys_descs_id = ?vp_cnonbaggedsys_descs_id AND;
		iif(?vp_lexcludediscontinued,pl_dscs.ldiscontinued=.f.,.t.) AND;
		iif(?vp_lexcluderemoved,NOT pl_dscs.cid in (select cpl_dscs_id FROM itfwarehouse!pl_dscsperfacwsite WHERE pl_dscsperfacwsite.cfacilities_id = ?vp_cfacilities_id),.t.) INTO CURSOR lcresult

When vp_lexcludediscontinued=.f. and vp_lexcluderemoved=.f. and the 2nd parameter of the 2nd iif is just .f., I get 1 record (the correct results). But merely putting the subquery in the 2nd parameter of the 2nd iif, gets me no records (incorrect results). So the question still remains, if vp_lexcluderemoved=.f. why is the subquery being run? With regard to the validity of the subquery, I have never done a subquery inside an iif in a where clause but it does seem everything here is logical. I do have subqueryies like I am doing here i.e. where not cid in select... in other views and they work just fine.

Thanks, John

 

[Chris Miller]

Okay, so this viewparameter will not change per record that the query processes, it's either .t. or .f.. It would be better to swtich between two views.

Iif you set vp_lexcluderemoved=.t. I doubt this can work.

Chriss

 

[cfsjohn]

Chris,

You are correct that setting vp_lexcluderemoved=.t. does not work either. As long as the subquery is in the iif, it does not work irrespective anything else. If you take the subquery out and replace the subquery with .t. or .f. everything works as it should.

With regard to using 2 views, I am trying to use the same child form for 2 different "parents". When running the child form for Parent1, vp_lexcluderemoved is always false because the 2nd iif is used to exclude something that is not pertinet to Parent1. vp_lexcluderemoved is always true when using the child form for Parent2 because it is needed/pertinent.

I use the Visual FoxExpress framework for development. It would be really difficult to get all the presentation object stuff to work off multiple views (never done that). It would be easier to just have 2 separate child forms.

All that aside, I still do not uderstand why having the subquery inside the iif does not work. I can't come up with any examples where anyone has done this. I've googled it do death. If I can't find a solution pretty quick I will just have to do 2 separate child forms.

Thanks,
John

 

[Chris Miller]

I still do not uderstand why having the subquery inside the iif does not work

I already told, why.

While this works:

SELECT ... WHERE ....  AND NOT pl_dscs.cid in (select cpl_dscs_id FROM itfwarehouse!pl_dscsperfacwsite WHERE pl_dscsperfacwsite.cfacilities_id = ?vp_cfacilities_id)

This is not what executes when using IIF.

When you execute an IIF the single exressions are evaluated for themselves, not in the context of the rest of the query. And see, I topldf already, that this expression in itself errors with

command contains unrecognized phrase keyword

It may be hard to get, but this idea is not working out. It's not worth trying to force such a construct to work. I can't help much with the FoxExpress framework, but if you know 2 separate childforms can solve this, then this is your way out, even when it means maintaining two very similar forms, which means double work when it comes to changes. But then how often does this change.

Your only other way out is finding another solution to formulate the query. I can't imagine it is that difficult to use one of two queries, You can add both views with "nodataonload" into the DE and finally only requery one of them.



Chriss

 

[cfsjohn]

Chris,
You are correct on 2 fronts. I really can't understand why "this idea is not working out" but that will have to wait for another day so don't be shocked to see me ask this again in a few years

With regard to 2 child forms, you are showing wisdom. Yes, that is what I should have done 2 weeks ago and am going to do now. Sometimes you can't see the forest for the trees.

Just FYI: This is my "warehouse" part of my app. I use it to maintain menus for each customer site. I am adding the ability to maintain sub menus for each menu. Everything that was at the top/master became a child and I inserted a new master level into my file structure. I was trying to use the ONE child form to show the children of the master and of the children of the master. I simply went down the wrong path there, but now, thanks to your wisdom, I will suck it up and do what I should have done from the start.

Thanks and I hope you have a wonderful day,
John

 

[Chris Miller]

I understand, in the moment I can only appeal to go the longer path you know leads to your goal, even though the shortcut seems so reachable.

I see the IIF embedding into a query works in more cases than I thought possible when trying to construct simpler non-working example. I just see your example proves IIF or also ICASE won't work the same as T-SQLs feature of CASE embedding into queries. IIF and ICASE are just VFP functions you use within the query, they are not part of the SQL dialect itself. Just like BETWEEN'(a,b,c) is a functoin you can use anywhere, also in SQL, whereas the SQL way to express this embedded is WHERE a BETWEEN b AND c. Or ISNULL(field) should be expressed as field I SNULL in queries or LIKE(x,pattern) should be expressed as x LIKE pattern, but there is nothing in VFP sql dialect that embeds an IIF into sql as T-SQLs CASE does.


Chriss