Left trim the Source.Description fields to 50
------------------------------------------
Update the dimMergedItemVariant stored procedure with this code
------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[dimMergedItemVariant]
@ItemVariant [dbo].[ItemVariantType] READONLY
AS
BEGIN
WITH tmaxrowid
AS (SELECT sIVA.[companyprefix],
sIVA.[item no_],
sIVA.[code],
Max(sIVA.[bigint_timestamp]) AS [RowID]
FROM [stg$item variant] sIVA
GROUP BY sIVA.[companyprefix],
sIVA.[item no_],
sIVA.[code]),
titemvariant
AS (SELECT sIVA.[companyprefix],
sIVA.[item no_],
sIVA.[code],
sIVA.[description],
sIVA.[description 2],
sIVA.[common item no_],
Getutcdate() AS [BatchDate],
Max(sIVA.[bigint_timestamp]) AS [RowID]
FROM [stg$item variant] sIVA
GROUP BY sIVA.[companyprefix],
sIVA.[item no_],
sIVA.[code],
sIVA.[description],
sIVA.[description 2],
sIVA.[common item no_])
MERGE [DW].[ditemvariant] AS Target
using (SELECT COALESCE(dCOM.[sk_company], -1) AS [Company],
tIVA.[item no_],
tIVA.[code],
tIVA.[description],
tIVA.[description 2],
tIVA.[common item no_],
tIVA.[batchdate],
tMRI.[rowid]
FROM [titemvariant] tIVA
RIGHT JOIN [tmaxrowid] tMRI
ON tIVA.[companyprefix] = tMRI.[companyprefix]
AND tIVA.[item no_] = tMRI.[item no_]
AND tIVA.[code] = tMRI.[code]
AND tIVA.[rowid] = tMRI.[rowid]
LEFT JOIN [DW].[dcompany] dCOM
ON tIVA.[companyprefix] = dCOM.[companyprefix]) AS
Source
ON Target.[item no_] = source.[Item No_]
AND Target.[code] = source.[Code]
AND Target.[company] = source.[Company]
WHEN matched THEN
UPDATE SET [item no_] = Source.[item no_],
[code] = Source.[code],
[description] = left(Source.[description],50),
[description 2] = Source.[description 2],
[common item no_] = Source.[common item no_],
[batchdate] = Source.[batchdate],
[rowid] = Source.[rowid]
WHEN NOT matched BY target THEN
INSERT ([company],
[item no_],
[code] ,
[description] ,
[description 2],
[common item no_],
[batchdate],
[rowid]
)
VALUES (Source.[company],
Source.[item no_],
Source.[code] ,
left(Source.[description],50),
Source.[description 2],
Source.[common item no_],
Source.[batchdate],
Source.rowid
);;
END
SELECT
'RowCount' = @@rowcount
No comments:
Post a Comment