This content is now out of date.

Visit Our Community

Calculated fields across subqueries

Dear All,

Similar to my previous enquiry, but even more fundamental:

I have a subquery structure as follows:

Master Query

ID Number
Amount Modifier

ID Number

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?