• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 137
  • Last Modified:

Help With Query

Hi,

I have table like this:

char_id
object_id
char_value
number_value
date_value
blob_value


The rules to this are per row only 1 out of:
char_value
number_value
date_value
blob_value

will ever be populated. There will never be circumstances where all 4 are null

Can a select statement (or function) be created that will only ever return the not null value?

At the moment I’m returning 4 columns, 3 of which are blank.

TIA
0
andyw27
Asked:
andyw27
1 Solution
 
Ess KayEntrapenuerCommented:
give an example of what u want and what u get
0
 
Ess KayEntrapenuerCommented:
Something like this:



SELECT "char_value" AS NAME, char_value AS Value FROM yOURtaBLE where char_value  isnot NULL
UNION
SELECT "date_value" AS NAME, date_value  AS Value FROM yOURtaBLE where date_value isnot NULL
UNION
SELECT "number_value" AS NAME, number_value AS Value FROM yOURtaBLE where number_value isnot NULL
UNION
SELECT "blob_value" AS NAME, blob_value AS Value FROM yOURtaBLE where blob_value isnot NULL
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Can a select statement (or function) be created that will only ever return the not null value?
Sure.  

Something like this if we're talking 'all columns must be non-NULL..
SELECT yada, yada, yada
FROM your_table
WHERE (char_value IS NOT NULL AND number_value IS NOT NULL AND date_value IS NOT NULL AND blob_value IS NOT NULL)

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Ess KayEntrapenuerCommented:
Sorry, in my comment IS NOT NULL should have spaces between them


--enjoy
0
 
Brian CroweCommented:
SELECT CASE
   WHEN char_value IS NOT NULL THEN 'char_value'
   WHEN number_value IS NOT NULL THEN 'number_value'
   WHEN date_value IS NOT NULL THEN 'date_value'
   WHEN blob_value IS NOT NULL THEN 'blob_value'
   END AS [PopulatedValue],
COALESCE(char_value, number_value, date_value, blob_value) AS [Value]
FROM myTable
0
 
Scott PletcherSenior DBACommented:
SELECT
    COALESCE(char_value, CAST(number_value AS varchar(30)), CONVERT(varchar(30), date_value, 120), CAST(blob_value AS varchar(1000))) AS Value
FROM ...
WHERE ...
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
So .. how's it going?  Lots of good comments here..
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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now