Need a little more help with a case statement in a union

I got some great help yesterday from zvytas but made my example too simple and it turned out to be harder for me to extend the answer than I thought.  

What I'm doing is writing some HTML around the sql results for output on a web page.  I need to have a case statement to check a course number - if it's 600 then I output just the class number.  If it's anything else but 600, I need to write an html link around that course number.

So, to illustrate, what I need for output is:

100 Level Courses
======================
Class 1
Class 2


200 Level Courses
======================
Class 1
Class 2


600 Level Courses
======================
Class 1
Class 2

In the code below, I've added the fields I really need to the solution I got yesterday but I get an error no matter how I represent those fields - getting back 'invalid column name' in Management Studio. Schema for both tables included as well.

Thank you very much in advance for helping sort this out.

schema:
tbl_AdultEd_Classes
=================================================
classID		int (pk)
groupNum	int
classNum	int
classVariant	varchar(10)
className	varchar(500)
classDisplay	bit

tbl_AdultEd_ClassGroups
=================================================
groupID		int
groupNum	int
groupName	nvarchar(MAX)
groupDisplay	bit

CURRENT QUERY CODE;
=================================================
SELECT CASE WHEN classId <> 0 AND groupNum = 600 THEN classNum+':  '+className ELSE Description  END Description
FROM
(
SELECT g.groupNum, 0 AS classId, '<div style="font-weight:bold;">'+g.groupName+'</div>' AS Description
FROM tbl_AdultEd_ClassGroups g where g.groupDisplay=1
UNION ALL
SELECT c.groupNum, c.classID, '<div class="fontStyle3" style="margin-left:5px;"><a href="https://gsusa.ebiz.uapps.net/vp/SearchResults.aspx?Search='+convert(varchar,c.classNum)+(c.classVariant)+'&pid=4" target="_blank">'+convert(varchar,c.classNum)+':  '+c.className+'</a></div>' AS Description 
FROM tbl_AdultEd_Classes c 
where c.classDisplay=1 
) sub
ORDER BY groupNum, classId

Open in new window

saabStoryAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
You are referring to className in the outerquery, but it is not in the select list in the inner query.  In order to use className you will have to do something like this:
SELECT  CASE WHEN classId <> 0
                  AND groupNum = 600 THEN classNum + ':  ' + className
             ELSE Description
        END Description
FROM    (SELECT g.groupNum,
                0 AS classId,
                '' className,       -- Add this
                '<div style="font-weight:bold;">' + g.groupName + '</div>' AS Description
         FROM   tbl_AdultEd_ClassGroups g
         WHERE  g.groupDisplay = 1
         UNION ALL
         SELECT c.groupNum,
                c.classID,
                c.className className,       -- Add this
                '<div class="fontStyle3" style="margin-left:5px;"><a href="https://gsusa.ebiz.uapps.net/vp/SearchResults.aspx?Search='
                + CONVERT(varchar, c.classNum) + (c.classVariant) + '&pid=4" target="_blank">' + CONVERT(varchar, c.classNum) + ':  ' + c.className
                + '</a></div>' AS Description
         FROM   tbl_AdultEd_Classes c
         WHERE  c.classDisplay = 1
        ) sub
ORDER BY groupNum,
        classId

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
When you run the query what do you get?
0
 
saabStoryAuthor Commented:
When I run this:

SELECT CASE WHEN classId <> 0 AND groupNum = 600 THEN convert(varchar,classNum)+':  '+className ELSE Description  END Description
FROM
(
SELECT g.groupNum, 0 AS classId, g.groupName AS Description
FROM tbl_AdultEd_ClassGroups g where g.groupDisplay=1
UNION ALL
SELECT c.groupNum, c.classID, convert(varchar,c.classNum)+':  '+c.className AS Description
FROM tbl_AdultEd_Classes c
where c.classDisplay=1
) sub
ORDER BY groupNum, classId


I get:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'classNum'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'className'.
0
 
saabStoryAuthor Commented:
Thank you sir - that's what  I needed.  I knew I wasn't referencing those two column correctly but had no idea what to do to fix it.

Many thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.