Re: [Q] Porting SQL to Oracle

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:bcbtjt$d57$1_at_ctb-nnrp2.saix.net...
> Niall Litchfield wrote:
>
> > No you aren't, you are saying concatenate an UNKNOWN value to the string
> > 'ABC', logically you can't do it.
>
> Well, it is either allowing this:
> 'ABC'||NULL
> or giving this:
> ORA-99999
>
> Which one do you want? :-)

:-( I think on the whole I'd vote for the error message.

> It's a pain to always do a NVL on every single nullable column in case it
> does contain a NULL.

is it more or less of a pain than ensuring that dates get formatted correctly?

>
> > Not only that but it is possible that
> > 'ABC'||NULL gives a different result everytime you run it. NULL isn't
> > NOTHING.
>
> You're sure? Anything you can throw this way (empty beer cans excluded,
full
> ones permitted if they are Bavarian) to show this?

Its the way I look at it. throwing NULL at an operator doesn't make sense at all to me, whether you regard NULL as meaing unknown value, missing data or nothing.

> > If It were I'd be happy to use zero for NULL in numeric/date
> > datatypes.
>
> But zero is a value. NULL is not. NULL means "does not exist". It does not
> even mean an unknown value - that by implication mean that the thing does
> have a value. NULL means "nothing", not some value hiding somewhere.

I think that is the heart of our difference, I can see how looking at it that way would help, but I'd say not existing is not quite the same as NULL. You can't even say whether NULL exists or not, what datatype it has etc etc. To me it is like the tree falling in the words with no-one to hear it. Its an unobserved value. There may or may not be a value there we just can't say. This is all in my mind anyhow.

> Niall, I don't get it. Are you guys disputing that NULL is not a state,
but
> is in fact a value?

I hope I have clarified a bit how I mentally treat NULL, as a state yes, but as a state about which we can say nothing.

Next week perhaps we should move onto counting angels on pins :(

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************