Change Function into SQL Statement

Question

Post reply

Change Function into SQL Statement

DinoRS

SSCrazy

Points: 2683

December 16, 2019 at 4:58 pm

Go to Answer

#3708219

Hi SSC,

I have the following Function

CREATE FUNCTION [dbo].[fCompareDO] 
    (
      @DealerNew INT ,
      @DealerOld INT
    )
RETURNS INT
AS
    BEGIN
    
    
DECLARE @DealerGroupNew as varchar(255);
        DECLARE @DealerGroupOld as varchar(255);


-- Return 0 if the dealers are not diffrent.
-- Pseodocode: 
--IF HändlerNeu = HändlerAlt
--Then StartDateNeu = StartDateAlt
IF @DealerNew = @DealerOld RETURN 0;


-- Get the dealergroup of the new dealer
select @DealerGroupNew = case when a.Gruppe is null then 'Einzelhändler' else a.Gruppe end
from [D_SalesOrg] left outer join (
  SELECT HNR, Gruppe from STG_Dealer_Groups
) as a on SalesOrg_CustomerWS_Code = a.HNR
where [SalesOrg_CustomerWS_ID] = @DealerNew


-- Return 1 if the dealers are not diffrent and the new dealer is an independent dealer.
-- Pseodocode:
--IF HändlerNeu <> HändlerAlt & Gruppe = Einzelhändler
--Then StartDateNeu = NEU
IF @DealerNew != @DealerOld and @DealerGroupNew = 'Einzelhändler' RETURN 1;


-- Get the dealergroup of the old dealer
select @DealerGroupOld = case when a.Gruppe is null then 'Einzelhändler' else a.Gruppe end
from [D_SalesOrg] left outer join (
  /*
  SELECT [DealerID], [Value] as Dealer_Group
  FROM [STG_Dealer_Details]
  where Attribute = 'Gruppe'
  */
  SELECT HNR, Gruppe from STG_Dealer_Groups
) as a on SalesOrg_CustomerWS_Code = a.HNR
where [SalesOrg_CustomerWS_ID] = @DealerOld


-- Return 0 if the new and the old dealer are in the same group
-- Pseodocode:
--IF GruppeNeu = GruppeAlt 
--Then StartDateNeu = StartDateAlt
IF @DealerGroupNew = @DealerGroupOld RETURN 0;

  

-- The last remaining Case: the new dealer and the old dealer are not in the same group.
--If GruppeNeu <> GruppeAlt
--Then startDateNeu = NEU
        RETURN 1;
        

    END

GO

this is rather simple I think, both Retailing_Dealer_ID and Ordering_Dealer_ID_Prev are integers so in theory this should work:

select  
f.Ordering_Dealer_ID,
f.Retailing_Dealer_ID,
f.Ordering_Dealer_ID_Prev--,
--dbo.fCompareDealerOrg(f.Retailing_Dealer_ID, f.Ordering_Dealer_ID_Prev) as RelevantDealerGroupChange
,f.Retailing_Dealer_ID as Dealer_New,
f.Ordering_Dealer_ID_Prev as Dealer_Old, 
case when a.Gruppe is null then 'Einzelhändler' else a.Gruppe end as 'Dealer_Gruppe',
case when  ((f.Retailing_Dealer_ID <> f.Ordering_Dealer_ID_Prev) and (a.Gruppe = 'Einzelhändler')) then '1'
when  ((Retailing_Dealer_ID = Ordering_Dealer_ID_Prev)) then '0'
when ((Retailing_Dealer_ID != Ordering_Dealer_ID_Prev) and (a.Gruppe != 'Einzelhändler')) then '1'
END as RelevantGroupChange
from dbo.STG_F_Sales f
left outer join dbo.D_SalesOrg d 
on d.SalesOrg_CustomerWS_ID = f.Retailing_Dealer_ID
left join (
SELECT HNR, Gruppe from STG_Dealer_Groups)
as a on d.SalesOrg_CustomerWS_Code = a.HNR
where d.SalesOrg_CustomerWS_ID = f.Retailing_Dealer_ID
order by f.PK_Sales desc

