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

x
?
Solved

SQL query help

Posted on 2011-03-15
3
Medium Priority
?
218 Views
Last Modified: 2012-05-11
Hi Experts,

I need to create a dataset from a query to show me what the last non blank value in 5 fields within a record.    The fields are TID1, TID2, TID3, TID4 and TID5.

They will be populated in this way - either just the the first one, the first 2, first 3, first 4 or all 5.   There are never any gaps so I so only need to return the last non-blank value.

For example.

TID1 TID2 TID3 TID4 TID5
14    232   244                          (this sholod return 244)
13    231                                   (this should return 231)
12    238  238   256                  (this should return 256)
14    237  250   279  410          (this should return 410)


Hope I have explained this well enough.

Thanks
Jon
0
Comment
Question by:JonYen
[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
3 Comments
 

Expert Comment

by:JoelDev
ID: 35143604
On SQL Server, using T-SQL that would look like this:

SELECT COALESCE(TID5, TID4, TID3, TID2, TID1) AS Field
FROM SYS_Customer

Open in new window


Using ANSI SQL it would look more like this.
SELECT CASE WHEN TID5IS NOT NULL THEN 
				TID5
			WHEN TID4 IS NOT NULL THEN
				TID4
			WHEN TID43IS NOT NULL THEN
				TID3
			WHEN TID2 IS NOT NULL THEN
				TID2
			ELSE
				TID1
			END AS Field
FROM SYS_Customer

Open in new window


The basics of this is your are looking at the fields in reverse until you find one that isn't NULL. If you're comparing against an empty string, just change "IS NOT NULL" to compare for any empty string: "<> ''"
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 2000 total points
ID: 35143815
adding to what JoleDev have prepared, you must also consider that empty field sometimes contain spaces instead of NULL therefore if the last field contain spaces instead of NULL, it will take the field that contain spaces as the last field. i would use LEN() function to test the spaces field. you can add ltrim and rtrim too.

i'm suggesting the following SQL:

SELECT
CASE WHEN TID1 IS NOT NULL AND LEN(TID1) > 0 THEN TID1
WHEN TID2 IS NOT NULL AND LEN(TID2) > 0 THEN TID2
WHEN TID3 IS NOT NULL AND LEN(TID3) > 0 THEN TID3
WHEN TID4 IS NOT NULL AND LEN(TID4) > 0 THEN TID4
WHEN TID5 IS NOT NULL AND LEN(TID5) > 0 THEN TID5
ELSE 'No Value Found'  END AS TIDS
FROM TABLENAME

0
 
LVL 2

Expert Comment

by:MTillett
ID: 35146520
You say "non blank".  Are we to assume that the columns are character datatype?  Are they nullable?
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

688 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