Coalescing names from multiple tables but ignoring blanks.

Hello.

I've got an SQL query where I have a SELECT line like ...

CASE WHEN ISNULL(SL.CUUSER2, '') <> '' THEN SL.CUUSER2 ELSE '[BLANK]' END [S/L Sort],

Open in new window


The concept here is that a NULL and a '' are to be shown as '[BLANK]' (the literal text that is).

I now have a similar query, where, rather than a single table, the sort is coming from 6 different tables.

So, convention says ...
COALESCE
		(
		CASE SL_1.CUSORT WHEN '' THEN NULL END,
		CASE SL_2.CUSORT WHEN '' THEN NULL END,
		CASE SL_3.CUSORT WHEN '' THEN NULL END,
		CASE SL_4.CUSORT WHEN '' THEN NULL END,
		CASE SL_5.CUSORT WHEN '' THEN NULL END,
		CASE SL_6.CUSORT WHEN '' THEN NULL END,
		'[BLANK]'
		) [S/L Sort],

Open in new window


But that results in an error ...
None of the result expressions in a CASE specification can be NULL.

Open in new window


Any suggestions?

One I have is to replace the FROM <tablename> with FROM (SELECT case when ...) tablename. But the query is already 614 lines long and whilst I can certainly do this, is there a shortcut I'm missing?

OOI. The massive query aggregates data from 30 tables over 13 different databases on 2 different SQL servers.
LVL 40
Richard QuadlingSenior Software DeveloperAsked:
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.

JestersGrindCommented:
You could probably use NULLIF instead of a CASE statement.  Something like this.

NULLIF(SL_1.CUSORT, '')

It compare the two values and if they are equal it returns NULL.

http://msdn.microsoft.com/en-us/library/ms177562.aspx

Greg

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
Richard QuadlingSenior Software DeveloperAuthor Commented:
Perfect!!!!

And for your enjoyment, here is the query ...
ALTER VIEW [dbo].[Source_Galahad]
AS
WITH Jobs AS
	(
	SELECT
		ARM_BTCJobs.BTCJob_ID,
		ARM_BTCJobs.Dataset_ID,
		ARM_BTCJobs.VehicleReg,
		ARM_BTCJobs.Head_SupplierName,
		ARM_BTCJobs.Head_SupplierName_Dis,
		CAST(CASE WHEN DistinctBTCJobOnQuery_ID.BTCJob_ID IS NOT NULL THEN 1 ELSE 0 END AS BIT) OnQuery,
		ARM_BTCJobs.CallOut_Qty,
		ARM_BTCJobs.CallOut_CallCentreUsed_Price,
		ARM_BTCJobs.CallOut_CallCentreUsed_Price_Uplift,
		ARM_BTCJobs.CallOut_Fee_Price,
		ARM_BTCJobs.CallOut_Fee_Price_Uplift,
		ARM_BTCJobs.CallOut_LabourCharge_Price,
		ARM_BTCJobs.CallOut_LabourCharge_Price_Uplift,
		ARM_BTCJobs.CallOut_TyreSupportFee_Price,
		ARM_BTCJobs.CallOut_TyreSupportFee_Price_Uplift,
		ARM_BTCJobs.TotalCost,
		ARM_BTCJobs.TotalUplift,
		ARM_BTCJobs.TotalUpliftedCost
	FROM
		-- All Jobs
		[BV-CLUSTER-SQL].Galahad.dbo.ARM_BTCJobs

		-- Include "On Query" marker.
		LEFT OUTER JOIN
			(
			SELECT DISTINCT
				BTCJob_ID
			FROM
				[BV-CLUSTER-SQL].Galahad.dbo.ARM_JobQueryData
			) DistinctBTCJobOnQuery_ID ON
				DistinctBTCJobOnQuery_ID.BTCJob_ID = ARM_BTCJobs.BTCJob_ID
	WHERE
		(
			-- Vehicle number must be valid.
			ISNULL(ARM_BTCJobs.VehicleReg, '') <> ''

			AND

			-- The job must not be archived.
			ISNULL(ARM_BTCJobs.Archived, 0) = 0

			AND

			-- No Purchase Order Number.
			ARM_BTCJobs.POH_Order_Numbr IS NULL

			AND NOT

				(
				-- Non TSU jobs
				ISNULL(ARM_BTCJobs.CreatedFromTSUJob, 0) = 0

				AND
				-- Invalid Supplier
				ISNULL(ARM_BTCJobs.Head_SupplierName, '') = ''
				)
		)
		OR
		(
			-- Exists as a query
			DistinctBTCJobOnQuery_ID.BTCJob_ID IS NOT NULL
		)
	)
