Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-09
4
Medium Priority
?
323 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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 to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 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