SQL Server 2005: Case Logic in Dynamic SQL

Hi Techies--

I am getting the error:

Incorrect syntax near the keyword 'as'.

...because I am trying to dub a column name within case/end logic.  Does anyone have a suggestion on how to solve this issue?

[begin snippet]

SET @Sql = N'
SELECT
 pat.AssemblyNum as ''assemblyNumber'',
 pat.PatternNum as ''patternNumber'',
 pat.Corner as ''cornerNumber'',
 class.PatClassTypeName as ''className'',
 case
  when pat.MatchTypeID = ''101'' then pat.Width/2 as ''repeatWidth''
  else pat.Width as ''repeatWidth''
 end ,
 pat.Length as ''repeatLength'',
 match.MatchTypeName as ''matchType'',
 pat.CollectionID as ''collectionID'',
 coll.CollectionName as ''collectionName''
FROM PatternClassType class, Collections coll, Patterns pat
    LEFT OUTER JOIN MatchType match
       ON pat.matchtypeid = match.matchtypeid

[end snippet]

Open in new window

Paula DiTalloIntegration developerAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
Your "AS" has to be on the entire case statemented column, not on each possible value.  So the correct syntax would be...


SET @Sql = N'
SELECT
 pat.AssemblyNum as ''assemblyNumber'',
 pat.PatternNum as ''patternNumber'',
 pat.Corner as ''cornerNumber'',
 class.PatClassTypeName as ''className'',
 case
  when pat.MatchTypeID = ''101'' then pat.Width/2
  else pat.Width
 end as ''repeatWidth'',
 pat.Length as ''repeatLength'',
 match.MatchTypeName as ''matchType'',
 pat.CollectionID as ''collectionID'',
 coll.CollectionName as ''collectionName''
FROM PatternClassType class, Collections coll, Patterns pat
    LEFT OUTER JOIN MatchType match
       ON pat.matchtypeid = match.matchtypeid

0
 
Paula DiTalloIntegration developerAuthor Commented:
brilliantly done!:-)
0
All Courses

From novice to tech pro — start learning today.