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:
I have a field that goes:
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:
Then in FinalQuery, I join SubQuery with a table, and when I run it, I get stuff like this:
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:
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?
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")))
Why does it do this? How do I fix it?
Last edited: