SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.
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.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
Open in new window