Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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:
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This lines up the column data better:
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
The string "PPK" is referenced 3 times, all in the same row of data, so I fail to see what you state I should.
Hi,
SQL is smart, but its not that smart, so it is unreasonable to assume that the nesting is infinite like you are doing.
What you could do, is use a subquery where PPK and the next level are calculated, making them constants or fields in the next higher level query.
HTH
David
PS Often calculating things like the above are best left to the presentation layer ie SSRS reports or Crystal reports and so forth and so on, which may be more forgiving, whereas SQL is the data retrieval layer ...
SQL is smart, but its not that smart, so it is unreasonable to assume that the nesting is infinite like you are doing.
What you could do, is use a subquery where PPK and the next level are calculated, making them constants or fields in the next higher level query.
HTH
David
PS Often calculating things like the above are best left to the presentation layer ie SSRS reports or Crystal reports and so forth and so on, which may be more forgiving, whereas SQL is the data retrieval layer ...
I didn't get a solution to this question here, but will award points to the answers that suggested alternatives.
The application provided has acknowledged the error that caused me to need to find the nested queries in the first instance, and I manually went though about 400 calculated fields and changed the formula.
The application provided has acknowledged the error that caused me to need to find the nested queries in the first instance, and I manually went though about 400 calculated fields and changed the formula.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial