Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Oracle varchar problem - Difficult

Oracle varchar problem - Difficult

From: Christopher Burke <craznar_at_hotmail.com>
Date: Tue, 06 Feb 2001 13:23:36 GMT
Message-ID: <9040EFD5CCraznar@61.9.128.12>

I have a table - say called 'blob', in it are two fields called 'value' and
'dirty'.

The field value holds an arbitrary string .. dirty holds a 'Y' or an 'N' - which determine if the record has been read and process by another program.

Here is the problem ... how do I do the following :

update blob
  set dirty='Y',value=new_value
  where value <> new_value;

... but do it correctly.

If value is currently '' (blank is a valid value) then it is treated as null, if new_value is '' then it is treated as a null.

That means that I cannot update a '' with a new value, nor can I update a value with a ''.

Why - because "null = xxx" and "null <> xxx" always both return false.

I changed to

update blob
  set dirty='Y',value=new_value
  where NVL(value,'askhda') <> NVL(new_value,'askhda');

But this isn't good either - because the 'value' is arbitrary, and I need to be able to store any value. The above example would be unable to store
'askhda'.

Is there any way around this ....

-- 
---
/* Christopher Burke - Spam Mail to craznar_at_hotmail.com
|* www.craznar.com - International Internet Writing Experiment
\* Real mail to cburke(at)craznar(dot)com
Received on Tue Feb 06 2001 - 07:23:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US