How to stop field from calculating beyond the query it's in (1 Viewer)

smbrr

Registered User.
Local time
Today, 04:05
Joined
Jun 12, 2014
Messages
61
I have an issue with a calculated field that continues calculating even outside of its scope.

Assume 2 queries:
SubQuery
FinalQuery

In SubQuery, I have fields including:
Code:
[Type]
[T1 Res]
[T2 Res]

I have a field that goes:
Code:
Result: iif([Type]=1,[T1 Res],iif([Type]=2,[T2 Res],"default result"))

The point is obviously to have in 1 field the result I want according to what type the record is.
It works perfectly when running SubQuery:

Code:
record   | T1 Res | T2 Res | Type | Result
---------+--------+--------+------+--------
record 1 | abc    | zok    | 1    | abc
record 2 | vyb    |        |      | default result
record 4 |        | mpi    | 1    | 
record 6 |        | jaj    | 2    | jaj
record 7 |        |        | 1    |

Then in FinalQuery, I join SubQuery with a table, and when I run it, I get stuff like this:

Code:
tbl rec  | sbqr rec | T1 Res | T2 Res | Type | Result
---------+----------+--------+--------+------+-------------
record 1 | record 1 | abc    | zok    | 1    | abc
record 2 | record 2 | vyb    |        |      | default result
record 3 |          |        |        |      | default result !!!!
record 4 | record 4 |        | mpi    | 1    | 
record 5 |          |        |        |      | default result !!!!
record 6 | record 6 |        | jaj    | 2    | jaj
record 7 | record 7 |        |        | 1    |

As you can see, the JOIN somehow brought "default result" on row 3 and 5 even though these rows don't exist in my SubQuery!
So apparently the calculated field still calculates the result based on the IIF functions in the final query, and finds no Type so the result is "default result".

But that makes no sense. If I wanted this calculation to be made in the final query, I would have put it in the final query. I want the records that weren't in the SubQuery to remain null in my FinalQuery.

I tried doing a "result 2: [result]" in hopes that it would convert it in raw values, but result 2 was also being calculated in the FinalQuery. I tried a SWITCH function instead of IIF but same result. The only thing that seems to work is to put an ISNULL function on top in my field:
Code:
Result: iif(isnull([SubQuery].[Record]),"",iif([Type]=1,[T1 Res],iif([Type]=2,[T2 Res],"default result")))
But that's just weird and feels wrong because it's just checking its own existence and it's never going to be null in the scope of the SubQuery.

Why does it do this? How do I fix it?
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 11:05
Joined
Feb 19, 2013
Messages
16,553
what does your query actually look like? You think it is the join causing the problem, you are probably right but we can only speculate on the issue
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:05
Joined
Sep 12, 2006
Messages
15,614
if you join 2 queries, depending on the joined fields, you can get a cartesian (cross-product) join.

Instead of an item in one query being correctly joined to particular items in the second query, it gets joined to more items than it should be joined to, and you get phantom rows appearing.

At its worse, for example, you have 30 items in one table, 10 in another, and you get 300 in the cross-product result.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 28, 2001
Messages
27,001
Your problem is "order of execution" of SQL. This link MIGHT give you problems because its certificate may have expired, but it explains things pretty well and my anti-virus didn't barf on the contents. You can research this by searching "SQL order of execution" if you don't want to trust the linked site.

https://sqlbolt.com/lesson/select_queries_order_of_execution

I'll try to 'splain it in case you don't follow the link.

In order to execute correctly, SQL has to first evaluate how many records it has to process, so it processes the JOIN and FROM clauses in order to see ALL records that it could possibly have to process. Then it does WHERE clauses to remove any records that can be excluded due to the WHERE constraints. This gives an "initial scope" of records.

After that, SQL will have to deal with GROUP BY and HAVING clauses, which alter the order of presentation when you are using SQL Aggregate functions. The HAVING clauses are secondary filters like WHERE clauses. Due to the potential for HAVING clauses, this would lead to a "secondary scope" of potentially fewer records.

THEN you get to the SELECT clause and any formulas that are to be evaluated. If, as you said, you did a JOIN of your sub-query and main query, then depending on exactly how you did the JOIN, that JOIN might have preceded the SELECT in terms of execution. Therefore, as noted in your example, records 3 and 5 were first created by the JOIN and FROM action and THEN your IIF was executed. (Since you didn't show us the actual SQL, we can't tell just how the JOIN was implemented.)

What happens next is any ORDER BY statements, and there are various limiting statements like TOP n that could further reduce the number of records presented.
 

smbrr

Registered User.
Local time
Today, 04:05
Joined
Jun 12, 2014
Messages
61
The_Doc_Man, thank you for this in depth explanation. Now I understand. As you said, my calculated field must be processed last. I am surprised by this because I don't remember having seen this before, but it must have happened every time I did joins and calculated fields together.

here's my query:
the calculated field is "comp"
everything else is normal fields

Code:
SELECT [Retail Info].Centrale, [Retail Info].Client, [Retail Info].Marque, [Retail Info].[Période Mensuelle], [compMois Fin].comp
FROM [Retail Info] LEFT JOIN [compMois Fin] ON ([Retail Info].Marque = [compMois Fin].Marque) AND ([Retail Info].Client = [compMois Fin].Client) AND ([Retail Info].Centrale = [compMois Fin].Centrale)
GROUP BY [Retail Info].Centrale, [Retail Info].Client, [Retail Info].Marque, [Retail Info].[Période Mensuelle], [compMois Fin].comp
HAVING ((([Retail Info].Centrale)=[ctrl]) AND (([Retail Info].[Période Mensuelle])=[per]));

I will try turning this query around, remaking it in a different order or something to see if I find a setup where the SELECT isn't processed last. Otherwise I might just remove the concept of a default value..

Anyways thank you all for the help
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 28, 2001
Messages
27,001
Hmmm - what you posted SHOULD have worked correctly because it appears you correctly layered it. Before, I didn't know how you had done the JOIN - but this doesn't look wrong.

By "layering" it, I mean that the formula with the IIF in it is treated as a recordsource by appearing in a FROM/JOIN clause rather than by manually joining the SQL elements of the underlying queries. That is, there is more than one way to join queries and you picked the right way.

What should happen here is that first, you evaluate the FROM components - but if one of them is a query, that first level of FROM/JOIN evaluation must pause in order to evaluate whichever one of those is a separate query. But once THAT query is processed, you SHOULD have something that resembles your first (5-record) result. That becomes a temporary table until the rest of the query is evaluated.

So now I'm with you. I don't know why it happened. It appears that your IIF actually is being processed last when in fact it shouldn't even be there in the outer query because processing SHOULD be completed in the inner query before getting back to that outer query. The two outer-query records you highlighted are correct if and ONLY if there is some reason for them to defer evaluation, and I don't see that.

sorry I led you down a garden path earlier, but I wasn't sure of exactly what you had done until you showed the SQL.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:05
Joined
Feb 19, 2013
Messages
16,553
based on this formula you provided

Result: iif(isnull([SubQuery].[Record]),"",iif([Type]=1,[T1 Res],iif([Type]=2,[T2 Res],"default result")))

If you are getting default result then subquery.record value is not null. Perhaps we need to see the sql to your subquery - compmois fin?

don't know whether you are providing air code or the actual code - but if the latter, Type is a reserved word and can cause unexpected errors when used as a name
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 28, 2001
Messages
27,001
Ah, missed that one, CJ. It was buried in a complex query. Good catch.
 

smbrr

Registered User.
Local time
Today, 04:05
Joined
Jun 12, 2014
Messages
61
It was just pseudo code.
And no, using the ISNULL function does remove my issue. But it doesn't feel like a fix, technically as Doc explained, my initial query should be evaluated and "stored" in a temp table. So evaluating ISNULL on itself should be a useless step. However it does work, proving that indeed my calculated field evaluates not in the subquery but the finalquery.
It just feels like the result is out of my control and I can't trust it.

[Comp Fin] is this:
Code:
SELECT [compMois 2].Marque, [compMois 2].Client, [compMois 2].Centrale, [compMois 2].[Période Mensuelle], Comp_TypeDeComp.TypeDeComp, IIf([TypeDeComp]="stock",[comp stock],IIf([TypeDeComp]="module",[comp moduleclient],[comp defaut])) AS comp
FROM [compMois 2] LEFT JOIN Comp_TypeDeComp ON [compMois 2].Marque = Comp_TypeDeComp.Marque;

[compMois 2] is a pretty simple query that counts and sums stuff, and [Comp_TypeDeComp] is a reference table for which type records should use.

I think I will take the time to rebuild all of it from scratch and if it still insist on evaluating at the end of the subquery trail, I will force a maketable temp as intermediary step...
 

Users who are viewing this thread

Top Bottom