Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Constructing a SELECT statement using a CASE statement?

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
BlakeMcKenna
Asked:
BlakeMcKenna
  • 5
  • 3
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> 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
 
BlakeMcKennaAuthor Commented:
That's what I was looking for! Thanks Jim...
0
 
Shaun KlineLead Software EngineerCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BlakeMcKennaAuthor Commented:
Shaun,

Using your statement, what happens if none of the "stationName" columns are NULL?
0
 
Shaun KlineLead Software EngineerCommented:
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
 
BlakeMcKennaAuthor Commented:
I'm running SQL Server 2008 and apparently the COALESE statement isn't available in this version.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
BlakeMcKennaAuthor Commented:
Will do...thanks!
0
 
Shaun KlineLead Software EngineerCommented:
The COALESCE function exists, but the code I provided contained a misspelling. It did not have the second C.
0
 
BlakeMcKennaAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now