Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Stored Proc - Performance Enhancement 15 54
SQL Script to Remove Data from Two Joined Tables 1 19
SQL Log size 3 17
Are triggers slow? 7 10
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

839 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