I need some help to write an SQL query please. I have a problem in my application where using calculated fields works fine when working with database fields or constants, and is also fine when a calculated field is used within another calculated field. It break when there are nested calculated fields beyond 1 level deep. I wish to construct a SQL query to identify to me any records which this affects.
Here is a sample of some rows where the problem exists:
TableID FieldName Seq DataType IsCalculated Formula FieldFormat FieldLabel Alias
Calculated HrlyCost 8 decimal 1 50
Calculated VarOnWag 14 decimal 1 (ProcLabCost + PackLabCost) * 999 ->>,>>9.99 Variable on Wages Calculated_VarOnWag
Calculated FixOnBas 18 decimal 1 BaseCost * 999 ->>,>>9.99 Fixed on Base Calculated_FixOnBas
Calculated FullCost 19 decimal 1 WageCost + VarOnWag + BaseCost + FixOnBas ->>,>>9.99 Fully Costed Calculated_FullCost
Calculated PackLabCost 12 decimal 1 (HrlyCost * Part_UD.Number07) / NULLIF((Part_UD.Number06 * 999), 0) ->>,>>9.99 Packing Labour Cost Calculated_PackLabCost
Calculated BaseCost 17 decimal 1 CostPart.StdMaterialCost * 150 / NULLIF(100, 0) ->>,>>9.99 Base Cost incl Waste Calculated_BaseCost
Calculated ProcLabCost 11 decimal 1 Part1_UD.Number05 * Part.NetWeight ->>,>>9.99 Process Labour Cost Calculated_ProcLabCost
Calculated WageCost 13 decimal 1 ProcLabCost + PackLabCost ->>,>>9.99 Wages Cost Calculated_WageCost
Calculated PPK 22 decimal 1 FullCost / NULLIF(Part.NetWeight, 0) ->>,>>9.99 Cost PPK Calculated_PPK
I'll also try to demonstrate the nested fields here:
The field PPK as you can see above has 4 levels.
Is there anyway to query for and show the number of levels based on the data provided above?