SELECT TOP 100 PERCENT
	-- CS+ No Costing Sales Ledger
	COALESCE
		(
		NULLIF(SL5.CUSORT,''),
		NULLIF(SL6.CUSORT,''),
		NULLIF(SL7.CUSORT,''),
		NULLIF(SL8.CUSORT,''),
		NULLIF(SL9.CUSORT,''),
		NULLIF(SL10.CUSORT,''),
		'[BLANK]'
		) [CS+NC S/L Sort],
	COALESCE
		(
		SL5.CUNAME,
		SL6.CUNAME,
		SL7.CUNAME,
		SL8.CUNAME,
		SL9.CUNAME,
		SL10.CUNAME
		) [CS+NC S/L Name],
	COALESCE
		(
		SL5.CUCODE,
		SL6.CUCODE,
		SL7.CUCODE,
		SL8.CUCODE,
		SL9.CUCODE,
		SL10.CUCODE
		) [CS+NC S/L Code],
	COALESCE
		(
		SL5.CU_ON_STOP,
		SL6.CU_ON_STOP,
		SL7.CU_ON_STOP,
		SL8.CU_ON_STOP,
		SL9.CU_ON_STOP,
		SL10.CU_ON_STOP
		) [CS+NC S/L On Stop],
	COALESCE
		(
		SL5.CU_DO_NOT_USE,
		SL6.CU_DO_NOT_USE,
		SL7.CU_DO_NOT_USE,
		SL8.CU_DO_NOT_USE,
		SL9.CU_DO_NOT_USE,
		SL10.CU_DO_NOT_USE
		) [CS+NC S/L Do Not Use],

	-- CS+ No Costing Purchase Ledger
	COALESCE
		(
		NULLIF(PL25.SU_USRCHAR1, ''),
		NULLIF(PL26.SU_USRCHAR1, ''),
		NULLIF(PL27.SU_USRCHAR1, ''),
		NULLIF(PL28.SU_USRCHAR1, ''),
		NULLIF(PL29.SU_USRCHAR1, ''),
		NULLIF(PL210.SU_USRCHAR1, ''),
		'[BLANK]'
		) [CS+NC P/L Sort],
	COALESCE
		(
		PL5.SUNAME,
		PL6.SUNAME,
		PL7.SUNAME,
		PL8.SUNAME,
		PL9.SUNAME,
		PL10.SUNAME
		) [CS+NC P/L Name],
	COALESCE
		(
		PL5.SUCODE,
		PL6.SUCODE,
		PL7.SUCODE,
		PL8.SUCODE,
		PL9.SUCODE,
		PL10.SUCODE
		) [CS+NC P/L Code],
	COALESCE
		(
		PL5.SU_ON_STOP,
		PL6.SU_ON_STOP,
		PL7.SU_ON_STOP,
		PL8.SU_ON_STOP,
		PL9.SU_ON_STOP,
		PL10.SU_ON_STOP
		) [CS+NC P/L On Stop],
	COALESCE
		(
		PL5.SU_DO_NOT_USE,
		PL6.SU_DO_NOT_USE,
		PL7.SU_DO_NOT_USE,
		PL8.SU_DO_NOT_USE,
		PL9.SU_DO_NOT_USE,
		PL10.SU_DO_NOT_USE
		) [CS+NC P/L Do Not Use],

	-- Database
	CASE Jobs.Dataset_ID
		WHEN 5 THEN 'CONTRACTS'
		WHEN 6 THEN 'GERMANY'
		WHEN 7 THEN 'HOLLAND'
		WHEN 8 THEN 'BELGIUM'
		WHEN 9 THEN 'BTCEUROPE'
		WHEN 10 THEN 'IRELAND'
		ELSE 'Unknown database' + CAST(Jobs.Dataset_ID AS VARCHAR(3))
		END [Database Name],

	-- On Query
	Jobs.OnQuery,

	CAST
		(
		SUM(ISNULL(Accessories.Value, 0))
		+
		SUM(ISNULL(Jobs.CallOut_CallCentreUsed_Price, 0))
		+
		SUM(ISNULL(Jobs.CallOut_Fee_Price, 0))
		+
		SUM(ISNULL(Husbandry.Value, 0))
		+
		(SUM(ISNULL(Jobs.CallOut_Qty, 0)) * SUM(ISNULL(Jobs.CallOut_LabourCharge_Price, 0)))
		+
		SUM(ISNULL(Misc.Value, 0))
		+
		SUM(ISNULL(TyresFitted.Value, 0))
		+
		SUM(ISNULL(Jobs.CallOut_TyreSupportFee_Price, 0))
		
		AS MONEY) [Value],

	CAST
		(
		SUM(ISNULL(Accessories.Uplift, 0))
		+
		SUM(ISNULL(Jobs.CallOut_CallCentreUsed_Price_Uplift, 0))
		+
		SUM(ISNULL(Jobs.CallOut_Fee_Price_Uplift, 0))
		+
		SUM(ISNULL(Husbandry.Uplift, 0))
		+
		(SUM(ISNULL(Jobs.CallOut_Qty, 0)) * SUM(ISNULL(Jobs.CallOut_LabourCharge_Price_Uplift, 0)))
		+
		SUM(ISNULL(Misc.Uplift, 0))
		+
		SUM(ISNULL(TyresFitted.Uplift, 0))
		+
		SUM(ISNULL(Jobs.CallOut_TyreSupportFee_Price_Uplift, 0))
		
		AS MONEY) [Uplift],

	CAST(SUM(ISNULL(TyresFitted.Rebate, 0)) AS MONEY) [Tyres Fitted Rebate]

