Re: selecting colunm with a space ''

On Sun, 21 May 2006 21:07:02 -0700, stephanie wrote:

When I submit a sql :

select * from view from column=''

the view is pointing to oracle table with ole db provider for oracle

In mssql2000, the result return rows which containing a space ' '

Hi Stephanie,

That's obviously wrong. After all, you were requesting rows with the
column equal to the empty string ('').

My suspicion for the reason of this error is that it's related to a
short-coming in Oracle. I'm not an Oracle expert, but I've heard that
Oracle uses the empty string to represent NULL. If you try to store an
empty string, it pads it with a single space, to make sure that it's not
interpreted as NULL. Of course, this means that it's now interpreted as
a single space, which is wrong as well.

If you check the execution plan, I expect that you'll see that the
filtering is done remotely (i.e. SQL Server asks Oracle to return only
rows with column equal to '').


However, in mssql2005, the result return no rows .

My guess has to be that the execution plan has changed. SQL Server now
asks Oracle for all rows and does the filtering on "column = ''" itself.
Or course, due to Oracle's extra space no row will match this.


Any advise on it?

What do you want? The SQL Server 2000 behaviour or the SQL Server 2005
behaviour? In the latter case, try changing the query to

SELECT *
FROM view
WHERE column = ' ' -- Single space instead of empty string

Another alternative is to look into OPENQUERY. That way, *you* decide if
the filtering is done by SQL Server or by Oracle.

--
Hugo Kornelis, SQL Server MVP
.