asked on

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

Ess Kay

give an example of what u want and what u get

Ess Kay

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

Jim Horn

>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

Ess Kay

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

--enjoy

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

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 ...

Jim Horn

So .. how's it going? Lots of good comments here..