FROM
	-- Unauthorised or On Query jobsheets.
	Jobs
	
	-- Accessories
	LEFT OUTER JOIN
		(
		SELECT
			Jobs.BTCJob_ID,
			SUM(ISNULL(ARM_Accessories.Qty, 0) * ISNULL(ARM_Accessories.MatrixPrice, 0)) Value,
			SUM(ISNULL(ARM_Accessories.Qty, 0) * ISNULL(ARM_Accessories.MatrixPrice_Uplift, 0)) Uplift
		FROM
			Jobs
			INNER JOIN
			[BV-CLUSTER-SQL].Galahad.dbo.ARM_Accessories
				ON
					Jobs.BTCJob_ID = ARM_Accessories.BTCJob_ID
		WHERE
			ISNULL(ARM_Accessories.Qty, 0) <> 0
			OR
			ISNULL(ARM_Accessories.MatrixPrice, 0) <> 0
			OR
			ISNULL(ARM_Accessories.MatrixPrice_Uplift, 0) <> 0
		GROUP BY
			Jobs.BTCJob_ID
		) Accessories ON
			Jobs.BTCJob_ID = Accessories.BTCJob_ID

	-- Husbandry
	LEFT OUTER JOIN
		(
		SELECT
			Jobs.BTCJob_ID,
			SUM(ISNULL(ARM_Husbandry.Qty, 0) * ISNULL(ARM_Husbandry.MatrixPrice, 0)) Value,
			SUM(ISNULL(ARM_Husbandry.Qty, 0) * ISNULL(ARM_Husbandry.MatrixPrice_Uplift, 0)) Uplift
		FROM
			Jobs
			INNER JOIN
			[BV-CLUSTER-SQL].Galahad.dbo.ARM_Husbandry
				ON
					Jobs.BTCJob_ID = ARM_Husbandry.BTCJob_ID
		WHERE
			ISNULL(ARM_Husbandry.Qty, 0) <> 0
			OR
			ISNULL(ARM_Husbandry.MatrixPrice, 0) <> 0
			OR
			ISNULL(ARM_Husbandry.MatrixPrice_Uplift, 0) <> 0
		GROUP BY
			Jobs.BTCJob_ID
		) Husbandry ON Jobs.BTCJob_ID = Husbandry.BTCJob_ID

	-- Miscellaneous
	LEFT OUTER JOIN
		(
		SELECT
			US_4.BTCJob_ID,
			SUM(ISNULL(ARM_Misc.MatrixPrice, 0)) Value,
			SUM(ISNULL(ARM_Misc.MatrixPrice_Uplift, 0)) Uplift
		FROM
			Jobs US_4
			INNER JOIN
			[BV-CLUSTER-SQL].Galahad.dbo.ARM_Misc
				ON
					US_4.BTCJob_ID = ARM_Misc.BTCJob_ID
		WHERE
			ISNULL(ARM_Misc.MatrixPrice, 0) <> 0
			OR
			ISNULL(ARM_Misc.MatrixPrice_Uplift, 0) <> 0
		GROUP BY
			US_4.BTCJob_ID
		) Misc ON Jobs.BTCJob_ID = Misc.BTCJob_ID

	-- Tyres Fitted
	LEFT OUTER JOIN
		(
		SELECT
			US_3.BTCJob_ID,
			COUNT(*) Items,
			SUM(ISNULL(ARM_TyresFitted.MatrixPrice, 0)) Value,
			SUM(ISNULL(ARM_TyresFitted.MatrixPrice_Uplift, 0)) Uplift,
			SUM(ISNULL(ARM_TyresFitted.MatrixPrice_Rebate, 0)) Rebate
		FROM
			Jobs US_3
			INNER JOIN
			[BV-CLUSTER-SQL].Galahad.dbo.ARM_TyresFitted
				ON
					US_3.BTCJob_ID = ARM_TyresFitted.BTCJob_ID
		WHERE
			ISNULL(ARM_TyresFitted.MatrixPrice, 0) <> 0
			OR
			ISNULL(ARM_TyresFitted.MatrixPrice_Uplift, 0) <> 0
		GROUP BY
			US_3.BTCJob_ID
		) TyresFitted ON Jobs.BTCJob_ID = TyresFitted.BTCJob_ID

	-- Vehicles (CS+ Cost Headers)

	-- Dataset_ID = 5 -- CONTRACTS
	LEFT OUTER JOIN
	[BV-CLUSTER-SQL].CONTRACTS.dbo.CST_COSTHEADER CH5
		ON
			Jobs.Dataset_ID = 5
			AND
			Jobs.VehicleReg = CH5.CH_CODE COLLATE SQL_Latin1_General_CP1_CI_AS

	-- Dataset_ID = 6 -- GERMANY
	LEFT OUTER JOIN
	[BV-CLUSTER-SQL].GERMANY.dbo.CST_COSTHEADER CH6
		ON
			Jobs.Dataset_ID = 6
			AND
			Jobs.VehicleReg = CH6.CH_CODE COLLATE SQL_Latin1_General_CP1_CI_AS

	-- Dataset_ID = 7 -- HOLLAND
	LEFT OUTER JOIN
	[BV-CLUSTER-SQL].HOLLAND.dbo.CST_COSTHEADER CH7
		ON
			Jobs.Dataset_ID = 7
			AND
			Jobs.VehicleReg = CH7.CH_CODE COLLATE SQL_Latin1_General_CP1_CI_AS

	-- Dataset_ID = 8 -- BELGIUM
	LEFT OUTER JOIN
	[BV-CLUSTER-SQL].BELGIUM.dbo.CST_COSTHEADER CH8
		ON
			Jobs.Dataset_ID = 8
			AND
			Jobs.VehicleReg = CH8.CH_CODE COLLATE SQL_Latin1_General_CP1_CI_AS

	-- Dataset_ID = 9 -- BTCEUROPE
	LEFT OUTER JOIN
	[BV-CLUSTER-SQL].BTCEUROPE.dbo.CST_COSTHEADER CH9
		ON
			Jobs.Dataset_ID = 9
			AND
			Jobs.VehicleReg = CH9.CH_CODE COLLATE SQL_Latin1_General_CP1_CI_AS

	-- Dataset_ID = 10 -- IRELAND
	LEFT OUTER JOIN
	[BV-CLUSTER-SQL].IRELAND.dbo.CST_COSTHEADER CH10
		ON
			Jobs.Dataset_ID = 10
			AND
			Jobs.VehicleReg = CH10.CH_CODE COLLATE SQL_Latin1_General_CP1_CI_AS

	-- Customers (CS+ NC Sales Ledger)

	-- Dataset_ID = 5 -- CONTRACTS
	LEFT OUTER JOIN
	[BV-VM-SVR-29].CONTRACTS_NO_COSTING.dbo.SL_ACCOUNTS SL5
		ON
			Jobs.Dataset_ID = 5
			AND
			CH5.CH_ACCOUNT = SL5.CUCODE

	-- Dataset_ID = 6 -- GERMANY
	LEFT OUTER JOIN
	[BV-VM-SVR-29].GERMANY_NO_COSTING.dbo.SL_ACCOUNTS SL6
		ON
			Jobs.Dataset_ID = 6
			AND
			CH6.CH_ACCOUNT = SL6.CUCODE

	-- Dataset_ID = 7 -- HOLLAND
	LEFT OUTER JOIN
	[BV-VM-SVR-29].CONTRACTS_NO_COSTING.dbo.SL_ACCOUNTS SL7
		ON
			Jobs.Dataset_ID = 7
			AND
			CH7.CH_ACCOUNT = SL7.CUCODE

	-- Dataset_ID = 8 -- BELGIUM
	LEFT OUTER JOIN
	[BV-VM-SVR-29].BELGIUM_NO_COSTING.dbo.SL_ACCOUNTS SL8
		ON
			Jobs.Dataset_ID = 8
			AND
			CH8.CH_ACCOUNT = SL8.CUCODE

	-- Dataset_ID = 9 -- BTC_EUROPE
	LEFT OUTER JOIN
	[BV-VM-SVR-29].BTC_EUROPE_NO_COSTING.dbo.SL_ACCOUNTS SL9
		ON
			Jobs.Dataset_ID = 9
			AND
			CH9.CH_ACCOUNT = SL9.CUCODE

	-- Dataset_ID = 10 -- IRELAND
	LEFT OUTER JOIN
	[BV-VM-SVR-29].IRELAND_NO_COSTING.dbo.SL_ACCOUNTS SL10
		ON
			Jobs.Dataset_ID = 10
			AND
			CH10.CH_ACCOUNT = SL10.CUCODE

	-- Suppliers (CS+ NC Purchase Ledger)

	-- Dataset_ID = 5 -- CONTRACTS
	LEFT OUTER JOIN
	[BV-VM-SVR-29].CONTRACTS_NO_COSTING.dbo.PL_ACCOUNTS PL5
		ON
			Jobs.Dataset_ID = 5
			AND
				(
				Jobs.Head_SupplierName = PL5.SUCODE COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
				OR
				Jobs.Head_SupplierName_Dis = PL5.SUNAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
				)
	LEFT OUTER JOIN
	[BV-VM-SVR-29].CONTRACTS_NO_COSTING.dbo.PL_ACCOUNTS2 PL25
		ON
			PL5.SU_PRIMARY = PL25.SU_PRIMARY_2

	-- Dataset_ID = 6 -- GERMANY
	LEFT OUTER JOIN
	[BV-VM-SVR-29].GERMANY_NO_COSTING.dbo.PL_ACCOUNTS PL6
		ON
			Jobs.Dataset_ID = 6
			AND
				(
				Jobs.Head_SupplierName = PL6.SUCODE COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
				OR
				Jobs.Head_SupplierName_Dis = PL6.SUNAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
				)
	LEFT OUTER JOIN
	[BV-VM-SVR-29].GERMANY_NO_COSTING.dbo.PL_ACCOUNTS2 PL26
		ON
			PL6.SU_PRIMARY = PL26.SU_PRIMARY_2

	-- Dataset_ID = 7 -- HOLLAND
	LEFT OUTER JOIN
	[BV-VM-SVR-29].HOLLAND_NO_COSTING.dbo.PL_ACCOUNTS PL7
		ON
			Jobs.Dataset_ID = 7
			AND
				(
				Jobs.Head_SupplierName = PL7.SUCODE COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
				OR
				Jobs.Head_SupplierName_Dis = PL7.SUNAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
				)
	LEFT OUTER JOIN
	[BV-VM-SVR-29].HOLLAND_NO_COSTING.dbo.PL_ACCOUNTS2 PL27
		ON
			PL7.SU_PRIMARY = PL27.SU_PRIMARY_2

	-- Dataset_ID = 8 -- BELGIUM
	LEFT OUTER JOIN
	[BV-VM-SVR-29].BELGIUM_NO_COSTING.dbo.PL_ACCOUNTS PL8
		ON
			Jobs.Dataset_ID = 8
			AND
				(
				Jobs.Head_SupplierName = PL8.SUCODE COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
				OR
				Jobs.Head_SupplierName_Dis = PL8.SUNAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
				)
	LEFT OUTER JOIN
	[BV-VM-SVR-29].BELGIUM_NO_COSTING.dbo.PL_ACCOUNTS2 PL28
		ON
			PL8.SU_PRIMARY = PL28.SU_PRIMARY_2

	-- Dataset_ID = 9 -- BTC_EUROPE
	LEFT OUTER JOIN
	[BV-VM-SVR-29].BELGIUM_NO_COSTING.dbo.PL_ACCOUNTS PL9
		ON
			Jobs.Dataset_ID = 9
			AND
				(
				Jobs.Head_SupplierName = PL9.SUCODE COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
				OR
				Jobs.Head_SupplierName_Dis = PL9.SUNAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
				)
	LEFT OUTER JOIN
	[BV-VM-SVR-29].BELGIUM_NO_COSTING.dbo.PL_ACCOUNTS2 PL29
		ON
			PL9.SU_PRIMARY = PL29.SU_PRIMARY_2

	-- Dataset_ID = 10 -- IRELAND
	LEFT OUTER JOIN
	[BV-VM-SVR-29].IRELAND_NO_COSTING.dbo.PL_ACCOUNTS PL10
		ON
			Jobs.Dataset_ID = 10
			AND
				(
				Jobs.Head_SupplierName = PL10.SUCODE COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
				OR
				Jobs.Head_SupplierName_Dis = PL10.SUNAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
				)
	LEFT OUTER JOIN
	[BV-VM-SVR-29].IRELAND_NO_COSTING.dbo.PL_ACCOUNTS2 PL210
		ON
			PL10.SU_PRIMARY = PL210.SU_PRIMARY_2

