Check for Null on select?

[ThatRickGuy]

Hi guys, another simple question. I'm pulling data from a table and there are two fields I'm interested in. One contains a value, and the other contains an override value. What I am trying to do is to check the override value for Null, if it is empty, use the default value, if it has a value us it.

In SQL Server, it would just be:

SELECT
 (field list...),
 CASE
  WHEN OVERRIDE_VALUE IS NULL THEN
    DEFAULT_VALUE
  ELSE
    OVERRIDE_VALUE
 END  FieldAlias
FROM
 (Table list...)

Any pointers?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ThatRickGuy]

Thought I had it for a moment. I ran into the {fn ifnull(value1, value2)} method. But it appears as if I was wrong. The field I am checking appears to be full of zero length strings or something. I tried:

{fn ifnull({fn rtrim(Field1)}, Field2)} FieldAlias

But still no luck.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ThatRickGuy]

This system has the amazing ability to thwart me at every turn.

I figured out I could use NullIf to pull a null value from the empty field, but I get "*** Error: Function not supported (NullIf)"

Any other ideas?

-Rick


VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ThatRickGuy]

Ugly and not especially fast I wound up making two similar SQL statments and then UNION ALL to get the data together.

The first query pulled the standard field aliased as the standard field name. In the where clause any records with an override value were excluded.

The second query pulled the over ride field aliased as the standard field name. In the where clause any records with out an override value where excluded.

Unioning the two queries together got the results that I needed.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[mberni]

try

select nvl(field, <defaultvalue>) from sometable

so if field is NULL you will get the defaultvalue else the field-value.

 

[mberni]

depending on the number of records in your tables you could try the following:

create temp table myresults (fld1 integer, fld2 integer);

insert into myresults
select t2.fld1, t1.fld1
from t2, outer t1
where t2.fld1 = t1.fld1;

select count(*) from myresults where fld2 is NULL;