Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-09
4
Medium Priority
?
325 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:saabStory
  • 2
4 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 36513251
When you run the query what do you get?
0
 

Author Comment

by:saabStory
ID: 36513504
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 36514810
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
 

Author Closing Comment

by:saabStory
ID: 36519782
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question