Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

ThatRickGuy (Programmer) (OP)
27 Mar 06 11:40
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:

CODE

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 (Programmer) (OP)
27 Mar 06 11:51
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:

CODE

{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 (Programmer) (OP)
27 Mar 06 13:06
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 (Programmer) (OP)
30 Mar 06 17:46
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 (IS/IT--Management)
2 Jun 06 7:31
try

select nvl(field, <defaultvalue>) from sometable

so if field is NULL you will get the defaultvalue else the field-value.
mberni (IS/IT--Management)
9 Jun 06 4:40
depending on the number of records in your tables you could try the following:

CODE

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;

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close