CODEWITHSUNDEEP
ssis
Ibru.M
Ibru.M

Reputation: 13

Incorrect Syntax Error near '='

Can anyone help me? I'm getting syntax error "Msg 102, Level 15, State 1, Line 26 Incorrect syntax near '='."

    {Insert Into [Mortgage_CDriveDW].[dbo].[Dim_LoanX]
    ([Loan_ID],
    [LoanDate],
    [LoanYear],
    [LoanMonth],
    [LoanWeek])}

        {Select Distinct
        [Loan_ID] = CAST(ISNULL([Loan_ID], -1) As Int),
        [LoanDate] = CAST(ISNULL([LoanDate], '01/01/1900')  As Date), 
        CONVERT(INT,DATEPART(YEAR,[LoanDate])) As [LoanYear] = CAST(ISNULL(CONVERT(INT,DATEPART(YEAR,[LoanDate])) As [LoanYear] -1) As Int),
    CONVERT(INT,DATEPART(MONTH,[LoanDate])) As [LoanMonth] = CAST(ISNULL(MONTH,[LoanDate], -1) As Int),
    CONVERT(INT,DATEPART(WEEK,[LoanDate]))  As [LoanWeek] = CAST(ISNULL(WEEK,[LoanDate], -1) As Int)
    From            [dbo].[ODS]}

{SET IDENTITY_INSERT [Mortgage_CDriveDW].[dbo].[Dim_LoanX] ON
GO
Insert Into [Mortgage_CDriveDW].[dbo].[Dim_LoanX]
(               [Loan_Key],
                [Loan_ID],
                [LoanDate],
                [LoanYear],
                [LoanMonth],
                [LoanWeek]
)
Select  -1, -1, -1, -1, -1 
SET IDENTITY_INSERT [Mortgage_CDriveDW].[dbo].[Dim_LoanX] OFF
GO}

I'm also trying to account for null values during insert and dimension loading with the Set Identity_Insert ON/OFF. The ODS Table has LoanYear/Month/Week as null values which I'm using a convert and datepart to insert the values in the columns. This is where the error is thrown when insert to load to Dim_LoanX table.

Upvotes: 0

Views: 191

Answers (1)

billinkc
billinkc

Reputation: 61259

Lines like this are not valid syntax

CONVERT(INT,DATEPART(YEAR,[LoanDate])) As [LoanYear] = CAST(ISNULL(CONVERT(INT,DATEPART(YEAR,[LoanDate])) As [LoanYear] -1) As Int)

There are two syntaxes for defining columns in a query

1 AS Col1

or Col1 = 1

You have both

Upvotes: 1

Related Questions