Query in Oracle & SQl Server - 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

Other Forums

Other Topics

Query in Oracle & SQl Server

Author

Topic

moorthyvisu
Starting Member

7 Posts

Posted - 2007-11-01 : 15:01:44

Hi All,

I need the basic information about how the query is working in Oracle & SQl Server.

Sicne I am getting different values when I run the query. I have a table called 'Client_Master' in both the DB
(ie., Oracle & SQL). I have inserted a record with the below values for Client_Master table. (both Oracle & SQl).

Inserted Values:
----------------
Start_Code = 90
End_code = ''

The query is :

SELECT * FROM CLIENT_MASTER
WHERE ID = '200'
AND ((START_CODE <= '102' AND END_CODE >= '102')
OR (START_CODE <= '200' AND END_CODE >= '200')
OR (START_CODE >= '102' AND END_CODE < '200')
OR (START_CODE = '102' AND END_CODE is null))

Oracle result : No rows return.
SQl Server : one row returned.

Data type for start_code & end_code are 'NVARCHAR2 (40)'.

I would highly appreciate if anyone can explain why is this and any suggestion like how to handle this in both the database.

The subject of this query is the values which is entered by the user should not be overlapped. But I dont how to handle
this if it is alphanumeric.

Thnx,

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-01 : 15:11:31

You might want to read this

http://weblogs.sqlteam.com/brettk/archive/2005/01/05/3943.aspx

And the subsequent links

It talks about those data type in Oracle

BUT!

You might want to provide sample data to show us what you mean. There are subtle (and not so subtle) differences between the two platforms

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 15:11:41

"AND END_CODE is null"

I expect that has the potential to be interpreted differently depending on Server/Database settings. It might match a blank-string, for example.

What are the values for the single row you get from SQL Server (in the columns being tested)? That will probably shed some light on the problem.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-01 : 15:14:42

[Guiness Commercial]
BRILLIANT
[/Guiness Commercial]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

moorthyvisu
Starting Member

7 Posts

Posted - 2007-11-01 : 15:26:19

The values are given in my previous post.

ie., Inserted Values:

Start_Code = 90
End_code = '' (it goes like this from front end).

And one thing is, if I removed the last line and still getting the same output. :(

SELECT * FROM CLIENT_MASTER
WHERE ID = '200'
AND ((START_CODE <= '102' AND END_CODE >= '102')
OR (START_CODE <= '200' AND END_CODE >= '200')
OR (START_CODE >= '102' AND END_CODE < '200')

--OR (START_CODE = '102' AND END_CODE is null)) - I removed this line

Oracle - returning no rows.
SQl Server - One row with start_code = 90 and end_code=''

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 15:31:31

"The values are given in my previous post."

I am not seeing the values from the single row that SQL Server returned.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources