Concatenation Issue

Question

Post reply

Concatenation Issue

stricknyn

SSCertifiable

Points: 6560
More actions
January 29, 2010 at 11:19 am

#138651

This should be pretty straightforward, but for some reason its not working and I can figure out why.
I included the SELECT portion. The error I get is
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ','.
This is the line where the address is. In looking at it, it looks correct.
SELECT
EL.SUBSCRIBER_ID,
EG.ENROLLEE_ID,
EN.FIRST_NAME MemberFirstName,
EN.LAST_NAME AS MemberLastName,
EN.DOB AS MemberDateofBirth,
EG.FIRST_NAME AS GuardianFirstName,
EG.LAST_NAME AS GuardianFirstName,
(EN.ADDRESS1 + ' ' + EN.CITY + ', ' + EN.STATE, + ' ' + EN.ZIP) AS MemberAddress,
EN.PHONE,
INS.DESCRIPTION AS INSURER,
P.NAME AS PRODUCT,EL.EFFECTIVE_DATE AS MemberEffectiveDate,
EL.TERMINATION_DATE AS MemberTerminationDate,
C.NAME AS County
Can anyone see if something is missing?
Thanks,
Strick

Viewing 7 posts - 1 through 7 (of 7 total)

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

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 1

I did not spot the location, however idea.

Select * instead of list (for debugging) if the syntax error still exists you know to look in your join/where sections.

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

Lynn Pettis

SSC Guru

Points: 442467

January 29, 2010 at 11:30 am

#1110909

Please post the entire query.

thanks

stricknyn SSCertifiable Points: 6560 More actions · Answer 3

Hi, Thanks for you response,

If I comment out the address line it works fine so I know it's not the join and/or where selects.

Thanks,

Strick

stricknyn SSCertifiable Points: 6560 More actions · Answer 4

Per request here is the whole query.

SELECT

EL.SUBSCRIBER_ID,

EG.ENROLLEE_ID,

EN.FIRST_NAME MemberFirstName,

EN.LAST_NAME AS MemberLastName,

EN.DOB AS MemberDateofBirth,

EG.FIRST_NAME AS GuardianFirstName,

EG.LAST_NAME AS GuardianFirstName,

(EN.ADDRESS1 + ' ' + EN.CITY + ', ' + EN.STATE, + ' ' + EN.ZIP) AS MemberAddress,

EN.PHONE,

INS.DESCRIPTION AS INSURER,

P.NAME AS PRODUCT,EL.EFFECTIVE_DATE AS MemberEffectiveDate,

EL.TERMINATION_DATE AS MemberTerminationDate,

C.NAME AS County

FROM dbo.ENROLLEE_GUARDIAN EG

INNER JOIN dbo.ENROLLEES EN ON EG.ENROLLEE_ID = EN.ENROLLEE_ID

INNER JOIN dbo.ELIGIBILITY EL ON EN.ENROLLEE_ID = EL.ENROLLEE_ID

INNER JOIN EnterpriseDB.dbo.INSURERS INS ON EL.INSURER_ID = INS.INSURER_ID

INNER JOIN EnterpriseDB.dbo.PRODUCTS P ON EL.PRODUCT_ID = P.PRODUCT_ID

INNER JOIN ENTERPRISEDB.DBO.COUNTIES C ON EN.COUNTY_ID = C.COUNTY_ID

WHERE LTRIM(RTRIM(EG.FIRST_NAME)) LIKE (@FNAME) AND LTRIM(RTRIM(EG.LAST_NAME)) LIKE (@LNAME) AND LTRIM(RTRIM(EG.LAST_NAME)) <> ''

AND EG.EFFECTIVE_DATE <= getdate() AND (EG.EXPIRATION_DATE > getdate() OR EG.EXPIRATION_DATE IS NULL)

AND EL.EFFECTIVE_DATE <= getdate() AND (EL.TERMINATION_DATE > getdate() OR EL.TERMINATION_DATE IS NULL)

ORDER BY LTRIM(RTRIM(EG.LAST_NAME)), LTRIM(RTRIM(EG.FIRST_NAME))

WayneS SSC Guru Points: 95461 More actions · Answer 5

stricknyn (1/29/2010)
This should be pretty straightforward, but for some reason its not working and I can figure out why.
I included the SELECT portion. The error I get is
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ','.
This is the line where the address is. In looking at it, it looks correct.
SELECT
EL.SUBSCRIBER_ID,
EG.ENROLLEE_ID,
EN.FIRST_NAME MemberFirstName,
EN.LAST_NAME AS MemberLastName,
EN.DOB AS MemberDateofBirth,
EG.FIRST_NAME AS GuardianFirstName,
EG.LAST_NAME AS GuardianFirstName,
(EN.ADDRESS1 + ' ' + EN.CITY + ', ' + EN.STATE, + ' ' + EN.ZIP) AS MemberAddress,
EN.PHONE,
INS.DESCRIPTION AS INSURER,
P.NAME AS PRODUCT,EL.EFFECTIVE_DATE AS MemberEffectiveDate,
EL.TERMINATION_DATE AS MemberTerminationDate,
C.NAME AS County
Can anyone see if something is missing?
Thanks,
Strick

Not missing, but extra:

(EN.ADDRESS1 + ' ' + EN.CITY + ', ' + EN.STATE, + ' ' + EN.ZIP) AS MemberAddress,

remove the comma after en.state

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

stricknyn SSCertifiable Points: 6560 More actions · Answer 6

Found it. There's an extra comma next to EN.State

Thanks all,

Strick