however as it looks like I'm having trouble getting a 0 or 1 for RelevantGroupChange if the Ordering_Dealer_ID_Prev is NULL - I don't see any RelevantGroupChange at all right now within the data but that might aswell just be because there was none.

Any thoughts if my CASE might still be valid despite all the NULLs I'm seeing, if not what should I change?

John Mitchell-245523

SSC Guru

Points: 148809
More actions
December 18, 2019 at 9:39 am
Answer
#3708722
If you're going to allow NULLs in your data then you need to handle them where they occur. Try changing the penultimate line like this:
```
and COALESCE(x.Ordering_Dealer_ID_Prev,0) = do.DealerOld
```
John

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 1

You have:

where d.SalesOrg_CustomerWS_ID = f.Retailing_Dealer_ID

in the WHERE clause which is comparing the left joined column d.SalesOrg_CustomerWS_ID to a column.

So it is the same as having an INNER JOIN. Maybe you want to delete that line as it's already in the left join?

DinoRS SSCrazy Points: 2683 More actions · Answer 2

DinoRS

SSCrazy

Points: 2683

December 16, 2019 at 5:19 pm

#3708232

You're right, I've removed that from the script.

DinoRS SSCrazy Points: 2683 More actions · Answer 3

Right, sofar I've got the following script:

with do (DealerGroupOld, DealerOld)
as
(Select Case when a.Gruppe is null then 'Einzelhändler' else a.Gruppe end, f.Ordering_Dealer_ID_Prev
from dbo.D_SalesOrg left outer join (
SELECT HNR, Gruppe from STG_Dealer_Groups
) as a on SalesOrg_CustomerWS_Code = a.HNR
left join STG_F_Sales f
on SalesOrg_CustomerWS_ID = f.Ordering_Dealer_ID_Prev
GROUP by Ordering_Dealer_ID_Prev, Gruppe
)
, dr (DealerGroupNew, DealerNew)
as
(SELECT CASE when b.Gruppe is null then 'Einzelhändler' else b.Gruppe end, g.Retailing_Dealer_ID
from dbo.D_SalesOrg left outer join (
select HNR, Gruppe from STG_Dealer_Groups
) as b on SalesOrg_CustomerWS_Code = b.HNR
left join stg_F_Sales g
on SalesOrg_CustomerWS_ID = g.Retailing_Dealer_ID
GROUP BY Retailing_Dealer_ID, Gruppe
)
SELECT --DISTINCT
x.PK_Sales, 
x.FK_PrevSales, 
x.Commission_ID,
x.ModelTypeFull_ID, 
x.Vehicle_Brand_Code,
x.Effective_DateTime, 
x.Effective_Date,
x.LifecycleStatus_ID, 
x.PrevSales_LC_ID,
x.BusinessTypeGroup_ID, 
x.PrevSales_BTG_ID, 
x.Ordering_Dealer_ID, 
x.Ordering_Dealer_BTG_ID, 
x.Invoicing_Dealer_ID, 
x.Retailing_Dealer_ID, 
x.Retailing_Dealer_BTG_ID, 
x.Disposability_ID, 
x.LastVehicleStatus, 
x.LastChangePerDay, 
x.StorageTime, 
x.EndDate, 
x.ProductGroup_ID, 
x.Floor_Variant_ID, 
x.Cabin_Width_ID, 
x.Value, 
x.Ordering_Dealer_ID_Prev,
case when (do.DealerOld = dr.DealerNew) and (do.DealerGroupOld = Dr.DealerGroupNew) then 0
when (do.DealerOld != dr.DealerNew) and do.DealerGroupOld != dr.DealerGroupNew then 1
when (do.DealerOld != dr.DealerNew) and (do.DealerGroupOld  = 'Einzelhändler')and (dr.DealerGroupNew = 'Einzelhändler') then 1
when  
do.DealerGroupOld = dr.DealerGroupNew then 0
end as RelevantDealerGroupChange
From STG_F_Sales x left outer join do
on x.Ordering_Dealer_ID_Prev = do.DealerOld 
left outer join dr
on x.Retailing_Dealer_ID = dr.DealerNew
where x.Retailing_Dealer_ID = dr.DealerNew
and x.Ordering_Dealer_ID_Prev = do.DealerOld
--order by PK_Sales

this script partially works now but I'm missing the first row where Ordering_Dealer_ID_Prev equals NULL, as far as I've seen the issue comes from the 'do' CTE as there is no SalesOrg_CustomerWS_ID with NULL as value. Any thoughts or ideas because right now I don't see it?

This is what I would expect:

expected

and this is what I get right now:

actual

Mike01 SSChampion Points: 11395 More actions · Answer 4

do you have any DDL and sample data you could provide? It's easier to work with real data than to look and say it should work

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

DinoRS SSCrazy Points: 2683 More actions · Answer 5

sure I have, here you go:

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[STG_F_Sales]
(
[PK_Sales] [bigint] NOT NULL,
[FK_PrevSales] [bigint] NULL,
[Commission_ID] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ModelTypeFull_ID] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Vehicle_Brand_Code] [int] NULL,
[Effective_DateTime] [datetime] NOT NULL,
[Effective_Date] [datetime] NOT NULL,
[LastVehicleStatus] [bit] NOT NULL,
[LastChangePerDay] [bit] NOT NULL,
[StorageTime] [bigint] NULL,
[EndDate] [datetime] NULL,
[Retailing_Dealer_ID] [int] NULL,
[Ordering_Dealer_ID_Prev] [int] NULL,
[SortKey] [int] NULL,
[Ordering_Dealer_ID] [int] NULL,

INDEX [IDX_PK_SALES_DESC] NONCLUSTERED 
(
[PK_Sales] DESC
),
 CONSTRAINT [STG_F_Sales_primaryKey]  PRIMARY KEY NONCLUSTERED HASH 
(
[PK_Sales]
)WITH ( BUCKET_COUNT = 2097152)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

ALTER TABLE [dbo].[STG_F_Sales] ADD  CONSTRAINT [DF__STG_F_Sal__LastV__2181F854]  DEFAULT ((0)) FOR [LastVehicleStatus]
GO

ALTER TABLE [dbo].[STG_F_Sales] ADD  CONSTRAINT [DF__STG_F_Sal__LastC__22761C8D]  DEFAULT ((0)) FOR [LastChangePerDay]
GO



USE [TestDB]
GO

