Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Constructing a SELECT statement using a CASE statement?

Posted on 2014-12-05
10
Medium Priority
?
288 Views
Last Modified: 2014-12-08
I have a column that exists in 3 different tables. Each table could potentially have a value in it at the same time. I just need to return that value from the first occurrence of whichever table contains a value. Here is what I have so far.

SELECT	A.serialNO AS 'S/N'
		,A.main_ID AS 'Test ID'
		,ISNULL(B.dateCreated,D.dateCreated) AS 'Calibration Date'
	        ,CASE WHEN B.station_ID > 0 THEN F.stationName ELSE '' END
		 + CASE WHEN D.station_ID > 0 THEN G.stationName ELSE '' END
		 + CASE WHEN E.station_ID > 0 THEN H.stationName ELSE '' END AS 'Test Station'
		,B.[load] AS 'Capacity'
		,B.endTime AS 'Ending Time'
  FROM  HEADER_Item A LEFT JOIN TEST_HEADER_Load B ON A.main_ID = B.main_ID
		LEFT JOIN TEST_HEADER_Creep D ON A.main_ID = D.main_ID
		LEFT JOIN TEST_HEADER_Repeatability E ON A.main_ID = E.main_ID
		LEFT JOIN LKUP_TestStations F ON B.station_ID = F.station_ID
		LEFT JOIN LKUP_TestStations G ON D.station_ID = G.station_ID
		LEFT JOIN LKUP_TestStations H ON E.station_ID = H.station_ID
 WHERE	A.serialNO = @serialNO

Open in new window


The table "LKUP_TestStations" contains the following columns: "station_ID", "stationName". The Primary Key is the "station_ID" in this table.

The "station_ID" column resides as a Foreign Key in the tables  "TEST_HEADER_Load", "TEST_HEADER_Creep" and "TEST_HEADER_Repeatability".

So, with the statement above, it is returning 3 values from the CASE statement when I only need one. How can I get it to just return the first occurrence of "station_ID".

I hope I've made sense with this.

Thanks!
0
Comment
Question by:BlakeMcKenna
[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
  • 5
  • 3
  • 2
10 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40483605
> I just need to return that value from the first occurrence of whichever table contains a value.

Not entirely getting the table logic here, but if the question is return the first non-zero value between the numeric columns b.StationID, c.StationID, and d.StationID, then something like.
SELECT blah, blah, blah, 
  CASE
    WHEN b.StationID > 0 THEN b.StationId
    WHEN d.StationID > 0 THEN d.StationId
    WHEN e.StationID > 0 THEN e.StationID
    ELSE '' END as StationID
FROM ...

Open in new window

btw I have an article out there on SQL Server CASE Solutions, with a wompload of examples, although it doesn't mention this scenario.
0
 

Author Closing Comment

by:BlakeMcKenna
ID: 40483667
That's what I was looking for! Thanks Jim...
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40483680
Because you are using left joins, that raises the possibility that a null value could be returned for any of the stationName  fields used in your CASE statement. You could replace the entire CASE statement with the COALESCE function:
COALESE(F.stationName, G.stationName, H.stationName)

Open in new window

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:BlakeMcKenna
ID: 40483962
Shaun,

Using your statement, what happens if none of the "stationName" columns are NULL?
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40484986
The COALESCE function returns the first non-null value in the list. If all three values could be null and you do not want null returned, add a fourth value that you would treat as the default value to return, such as an empty string.
0
 

Author Comment

by:BlakeMcKenna
ID: 40486797
I'm running SQL Server 2008 and apparently the COALESE statement isn't available in this version.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40486807
Blake - Yep.  When asking T-SQL questions please tell us the version of SQL you're running, as 2012 has a lot of improvements over 2008/R2 that can make a big difference in the answers we provide.
0
 

Author Comment

by:BlakeMcKenna
ID: 40486820
Will do...thanks!
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40486868
The COALESCE function exists, but the code I provided contained a misspelling. It did not have the second C.
0
 

Author Comment

by:BlakeMcKenna
ID: 40486988
Shaun,

That worked. Funny though, the intelisense didn't prompt me when I started typing in the word. It only showed up as a reserved word when I finished typing the whole word. But it worked!

Thanks!
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

610 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