Incorrect syntax

Question

Post reply

Incorrect syntax

raym

SSCrazy

Points: 2213
More actions
November 20, 2008 at 7:28 am

#383428

I'm getting a syntax error. Where am I going wrong here
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ','.
DECLARE @sql VARCHAR(8000),@Col1 VARCHAR(10),@Col2 VARCHAR(10),@Col3 VARCHAR(10
)
SELECT @Col1 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbComm_CurrentMonth' AND ORDINAL_POSITION =(6
)
SELECT @Col2 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbComm_CurrentMonth' AND ORDINAL_POSITION =(7
)
SELECT @Col3 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbComm_CurrentMonth' AND ORDINAL_POSITION =(8
)
set @sql =
'SELECT DISTINCT
a.LoanRef
,a.First_Perend_Date
,a.Branch
,a.Balance
,a.EvenInstalment'
','+ @Col1
','+ @Col2
','+ @Col3
,'a.ProductCode
,a.Description
,a.Missed_Instalment
,a.Miss_Payment_Flag
,b.threemonthmovingavg
,a.RUNDATE
FROM
dbo.tbComm_3Months_Ago a INNER JOIN EDW_Stage..tbComm_Summary_Branch b ON a.productcode = b.productcode
WHERE Source = 3 ORDER BY a.Loanref'
exec (@sql)

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

awe comeon, you didn't need to post a question for this:

your error says line 24;

if you go to your query, you'll see this, starting (guess where? line 24!):

','+ @Col1

','+ @Col2

','+ @Col3

you are missing commas in the front of these three lines.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

raym SSCrazy Points: 2213 More actions · Answer 2

this is the table

when I select

select @Col1

select @Col2

select @Col3 I get the right values. But when I execute the script it gives me

Msg 207, Level 16, State 1, Line 6

Invalid column name 'May08AMT'.

CREATE TABLE dbo.tbComm_CurrentMonth

(

LoanRefVARCHAR(20)NULL,

First_Perend_DateINTNULL,

BranchCHAR(5)NULL,

BalanceNUMERIC(13, 2)NOT NULL,

EvenInstalmentNUMERIC(13, 2)NOT NULL,

Jul08AMTNUMERIC(13, 2)NULL,

Jun08AMTNUMERIC(13, 2)NULL,

May08AMTNUMERIC(13, 2)NULL,

ProductCodeCHAR(1)NULL,

DescriptionVARCHAR(100)NULL,

Missed_InstalmentDECIMAL(18, 3)NULL,

Miss_Payment_FlagSMALLINTNULL,

threemonthmovingavgDECIMAL(13, 3)NULL,

RundateDATETIMENULL

)

DECLARE @sql VARCHAR(8000),@Col1 VARCHAR(10),@Col2 VARCHAR(10),@Col3 VARCHAR(10

)

SELECT @Col1 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'tbComm_CurrentMonth' AND ORDINAL_POSITION =(6

)

SELECT @Col2 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'tbComm_CurrentMonth' AND ORDINAL_POSITION =(7

)

SELECT @Col3 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'tbComm_CurrentMonth' AND ORDINAL_POSITION =(8

)

SET @sql =

'SELECT DISTINCT

a.LoanRef

,a.First_Perend_Date

,a.Branch

,a.Balance

,a.EvenInstalment'

+ ',' + @Col1

+ ',' + @Col2 +

+ ',' + @Col3 +

',a.ProductCode

,a.Description

,a.Missed_Instalment

,a.Miss_Payment_Flag

,b.threemonthmovingavg

,a.RUNDATE

FROM

dbo.tbComm_3Months_Ago a INNER JOIN EDW_Stage..tbComm_Summary_Branch b ON a.productcode = b.productcode

WHERE Source = 3 ORDER BY a.Loanref'

EXEC (@Sql)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

raym (11/20/2008)CREATE TABLE dbo.tbComm_CurrentMonth(
LoanRefVARCHAR(20)NULL,
First_Perend_DateINTNULL,
BranchCHAR(5)NULL,
BalanceNUMERIC(13, 2)NOT NULL,
EvenInstalmentNUMERIC(13, 2)NOT NULL,
Jul08AMTNUMERIC(13, 2)NULL,
Jun08AMTNUMERIC(13, 2)NULL,
May08AMTNUMERIC(13, 2)NULL,
ProductCodeCHAR(1)NULL,
DescriptionVARCHAR(100)NULL,
Missed_InstalmentDECIMAL(18, 3)NULL,
Miss_Payment_FlagSMALLINTNULL,
threemonthmovingavgDECIMAL(13, 3)NULL,
RundateDATETIMENULL
)
DECLARE @sql VARCHAR(8000),@Col1 VARCHAR(10),@Col2 VARCHAR(10),@Col3 VARCHAR(10
)
SELECT @Col1 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbComm_CurrentMonth' AND ORDINAL_POSITION =(6
)
SELECT @Col2 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbComm_CurrentMonth' AND ORDINAL_POSITION =(7
)
SELECT @Col3 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbComm_CurrentMonth' AND ORDINAL_POSITION =(8
)
SET @sql =
'SELECT DISTINCT
a.LoanRef
,a.First_Perend_Date
,a.Branch
,a.Balance
,a.EvenInstalment'
+ ',' + @Col1
+ ',' + @Col2 +
+ ',' + @Col3 +
',a.ProductCode
,a.Description
,a.Missed_Instalment
,a.Miss_Payment_Flag
,b.threemonthmovingavg
,a.RUNDATE
FROM
dbo.tbComm_3Months_Ago a INNER JOIN EDW_Stage..tbComm_Summary_Branch b ON a.productcode = b.productcode
WHERE Source = 3 ORDER BY a.Loanref'
EXEC (@Sql)

I see a discrepency between your query, and the table definition you provide. See the bolded parts above.

raym SSCrazy Points: 2213 More actions · Answer 4

raym

SSCrazy

Points: 2213

November 20, 2008 at 11:43 pm

#901455

Thanks ....Silly mistake on my part

j nuñez SSCrazy Points: 2175 More actions · Answer 5

Another time, you can use a PRINT(@sql) before the EXEC(@sql) to analyze the DML you are building.