SQLTeam.com | Weblogs | Forums

How to check the Multiple left Join SQL query or How to split it


#1

Hi,

I have checked the existing query, because i am getting the NULL value, but expected is Some values in DB table,
i need to know how to split and check the big SQL join Query or how to debug to find the root cause.

Table --> Classrecord_C

NumValue
575.000000
320.000000
240.000000
480.000000
400.000000

Table -- Price_hours_c

Base_Abs
34.98
32.18
32.18
34.98

Expected result:

xSLBaseHours
34.98
32.18
32.18
34.98

SQL Query:

SELECT SLphc.Base_Abs as xSLBaseHours
	FROM  dbo.Price_C pd with (NOLOCK)
	LEFT JOIN dbo.PriceValue_C pdv  with (NOLOCK)

	ON 	pdv.SPDGUID=pd.camosguid AND pd.AttributeIdent = 'Load' and pdv.flag_Activated = 1 AND	pdv.Deleted = 0 
	
	LEFT JOIN dbo.ClassRecord_C cr  with (NOLOCK)
	ON
		cr.PriceListGUID = 'DE3BC35104E7A64F9A168A19C80ED84A' AND
		cr.ClassName = 'ComSpace' AND
		cr.Deleted = 0 AND
		cr.Attribute = 'Load' AND
		((pd.AttributeType='ASCII' and cr.CharValue = pdv.AttributeValue) OR
			(pd.AttributeType='number' and
			cr.NumValue = 
				case isnumeric(pdv.AttributeValue) 
					when 1 then convert(decimal(18,6),replace(replace(pdv.AttributeValue,' ',''),',','.')) 
					else null 
				end
			)
		)
LEFT JOIN
		dbo.pricelist_price_c plc with (NOLOCK)
	ON 
		plc.ClassRecordGUID = cr.CGUID AND
		plc.Deleted = 0
	LEFT JOIN
		dbo.Price_Hour_C plhc with (NOLOCK)
	ON 
		plhc.ClassRecordGUID = cr.CGUID AND
		plhc.Deleted = 0
	

		LEFT JOIN
			dbo.pricelist_price_c SLplc with (NOLOCK)
		ON 
			SLplc.Deleted = 0 and
			SLplc.ClassRecordGUID in (
				SELECT SLcr.CGUID
					FROM 
						dbo.Price_C SLpd with (NOLOCK)
					LEFT JOIN
						dbo.PriceValue_C SLpdv  with (NOLOCK)
					ON
						SLpdv.SPDGUID=SLpd.Cguid AND
						SLpdv.flag_Activated = 1 AND
						SLpdv.Deleted = 0 
					LEFT JOIN
						dbo.ClassRecord_C SLcr  with (NOLOCK)
					ON
						SLcr.PriceListGUID = '762F693052B72847A8AA48A4FFF7A201' AND
						SLcr.ClassName = slpd.AttributeClass AND
						slpd.AttributeClass = 'ComSpace' AND
						SLcr.Deleted = 0 AND
						SLcr.Attribute = slpd.AttributeIdent AND
						slpd.AttributeIdent = 'Load' AND
						slpdv.AttributeValue = pdv.AttributeValue AND
						(
							(SLpd.AttributeType='ASCII' and SLcr.CharValue = SLpdv.AttributeValue) OR
							(SLpd.AttributeType='number' and
								SLcr.NumValue = 
									case isnumeric(SLpdv.AttributeValue) 
										when 1 then convert(decimal(18,6),replace(replace(SLpdv.AttributeValue,' ',''),',','.')) 
										else null 
								end
								)
					)
					WHERE
						SLpd.SPGUID='4574A2DB26C5A74B824FA84C2687C6E3' AND 
						SLpd.flag_Activated = 1 AND
						SLpd.Deleted = 0 AND
						SLpd.AttributeIdent <> '' AND
						SLPD.flag_SP = 1 AND
						(slpd.flag_TP=1 or slpd.flag_ID=1)
			)
LEFT JOIN
			dbo.Price_C sslpd with (NOLOCK)
		ON 
			pd.AttributeClass = sslpd.AttributeClass AND
			sslpd.Deleted = 0 AND
			pd.AttributeIdent = sslpd.AttributeIdent AND
			pd.AttributeType = sslpd.AttributeType AND
			sslpd.SPGUID='4574A2DB26C5A74B824FA84C2687C6E3' AND 
			sslpd.flag_Activated = 1 

		LEFT JOIN
			dbo.pricelist_hour_c SLphc with (NOLOCK)
		ON 
			SLphc.Deleted = 0 and
			SLphc.ClassRecordGUID in (
				SELECT SLhcr.camosGUID
					FROM 
						dbo.Price_C SLhpd with (NOLOCK)
					LEFT JOIN
						dbo.PriceValue_C SLhpdv  with (NOLOCK)
					ON
						SLhpdv.SPDGUID=SLhpd.Cguid AND
						SLhpdv.flag_Activated = 1 AND
						SLhpdv.Deleted = 0 
					LEFT JOIN
						dbo.ClassRecord_C SLhcr  with (NOLOCK)
					ON
						SLhcr.PriceListGUID = '762F693052B72847A8AA48A4FFF7A201' AND
						SLhcr.ClassName = slhpd.AttributeClass AND
						slhpd.AttributeClass = 'ComSpace' AND
						SLhcr.Deleted = 0 AND
						SLhcr.Attribute = slhpd.AttributeIdent AND
						slhpd.AttributeIdent = 'Load' AND
						slhpdv.AttributeValue = pdv.AttributeValue AND
						(SLhcr.CharValue = slhpdv.AttributeValue OR
						CONVERT(nvarchar,SLhcr.NumValue) = SLhpdv.AttributeValue)
					WHERE
						SLhpd.SPGUID='4574A2DB26C5A74B824FA84C2687C6E3' AND 
						SLhpd.flag_Activated = 1 AND
						SLhpd.Deleted = 0 AND
						SLhpd.AttributeIdent <> '' AND
						SLhPD.flag_SP = 1 AND
						(slhpd.flag_TP=1 or slhpd.flag_ID=1)
			
				)
	WHERE
		pd.SPGUID='B207F062320D46469DAC5FE420ABC090' AND
 
		
	(
		plc.Base_Abs <> 0 or
		plc.Service_Abs <> 0 or
		plc.Meter_Abs <> 0 or
		plc.Each_Abs <> 0 or
		plhc.Base_Abs <> 0 or
		plhc.Service_Abs <> 0 or
		plhc.Meter_Abs <> 0 
	) AND 

		pd.flag_Activated = 1 AND
		pd.Deleted = 0 AND
		pd.AttributeIdent <> '' 
	ORDER BY
		pd.AttributeClass,pd.AttributeIdent,pdv.AttributeValue

#2

#3

Duplicate