2 thoughts on “About”

  1. Hello,

    I have several merge statements that populate my type 1 and 2 dimensions in my data warehouse that I built. I have one that has recently acted up that I am not able to figure out. Was wondering if someone could help.

    I have the following table that gets populated from an excel file as part of my SSIS ETL package. This is the Royalty Staging Table. NOTE That I convert the datatypes from the excel file to the types that I have in the database in both the dimension and staging table .

    Note that for some reason this just stopped working for any new additions to the items in the stageing. I get an error that is Msg 8114, Level 16, State 5, Line 21
    Error converting data type varchar to numeric.

    I am not following why I am getting this error since I make sure that all tata is of the datatype before it goes into staging and hence any new records that end up in the dimension table.

    Please advise if you can point me in the right direction to what is happening. I am suspectng that SQL Server is MERGE_OUT portion and making a tempory table before the insert.

    —- This is the script of the Staging Table

    CREATE TABLE [dbo].[Royalty Destination STG](
    [Item] [varchar](25) NOT NULL,
    [Artist] [varchar](100) NOT NULL,
    [License] [varchar](50) NOT NULL,
    [Rate_Other] [numeric](8, 4) NOT NULL,
    [Rate_MASS] [numeric](8, 4) NOT NULL,
    [Rate_RMND] [numeric](8, 4) NOT NULL,
    [Rate_MarMaxx_Tuesday_Morn] [numeric](8, 4) NOT NULL
    ) ON [PRIMARY]

    GO

    —- This is the Script for the Dimension Table

    CREATE TABLE [dbo].[Dim_Royalty_Master](
    [Royalty_Item_Key] [int] IDENTITY(1,1) NOT NULL,
    [date_effective] [datetime] NOT NULL,
    [date_expires] [datetime] NOT NULL,
    [Row_Change_Reason] [nvarchar](200) NULL,
    [Item] [varchar](25) NOT NULL,
    [Artist] [varchar](100) NULL,
    [Rate_Other] [numeric](8, 4) NOT NULL,
    [Royalty_Rate_MASS] [numeric](8, 4) NOT NULL,
    [Royalty_Rate_RMND] [numeric](8, 4) NOT NULL,
    [Royalty_Rate_MarMaxx_Tuesday_Morn] [numeric](8, 4) NOT NULL,
    [License_#] [varchar](50) NOT NULL
    ) ON [PRIMARY]

    GO

    —This is my Merge Statement

    MERGE INTO Dim_Royalty_Master AS RM
    USING stgTEST.dbo.[Royalty Destination STG] AS RS
    ON (RM.Item = RS.Item)

    WHEN MATCHED AND

    (RM.Artist RS.Artist)
    OR (RM.License_# RS.License)

    — Update all existing rows for Type 1 changes
    THEN UPDATE SET RM.Artist = RS.Artist
    ,RM.License_# = RS.License

    ;

    /***********************
    Handle Type 2 changes
    ***********************/
    INSERT INTO Dim_Royalty_Master
    (Item
    ,Artist
    ,License_#
    ,Rate_Other
    ,Royalty_Rate_MASS
    ,Royalty_Rate_RMND
    ,Royalty_Rate_MarMaxx_Tuesday_Morn
    ,Row_Change_Reason
    ,date_effective
    ,date_expires)
    SELECT MERGE_OUT.Item
    ,MERGE_OUT.Artist
    ,MERGE_OUT.License_#
    ,MERGE_OUT.Rate_Other
    ,MERGE_OUT.Royalty_Rate_MASS
    ,MERGE_OUT.Royalty_Rate_RMND
    ,MERGE_OUT.Royalty_Rate_MarMaxx_Tuesday_Morn
    ,MERGE_OUT.Row_Change_Reason
    ,MERGE_OUT.date_effective
    ,MERGE_OUT.date_expires
    FROM
    ( MERGE Dim_Royalty_Master RM
    USING stgTEST.dbo.[Royalty Destination STG] RS
    ON (RM.Item = RS.Item)

    WHEN NOT MATCHED THEN — Insert the new rows
    INSERT VALUES(RS.Item
    ,RS.Artist
    ,RS.License
    ,RS.Rate_Other
    ,RS.Rate_MASS
    ,RS.Rate_RMND
    ,RS.Rate_MarMaxx_Tuesday_Morn
    ,’Row is New’
    ,’1753-01-01 00:00:00.000′
    ,’9999-12-31 00:00:00.000′)
    WHEN MATCHED AND date_expires = ‘9999-12-31 00:00:00.000’
    AND ((RM.Rate_Other RS.Rate_Other) — Compare the Type 2 columns to see if we have a change
    OR (RM.Royalty_Rate_MASS RS.Rate_MASS)
    OR (RM.Royalty_Rate_RMND Rate_RMND)
    OR (RM.Royalty_Rate_MarMaxx_Tuesday_Morn Rate_MarMaxx_Tuesday_Morn))
    THEN — If it’s a Type 2 change, expire the old row here
    UPDATE SET RM.date_expires = DATEADD(dd, DATEDIFF(dd, 0, getdate()-1), 0)
    — Output the changed rows which will feed into the INSERT statement
    OUTPUT $Action Action_Out, RS.Item
    ,RS.Artist
    ,RS.License
    ,RS.Rate_Other
    ,RS.Rate_MASS
    ,RS.Rate_RMND
    ,RS.Rate_MarMaxx_Tuesday_Morn
    ,(case when INSERTED.Rate_Other RS.Rate_Other then ‘SCD2 Change Rate Other ‘ else ” end +
    case when INSERTED.Royalty_Rate_MASS RS.Rate_MASS then ‘SCD2 Change Rate MASS ‘ else ” end +
    case when INSERTED.Royalty_Rate_RMND RS.Rate_RMND then ‘SCD2 Change Rate RMND ‘ else ” end +
    case when INSERTED.Royalty_Rate_MarMaxx_Tuesday_Morn RS.Rate_MarMaxx_Tuesday_Morn then ‘SCD2 Change Rate Rate_MarMaxx_Tuesday_Morn ‘ else ” end) Row_Change_Reason
    ,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) date_effective
    ,’9999-12-31 00:00:00.000’ date_expires
    ) AS MERGE_OUT (Action_Out, Item
    ,Artist
    ,License_#
    ,Rate_Other
    ,Royalty_Rate_MASS
    ,Royalty_Rate_RMND
    ,Royalty_Rate_MarMaxx_Tuesday_Morn
    ,Row_Change_Reason
    ,date_effective
    ,date_expires)
    WHERE
    MERGE_OUT.Action_Out = ‘UPDATE’ — this limits the output row set to only the Type 2 rows
    ;

    GO

    Reply

    1. Hi Scott,

      That’s clearly a casting error, but it could be a mask to another error :).

      Can you provide a example where I can test this script with data?

      Reply

Leave a comment