Merge statement not working with a null field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Merge statement not working with a null field

Author

Topic

sportsguy
Starting Member

39 Posts

Posted - 2014-07-28 : 14:44:10

I have an historical price table with starting periods in integer format of calendar year, month and week, and endperiods in the same format. If the ending period is NULL, then that is the currently active list price. . .

I have two merge processes,
a merge to identify changed prices against the endperiod is null field, and then to update that field with the end period file.

Then a second merge to insert the new record when there is no matching null field. . .

The first works, the second doesn't work as I expect it should. . it tries to insert a field when there is a null field. . .

comments and suggestions welcome! :)
First Merge to End period an old price:


DECLARE @ENDINGWEEK int
SET @ENDINGWEEK = 20140703 --(SELECT rw.PeriodWeek FROM dbo.REPORT_WEEK rw WHERE rw.REPORTWEEK_ID = 1)

MERGE dbo.LABOR_STD_RATES as TARGET
USING (SELECT tmp.Price_List_Name, pl.Price_List_Location, tmp.Item_Name, tmp.Price
	FROM dbo.tmp_PRICE_LIST tmp
		INNER JOIN dbo.LABOR_PIDS lp ON tmp.Item_Name = lp.Item_Nbr
		INNER JOIN dbo.PRICE_LISTS pl ON tmp.Price_List_Name = pl.Price_List_Name
	WHERE (tmp.Price_List_Name Like '% ELE PRICE LIST'
		OR tmp.Price_List_Name Like '% SPK PRICE LIST'
		OR tmp.Price_List_Name Like '% SUP PRICE LIST'
		OR tmp.Price_List_Name Like '% TIME SOLN PRICE LIST')
		AND pl.Price_List_Location IS Not NULL
		AND pl.Active = 'Y'
		AND (tmp.Price_List_Name like 'US DIST 202 %') --OR tmp.Price_List_Name like 'US DIST 434 %')
		) AS SOURCE
ON (TARGET.Price_List_Name = SOURCE.Price_List_Name AND TARGET.Labor_PID = SOURCE.Item_Name)

WHEN MATCHED AND target.End_PeriodWeek IS NULL AND TARGET.Hourly_Rate <> SOURCE.Price
	THEN UPDATE SET TARGET.End_PeriodWeek =  @ENDINGWEEK, TARGET.Validated = GETDATE()
;
GO

Now after I end period a particular price, there is no record in the table with a End_PerioWeek value IS NULL. . . so I merge on that and when not matched, then insert the record. . .


DECLARE @FOLLOWINGWEEK int
SET @FOLLOWINGWEEK = 20140704  --(SELECT rw.PeriodWeek_Following FROM dbo.REPORT_WEEK rw WHERE rw.REPORTWEEK_ID = 1)

MERGE dbo.LABOR_STD_RATES as TARGET
USING (SELECT tmp.Price_List_Name, pl.Price_List_Location, tmp.Item_Name, tmp.Price, Null As "EndPeriodWeek"
	FROM dbo.tmp_PRICE_LIST tmp
		INNER JOIN dbo.LABOR_PIDS lp ON tmp.Item_Name = lp.Item_Nbr
		INNER JOIN dbo.PRICE_LISTS pl ON tmp.Price_List_Name = pl.Price_List_Name
	WHERE (tmp.Price_List_Name Like '% ELE PRICE LIST'
		OR tmp.Price_List_Name Like '% SPK PRICE LIST'
		OR tmp.Price_List_Name Like '% SUP PRICE LIST'
		OR tmp.Price_List_Name Like '% TIME SOLN PRICE LIST')
		AND tmp.Price_List_Name NOT LIKE 'GSA%'
		AND pl.Active = 'Y'
		AND (tmp.Price_List_Name like 'US DIST 202 %')-- OR tmp.Price_List_Name like 'US DIST 421 %')   
	) AS SOURCE
ON (TARGET.Price_List_Name = SOURCE.Price_List_Name AND TARGET.Labor_PID = SOURCE.Item_Name AND SOURCE.EndPeriodWeek = Target.End_PeriodWeek)

WHEN NOT MATCHED BY TARGET
	THEN INSERT (Start_PeriodWeek
	, Price_List_Name
	, strDistrict
	, Labor_PID
	, Hourly_Rate
	, Validated)
VALUES (@FOLLOWINGWEEK
, Source.Price_List_Name
, Source.Price_List_Location
, Source.Item_Name
, Source.Price
, GETDATE()
)
;
GO

So i assume that the null match isn't working properly, workarounds?

thanks in advance,
sportsguy

MS Access 20 years, SQL hack

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-28 : 15:03:05

Question: why aren't you doing both merge operations in the same merge statement?

You might try
ON (TARGET.Price_List_Name = SOURCE.Price_List_Name AND TARGET.Labor_PID = SOURCE.Item_Name AND ISNUL(Target.End_PeriodWeek,SOURCE.EndPeriodWeek)
)

Or (shell only ) if you use one statment
USING
ON (TARGET.Price_List_Name = SOURCE.Price_List_Name AND TARGET.Labor_PID = SOURCE.Item_Name)
WHEN MATCHED AND target.End_PeriodWeek IS NULL AND TARGET.Hourly_Rate <> SOURCE.Price -- what do you want to happen when the TARGET.Hourly_Rate = SOURCE.Price, but the End_periodWeek is null
THEN UPDATE
WHEN NOT MATCHED BY TARGET AND SOURCE.EndPeriodWeek = ISNULL(Target.End_PeriodWeek,SOURCE.EndPeriodWeek)

WHEN NOT MATCHED BY TARGET AND SOURCE.EndPeriodWeek =

sportsguy
Starting Member

39 Posts

Posted - 2014-07-28 : 15:11:51

Hmmm, i guess I assumed that you only get one match or not match, but I appreciate the suggestions! the ISNULL suggestion was never thought of but makes perfect logic!

thanks!

MS Access 20 years, SQL hack

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources