March 30, 2006 at 9:32 am
So I have this Case Statement in My select clause. when I attempt to run it in QA I receive the wonderfully precise error
"Line 17: Incorrect syntax near ','."
So after Beating My head against the wall for an hour or so I'm throwing it out to you guys. If I comment out the case statement the Select query executes just fine. And yes I have all of the tables specified in my From clause. I just dont' see what's wrong, I use the exact same logic in another query.
SELECT CASE WHEN LEN(LTRIM(RTRIM(dbo.AttorneyNames.Title))) >0 THEN dbo.AttorneyNames.Title ELSE '' +
CASE WHEN LEN(LTRIM(RTRIM(dbo.AttorneyNames.FirstName))) >0 THEN dbo.AttorneyNames.FirstName ELSE '' +
CASE WHEN LEN(LTRIM(RTRIM(dbo.AttorneyNames.MiddleName))) >0 THEN dbo.AttorneyNames.MiddleName ELSE '' +
CASE WHEN LEN(LTRIM(RTRIM(dbo.AttorneyNames.MiddleName2))) >0 THEN dbo.AttorneyNames.MiddleName2 ELSE '' +
CASE WHEN LEN(LTRIM(RTRIM(dbo.AttorneyNames.LastName))) >0 THEN dbo.AttorneyNames.LastName ELSE '' +
CASE WHEN LEN(LTRIM(RTRIM(dbo.AttorneyNames.Suffix))) >0 THEN ', '+ (CASE WHEN dbo.AttorneyNames.Suffix IN ('Esq','Esq.','ESQ','ESQ.') THEN 'Esquire' ELSE dbo.AttorneyNames.Suffix END) ELSE '' END, dbo.AttorneyNames.phonnumber FROM dbo.AttorneyNames
This strings together a Name for a report rather than adding each field to the report so that I can eliminate blank spaces and such in the report.
I think it's a syntax error somewhere I just can't see it, and of course SQL isn't being very helpful with the error messages.
Thanks in advance.
March 30, 2006 at 9:40 am
Here ya go:
SELECT
CASE
WHEN
LEN(LTRIM(RTRIM(dbo.AttorneyNames.Title))) >0 THEN dbo.AttorneyNames.Title
ELSE
''
END
+
CASE
WHEN
LEN(LTRIM(RTRIM(dbo.AttorneyNames.FirstName))) >0 THEN dbo.AttorneyNames.FirstName
ELSE
''
END
+
CASE
WHEN
LEN(LTRIM(RTRIM(dbo.AttorneyNames.MiddleName))) >0 THEN dbo.AttorneyNames.MiddleName
ELSE
''
END
+
CASE
WHEN
LEN(LTRIM(RTRIM(dbo.AttorneyNames.MiddleName2))) >0 THEN dbo.AttorneyNames.MiddleName2
ELSE
''
END
+
CASE
WHEN
LEN(LTRIM(RTRIM(dbo.AttorneyNames.LastName))) >0 THEN dbo.AttorneyNames.LastName
ELSE
''
END
+
CASE
WHEN
LEN(LTRIM(RTRIM(dbo.AttorneyNames.Suffix))) >0 THEN ', ' +
(
CASE
WHEN dbo.AttorneyNames.Suffix IN ('Esq','Esq.','ESQ','ESQ.') THEN 'Esquire'
ELSE dbo.AttorneyNames.Suffix
END
)
ELSE
''
END
,
dbo.AttorneyNames.phonnumber
FROM
dbo.AttorneyNames
March 30, 2006 at 9:48 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply