need alternative of query
isnull(OrgGen.[CustomerNKey],-99) = isnull(COALESCE(AC.CustomerNKey,ORGGeneral.CustomerNKey),-99)
That's not a query, it's a boolean expression. Does it appear in a WHERE clause? A CASE expression? An IIF() call?
Please post the entire query you're trying to tune.
(OrgGen.[CustomerNKey] IS NULL AND AC.CustomerNKey IS NULL AND ORGGeneral.CustomerNKey IS NULL)
OR
(OrgGen.[CustomerNKey] = AC.CustomerNKey OR OrgGen.[CustomerNKey] = ORGGeneral.CustomerNKey)
WHERE
OrgGen.CustomerNKey = AC.CustomerNKey
OR
OrgGen.CustomerNKey = ORGGeneral.CustomerNKey
OR
(
OrgGen.CustomerNKey IS NULL
AND AC.CustomerNKey IS NULL
AND ORGGeneral.CustomerNKey
)
this is and condition in where cluse there are also other cluse
WHERE NOT EXISTS (SELECT 1
FROM [OCC].[dbo].[OrgGeneralInfo] AS OrgGen
--Inner join to include last valid row per key
INNER JOIN (SELECT [OrganisationID]
, MAX(ValidFromDate) MAXValidFromDate
FROM OCC.dbo.ORGGeneralInfo
GROUP BY [OrganisationID] ) AS ORGMax
ON ORGMax.OrganisationID = OrgGen.OrganisationID
AND ORGMax.MAXValidFromDate = OrgGen.ValidFromDate
WHERE OrgGen.[OrganisationID] = Org.[OrganisationID]
AND OrgGen.[KknrID] = KR.[KknrID]
AND OrgGen.[OrganisationNo] = R1.[OrgNo]
AND OrgGen.[Name] = R1.[OrgName]
AND OrgGen.[CustomerSegmentNKey] =
R1.[CustomerSegmentNKey]
AND OrgGen.[CustomerSegment] = R1.[CustomerSegment]
AND OrgGen.[SectorNKey] = R1.[SectorNKey]
AND OrgGen.[Sector] = R1.[Sector]
AND OrgGen.[SalesUnitNKey] = R1.[SalesUnitNKey]
AND OrgGen.[SalesUnit] = R1.[SalesUnit]
AND OrgGen.[SalesTeamNKey] = R1.[SalesTeamNKey]
AND OrgGen.[SalesTeam] = R1.[SalesTeam]
AND OrgGen.[SalesPersonNKey] = R1.[SalesPersonNKey]
AND OrgGen.[SalesPerson] = R1.[SalesPerson]
AND OrgGen.[SegmentNKey] = R1.[SegmentNKey]
AND OrgGen.[Segment] = R1.[Segment]
AND OrgGen.[IndustryNKey] = R1.[IndustryNKey]
AND OrgGen.[Industry] = R1.[Industry]
AND OrgGen.[AccountTypeNKey] = R1.[AccountTypeNKey]
AND OrgGen.[AccountType] = R1.[AccountType]
AND ISNULL(OrgGen.[CustomerNKey],-99) = ISNULL(ISNULL(AC.CustomerNKey,ORGGeneral.CustomerNKey),-99)
)
AND LEN(R1.ORGNAME)>0
thank you for response.it is appear in where cluse with and condition
and isnull(OrgGen.[CustomerNKey],-99) = isnull(COALESCE(AC.CustomerNKey,ORGGeneral.CustomerNKey),-99)
thanku for response.can we use these 2 statement simultanousluy