WHERE
	ISNULL(Accessories.Value, 0) <> 0
	OR
	ISNULL(Accessories.Uplift, 0) <> 0
	OR

	ISNULL(Jobs.CallOut_CallCentreUsed_Price, 0) <> 0
	OR
	ISNULL(Jobs.CallOut_CallCentreUsed_Price_Uplift, 0) <> 0
	OR

	ISNULL(Jobs.CallOut_Fee_Price, 0) <> 0
	OR
	ISNULL(Jobs.CallOut_Fee_Price_Uplift, 0) <> 0
	OR

	ISNULL(Husbandry.Value, 0) <> 0
	OR
	ISNULL(Husbandry.Uplift, 0) <> 0
	OR

	ISNULL(Jobs.CallOut_Qty * Jobs.CallOut_LabourCharge_Price, 0) <> 0
	OR
	ISNULL(Jobs.CallOut_Qty * Jobs.CallOut_LabourCharge_Price_Uplift, 0) <> 0
	OR

	ISNULL(Misc.Value, 0) <> 0
	OR
	ISNULL(Misc.Uplift, 0) <> 0
	OR

	ISNULL(TyresFitted.Value, 0) <> 0
	OR
	ISNULL(TyresFitted.Uplift, 0) <> 0
	OR

	ISNULL(Jobs.CallOut_TyreSupportFee_Price, 0) <> 0
	OR
	ISNULL(Jobs.CallOut_TyreSupportFee_Price_Uplift, 0) <> 0
