Solved

Constructing a SELECT statement using a CASE statement?

Posted on 2014-12-05
10
203 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 65

Accepted Solution

by:
Jim Horn earned 500 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 26

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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:BlakeMcKenna
ID: 40483962
Shaun,

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

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 65

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 26

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

726 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