INSERT INTO [dbo].[STG_F_Sales]
           ([PK_Sales]
           ,[FK_PrevSales]
           ,[Commission_ID]
           ,[ModelTypeFull_ID]
           ,[Vehicle_Brand_Code]
           ,[Effective_DateTime]
           ,[Effective_Date]
           ,[LastVehicleStatus]
           ,[LastChangePerDay]
           ,[StorageTime]
           ,[EndDate]
           ,[Retailing_Dealer_ID]
           ,[Ordering_Dealer_ID_Prev]
   ,[Ordering_Dealer_ID]
           ,[SortKey]
           )
     VALUES
           (
'1','2','0057~00002','16315412-000','100','2000-08-03 09:00:00.000','2000-08-03 00:00:00.000','1','1','0','2019-12-17 12:52:46.567','14765','14765','14765','2'),
('2','3','0057~00002','16315412-000','100','2000-08-03 08:00:00.000','2000-08-03 00:00:00.000','0','0','0','2000-08-03 00:00:00.000','14765','14765','14765','3'),
('3','4','0057~00002','16315412-000','100','2000-08-03 07:00:00.000','2000-08-03 00:00:00.000','0','0','0','2000-08-03 00:00:00.000','14765','14765','14765','4'),
('4','5','0057~00002','16315412-000','100','2000-08-03 06:00:00.000','2000-08-03 00:00:00.000','0','0','0','2000-08-03 00:00:00.000','14765','14765','14765','5'),
('5','','0057~00002','16315412-000','100','2000-01-11 00:00:00.000','2000-01-11 00:00:00.000','0','1','0','2000-08-03 00:00:00.000','14765','14765','','0'),
('6','7','0057~00003','16803312-000','100','2000-03-14 09:00:00.000','2000-03-14 00:00:00.000', '1','1','0','2019-12-17 12:52:46.567','14765','14765','14765','2'),
('7','8','0057~00003','16803312-000','100','2000-03-14 08:00:00.000','2000-03-14 00:00:00.000','0','0','0','2000-03-14 00:00:00.000','14765','14765','14765','3'),
('8','9','0057~00003','16803312-000','100','2000-03-14 07:00:00.000','2000-03-14 00:00:00.000','0','0','0','2000-03-14 00:00:00.000','14765','14765','14765','4'),
('9','10','0057~00003','16803312-000','100','2000-03-14 06:00:00.000','2000-03-14 00:00:00.000','0','0','0','2000-03-14 00:00:00.000','14765','14765','14765','5'),
('10','','0057~00003','16803312-000','100','2000-01-12 00:00:00.000','2000-01-12 00:00:00.000','0','1','0','2000-03-14 00:00:00.000','14765','14765','','0')
GO

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[D_SalesOrg]
(
[PK_SalesOrg] [int] IDENTITY(1,1) NOT NULL,
[SalesOrg_CustomerWS_ID] [int] NOT NULL,
[SalesOrg_CustomerWS_Code] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,

 CONSTRAINT [D_SalesOrg_primaryKey]  PRIMARY KEY NONCLUSTERED HASH 
(
[PK_SalesOrg]
)WITH ( BUCKET_COUNT = 1024)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

USE [TestDB]
GO

INSERT INTO [dbo].[D_SalesOrg]
           ([SalesOrg_CustomerWS_ID]
           ,[SalesOrg_CustomerWS_Code])
     VALUES
           ('14765','00260')
GO

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[STG_Dealer_Groups]
(
[HNR] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Gruppe] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,

INDEX [STG_Dealer_Groups_index] NONCLUSTERED HASH 
(
[HNR],
[Gruppe]
)WITH ( BUCKET_COUNT = 256)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO


USE [TestDB]
GO

INSERT INTO [dbo].[STG_Dealer_Groups]
           ([HNR]
           ,[Gruppe])
     VALUES
           ('00260', 'Test Dealer'
           )
GO

USE [TestDB]
GO

UPDATE [dbo].[STG_F_Sales]
   SET 
      [FK_PrevSales] = ''

 WHERE FK_PrevSales = '0'
GO
UPDATE [dbo].[STG_F_Sales]
   SET [Ordering_Dealer_ID_Prev] = '0'
      ,[Ordering_Dealer_ID] = '14765'
 WHERE Ordering_Dealer_ID = '0'
GO

--Query
with do (DealerGroupOld, DealerOld)
as
(Select Case when a.Gruppe is null then 'Einzelhändler' else a.Gruppe end, f.Ordering_Dealer_ID_Prev
from dbo.D_SalesOrg left outer join (
SELECT HNR, Gruppe from STG_Dealer_Groups
) as a on SalesOrg_CustomerWS_Code = a.HNR
left join STG_F_Sales f
on SalesOrg_CustomerWS_ID = f.Ordering_Dealer_ID_Prev
GROUP by Ordering_Dealer_ID_Prev, Gruppe
)
, dr (DealerGroupNew, DealerNew)
as
(SELECT CASE when b.Gruppe is null then 'Einzelhändler' else b.Gruppe end, g.Retailing_Dealer_ID
from dbo.D_SalesOrg left outer join (
select HNR, Gruppe from STG_Dealer_Groups
) as b on SalesOrg_CustomerWS_Code = b.HNR
left join stg_F_Sales g
on SalesOrg_CustomerWS_ID = g.Retailing_Dealer_ID
GROUP BY Retailing_Dealer_ID, Gruppe
)
SELECT --DISTINCT
x.PK_Sales, 
x.FK_PrevSales, 
x.Commission_ID,
x.ModelTypeFull_ID, 
x.Vehicle_Brand_Code,
x.Effective_DateTime, 
x.Effective_Date,
x.Ordering_Dealer_ID, 
x.Retailing_Dealer_ID, 
x.LastVehicleStatus, 
x.LastChangePerDay, 
x.StorageTime, 
x.EndDate, 
x.Ordering_Dealer_ID_Prev,
case when (do.DealerOld = dr.DealerNew) and (do.DealerGroupOld = Dr.DealerGroupNew) then 0
when (do.DealerOld != dr.DealerNew) and do.DealerGroupOld != dr.DealerGroupNew then 1
when (do.DealerOld != dr.DealerNew) and (do.DealerGroupOld  = 'Einzelhändler')and (dr.DealerGroupNew = 'Einzelhändler') then 1
when --(do.DealerOld != dr.DealerNew) and 
do.DealerGroupOld = dr.DealerGroupNew then 0
--when (do.DealerOld != dr.DealerNew) 
end as RelevantDealerGroupChange
From STG_F_Sales x, do, dr
where x.Retailing_Dealer_ID = dr.DealerNew
and x.Ordering_Dealer_ID_Prev = do.DealerOld
order by PK_Sales

I would expect for any row where FK_PrevSales = 0 or NULL to have for RelevantGroupChange a 1 instead of 0, logic works sofar for all other test cases.

DinoRS SSCrazy Points: 2683 More actions · Answer 6

with do (DealerGroupOld, DealerOld)
as
(Select Case when a.Gruppe is null then 'Einzelhändler' else a.Gruppe end, f.Ordering_Dealer_ID_Prev
from dbo.D_SalesOrg left outer join (
SELECT HNR, Gruppe from STG_Dealer_Groups
) as a on SalesOrg_CustomerWS_Code = a.HNR
left join STG_F_Sales f
on SalesOrg_CustomerWS_ID = f.Ordering_Dealer_ID_Prev
GROUP by Ordering_Dealer_ID_Prev, Gruppe
UNION ALL
SELECT '-2' as Gruppe, '' as Ordering_Dealer_ID_Prev

)
, dr (DealerGroupNew, DealerNew)
as
(SELECT CASE when b.Gruppe is null then 'Einzelhändler' else b.Gruppe end, g.Retailing_Dealer_ID
from dbo.D_SalesOrg left outer join (
select HNR, Gruppe from STG_Dealer_Groups
) as b on SalesOrg_CustomerWS_Code = b.HNR
left join stg_F_Sales g
on SalesOrg_CustomerWS_ID = g.Retailing_Dealer_ID
GROUP BY Retailing_Dealer_ID, Gruppe
)
SELECT --DISTINCT
x.PK_Sales, 
x.FK_PrevSales, 
x.Commission_ID,
x.ModelTypeFull_ID, 
x.Vehicle_Brand_Code,
x.Effective_DateTime, 
x.Effective_Date,
x.Ordering_Dealer_ID, 
x.Retailing_Dealer_ID, 
x.LastVehicleStatus, 
x.LastChangePerDay, 
x.StorageTime, 
x.EndDate, 
x.Ordering_Dealer_ID_Prev,
case when (do.DealerOld = dr.DealerNew) and (do.DealerGroupOld = Dr.DealerGroupNew) then 0
when (do.DealerOld != dr.DealerNew) and do.DealerGroupOld != dr.DealerGroupNew then 1
when (do.DealerOld != dr.DealerNew) and (do.DealerGroupOld  = 'Einzelhändler')and (dr.DealerGroupNew = 'Einzelhändler') then 1
when do.DealerGroupOld = dr.DealerGroupNew then 0
end as RelevantDealerGroupChange
From STG_F_Sales x, do, dr
where x.Retailing_Dealer_ID = dr.DealerNew
and x.Ordering_Dealer_ID_Prev = do.DealerOld
order by PK_Sales

this is getting around the issue at hand if in the table for Ordering_Dealer_ID_Prev the value is 0 (as per example above) instead of NULL,

UPDATE [dbo].[STG_F_Sales]
   SET [Ordering_Dealer_ID_Prev] = NULL

 WHERE FK_PrevSales = '0'
GO

after that one, instead of 10 rows I get 8 rows returned.

DinoRS SSCrazy Points: 2683 More actions · Answer 7

Thank you! That's what I was missing together with changing

SELECT '-2' as Gruppe, '' as Ordering_Dealer_ID_Prev

to

SELECT '-2' as Gruppe, '0' as Ordering_Dealer_ID_Prev

looks very promising right now, running a comparison against the data coming out of the function.

Alternative / faster approaches are always welcome!