GROUP BY
	SL5.CUSORT,
	SL6.CUSORT,
	SL7.CUSORT,
	SL8.CUSORT,
	SL9.CUSORT,
	SL10.CUSORT,
	SL5.CUNAME,
	SL6.CUNAME,
	SL7.CUNAME,
	SL8.CUNAME,
	SL9.CUNAME,
	SL10.CUNAME,
	SL5.CUCODE,
	SL6.CUCODE,
	SL7.CUCODE,
	SL8.CUCODE,
	SL9.CUCODE,
	SL10.CUCODE,
	SL5.CU_ON_STOP,
	SL6.CU_ON_STOP,
	SL7.CU_ON_STOP,
	SL8.CU_ON_STOP,
	SL9.CU_ON_STOP,
	SL10.CU_ON_STOP,
	SL5.CU_DO_NOT_USE,
	SL6.CU_DO_NOT_USE,
	SL7.CU_DO_NOT_USE,
	SL8.CU_DO_NOT_USE,
	SL9.CU_DO_NOT_USE,
	SL10.CU_DO_NOT_USE,
	PL25.SU_USRCHAR1,
	PL26.SU_USRCHAR1,
	PL27.SU_USRCHAR1,
	PL28.SU_USRCHAR1,
	PL29.SU_USRCHAR1,
	PL210.SU_USRCHAR1,
	PL5.SUNAME,
	PL6.SUNAME,
	PL7.SUNAME,
	PL8.SUNAME,
	PL9.SUNAME,
	PL10.SUNAME,
	PL5.SUCODE,
	PL6.SUCODE,
	PL7.SUCODE,
	PL8.SUCODE,
	PL9.SUCODE,
	PL10.SUCODE,
	PL5.SU_ON_STOP,
	PL6.SU_ON_STOP,
	PL7.SU_ON_STOP,
	PL8.SU_ON_STOP,
	PL9.SU_ON_STOP,
	PL10.SU_ON_STOP,
	PL5.SU_DO_NOT_USE,
	PL6.SU_DO_NOT_USE,
	PL7.SU_DO_NOT_USE,
	PL8.SU_DO_NOT_USE,
	PL9.SU_DO_NOT_USE,
	PL10.SU_DO_NOT_USE,
	Jobs.Dataset_ID,
	Jobs.OnQuery
GO

Open in new window

Richard QuadlingSenior Software DeveloperAuthor Commented:
Perfect.
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
Query Syntax

From novice to tech pro — start learning today.