Re: [Q] Porting SQL to Oracle

If you do

select dump( x ), x, dump( z )

you can see exactly what is in the field, more than likely there is nothing (i.e. the field is null).

Null is a religous experience, so don't even try going there! Null is 'undefined' and guess what, Null does not equal itself (this seems silly to me, oops, here come the flames, since undefined is certainly the same as undefined, but religion sometimes doesn't make sense from the outside.

To check for this you can, as you say, do the trim thing, you can also wrap the right side field in nvl, but I don't know if other SQLs support that either.

You could write your own Stored Procedure function (that seems counter intuitive as a name, eh wot) that would figure it out for you and for each DB product you would do what is needed.

"Daniel Zuppinger" <zuppinger_at_the_infobrain_at_infobREMOVE_CAPITAL_rain.com> wrote in message news:3ee6e19e$0$255$4d4ef98e_at_read.news.ch.uu.net...
> Hi,
>
> We have the following SQL Statement wich must run against a table on a
> Oracle DB:
>
> select x,x,z from tableA where x = '';
>
> The field x is defined as char 40 .It looks like the field contains 40
> blanks. The SQL statement does NOT return these rows as expected. We have
> found that the syntax ' where trim(x) is null' works. But this is not
> acceptable for us, since we need to support several database systems.
>
> Now the question: Can we force oracle to accept the normal sql statement
as
> shown above for our application ? Maybe a environment variable or similar
?
>
> Thanks Daniel
>
>
Received on Wed Jun 11 2003 - 06:48:33 CDT