Solved

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

Posted on 2011-09-09
4
313 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 40

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 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now