Invalid column name `Calculated_...` on working BAQ when I try to edit calculated field

,

I need to make a slight change in a calculated formula in a BAQ to fix a bug I found in the calculation.

However, when I made the change and checked the syntax I got the error:

Invalid column name 'Calculated_<varname>'.

Where is the name of the variable as entered in the Field Name field of the Calculated Field SQL Editor for the BAQ.

I didn’t save and instead exited out and tried loading the BAQ again and entering the Calculated Field editor again, and this time tried checking the syntax before doing anything. And I get the same error. But the BAQ is working, and in fact is being used by a dashboard and runs just fine.

After loading the BAQ fresh I went to the Analyze tab and clicked “Analyze”. Some moments later it gave the message “Syntax is OK”. Then I went to the sub-query having the issue and opened the calculated field editor. As soon as it opened I clicked “Check Syntax”. Now it pops up with the invalid column name error again!!! So I closed the calculated field editor without making any changes and go to the analyze tab and click “Analyze” again. Now it says there is the same invalid column name!

If I clear the BAQ and reload, then everything is working. But I need to fix this calculated field, and every time I try, this error comes back before I can even start editing it!

When I look at the query string, I don’t see any difference around the definition or use of this field.

What are the names of your calculated fields? They aren’t any reserved words are they ? (Like Count, Sum, etc)

The field name it doesn’t like is “Calculated_MakeToStock”. (Although, it works fine until I try to enter the calculated field editor.)

Sometimes that error is a red herring though. Do you have any other calculated fields?

There are a couple others in this CTE and a few in other CTEs, but nothing with any reserved names, they are all long names like Calculated_TotalTranQty.

Well, that’s all I got. Without the query to look at, I’m not sure what else to look for.

Thanks! I’m going to experiment with a copy of the BAQ.

Well I seem to have found a workaround. If I start at the top-level query (which is at the end) and work backwards into each previous CTE deleting the field in that order, then I am able to recreate the calculated expression and then re-add it in subsequent queries.