Calculated fields across subqueries
14 March, 2016
Dear All,
Similar to my previous enquiry, but even more fundamental:
I have a subquery structure as follows:
Master Query
Date
ID Number
Category
Amount Modifier
Subquery
Date
ID Number
Amount
I have created a subquery that operates under the following logic:
CASE WHEN Category = 7 THEN Amount * Amount Modifier
However, the column returns all blanks. This is using either an inner join or an outer join.
Is there no way to write a case statement where it's WHEN condition in Master Query variable THEN operation on Subquery variable?
Similar to my previous enquiry, but even more fundamental:
I have a subquery structure as follows:
Master Query
Date
ID Number
Category
Amount Modifier
Subquery
Date
ID Number
Amount
I have created a subquery that operates under the following logic:
CASE WHEN Category = 7 THEN Amount * Amount Modifier
However, the column returns all blanks. This is using either an inner join or an outer join.
Is there no way to write a case statement where it's WHEN condition in Master Query variable THEN operation on Subquery variable?
Hi David,
no, there isn't a way to spread a calculated field definition across a master and sub query. However, there might be other ways around this problem although it's hard for us to work them out without knowing the full context. I'm assuming that because of the way the sub-query is linked to the master query, the Category column never equals 7. Are you able to upload enough screenshots from the report builder so that we can fully understand your report?
regards,
Dave
no, there isn't a way to spread a calculated field definition across a master and sub query. However, there might be other ways around this problem although it's hard for us to work them out without knowing the full context. I'm assuming that because of the way the sub-query is linked to the master query, the Category column never equals 7. Are you able to upload enough screenshots from the report builder so that we can fully understand your report?
regards,
Dave