Re: Oracle varchar problem - Difficult

In article <9040EFD5CCraznar_at_61.9.128.12>,   craznar_at_hotmail.com (Christopher Burke) wrote:
> 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.

update blob
set dirty = 'Y', value = new_value
where not(value = new_value)

In case when either value or new_value is null, result of value = new_value is false, so this case is covered too.

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

Sent via Deja.com
http://www.deja.com/ Received on Tue Feb 06 2001 - 09:46:52 CST