Handling NULL values. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Handling NULL values.

Author

Topic

moorthyvisu
Starting Member

7 Posts

Posted - 2007-10-19 : 11:29:52

Hi,

I would like to know the solution for the below problem.

Actually I have a single Hibernate xml file which has the query on it. And I am accessing the xml file for SQL server 2005 and Oracle 10G. But the query is working in Oracle and not working in SQl Server.

I noticed from the query that handling 'NULL' is the problem.

Findings:
1. The following query is the same in both Oracle and SQL Server, which is causing the Issue.
SELECT *
FROM CLIENT_SKU AS o
WHERE o.OU_ID = 20000000
AND ((o.CLIENT_SKU_START_CODE <= 'wwwww' AND o.CLIENT_SKU_END_CODE >= 'wwwww')
OR (o.CLIENT_SKU_START_CODE <= '' AND o.CLIENT_SKU_END_CODE >= '')
OR (o.CLIENT_SKU_START_CODE >= 'wwwww' AND o.CLIENT_SKU_END_CODE < '')
OR (o.CLIENT_SKU_START_CODE = 'wwwww' AND o.CLIENT_SKU_END_CODE is null))

CAUSE:
Oracle treats NULL and the empty string almost the same making the latter almost an alias for NULL
The following two statements produce identical results:
1. update employee set lastname = ''
2. update employee set lastname = NULL
In our case, the CLIENT_SKU_END_CODE is an empty string which is being checked with an empty string returns False in Oracle where as returns True in Sql Server causing the issue.

It would be great if any one who can give some possible solution to resolve the same.

Thnx

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 11:46:10

NullIf(CLIENT_SKU_END_CODE, '')

will return NULL if CLIENT_SKU_END_CODE is blank (in SQL Server at least)

But I've no idea if that helps at all?!

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-19 : 11:46:57

>> the CLIENT_SKU_END_CODE is an empty string which is being checked with an empty string returns False in Oracle
you mean '' = '' returns false in Oracle and that's what you want?
Sounds odd but I would have thought changing
o.CLIENT_SKU_END_CODE >= ''
too.CLIENT_SKU_END_CODE > ''
would omit the empty strings on both systems.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 12:40:25

CLIENT_SKU_END_CODE char, rather than Varchar, perhaps? (Dunno how that behaves on Oracle, but in SQL Server there is some automatic trimming when comparing to a string constant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources