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:

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

Open in new window


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.
LVL 13
Mark DamenERP System ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

aikimarkCommented:
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

Open in new window

PortletPaulEE Topic AdvisorCommented:
The string "PPK" is referenced 3 times, all in the same row of data, so I fail to see what you state I should.
David ToddSenior Database AdministratorCommented:
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 ...
SolarWinds® Network Configuration Manager (NCM)

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.

aikimarkCommented:
What third party database tools are installed?
Vitor MontalvãoMSSQL Senior EngineerCommented:
markusdamenous, do you still need help with this question?
Mark DamenERP System ManagerAuthor Commented:
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.

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 trial
Mark DamenERP System ManagerAuthor Commented:
See my last comment for explanation of points allocation.
It'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.