asked on SQL Query Help - find Field Name within Text - Iterative loop
Hi
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:
I'll also try to demonstrate the nested fields here:
![Screenshot]()
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?
Many Thanks
Mark.
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?
Many Thanks
Mark.
DatabasesMicrosoft SQL ServerSQL
Last Comment
Mark Damen
The string "PPK" is referenced 3 times, all in the same row of data, so I fail to see what you state I should.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
What third party database tools are installed?
markusdamenous, do you still need help with this question?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
See my last comment for explanation of points allocation.
Open in new window