Avatar of Mark Damen
Mark Damen
Flag for United Kingdom of Great Britain and Northern Ireland 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:

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.
DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Mark Damen

8/22/2022 - Mon
aikimark

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

PortletPaul

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
David Todd

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
aikimark

What third party database tools are installed?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Vitor Montalvão

markusdamenous, do you still need help with this question?
ASKER CERTIFIED SOLUTION
Mark Damen

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark Damen

ASKER
See my last comment for explanation of points allocation.