SunQuest
           Oracle Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesOracle Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!
  #1  
Old May 10th, 2004, 12:49 PM
jj_oracle jj_oracle is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2004
Posts: 2 jj_oracle User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question ORroneous Behaviour in SQL

The first 2 queries do not behave identically while the remaining 2 do when we input the following values for input parameters:

&FIR=2
&SEC=4

why is it so ? the culprit seems the OR condition which seems to behave in a strange manner on doing a comparison with a SELECT ( ie. when a cursor comes into picture )

could you please tell me why ????

-----------------------------------------------


select 'SOMETHING' from dual where
(&FIR = 1 OR &FIR=( SELECT 2 FROM DUAL ))
AND
(&SEC = 3 )

------------------------------------------------

select 'SOMETHING' from dual where
(&FIR = 1 OR &FIR= 2 )
AND
(&SEC = 3 )


----------------------------------------------------

select 'SOMETHING' from dual where
( &FIR=( SELECT 2 FROM DUAL ))
AND
(&SEC = 3 )

------------------------------------------------

select 'SOMETHING' from dual where
( &FIR= 2 )
AND
(&SEC = 3 )

--------------------

This was checked in the SQL environment provided by PL/SQL Developer for Oracle 9i

Last edited by jj_oracle : May 10th, 2004 at 12:52 PM. Reason: wish to mention the database with which this was checked

Reply With Quote
  #2  
Old May 10th, 2004, 02:19 PM
shafique shafique is offline
Senior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Canada
Posts: 305 shafique User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 45 m 20 sec
Reputation Power: 5
Suppose to produce the same result. Actually what is you first two queries are doing. Print the string 'Something' When &FIR either One (1) or two (2) but &SEC must be equal to three (3), second query is produce the same result as well. But the last two queries print the string 'Something' when &FIR must be equal to 2 and &SEC equal to three (3).

Reply With Quote
  #3  
Old May 12th, 2004, 11:12 AM
jj_oracle jj_oracle is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2004
Posts: 2 jj_oracle User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Quote:
Originally Posted by shafique
Suppose to produce the same result. Actually what is you first two queries are doing. Print the string 'Something' When &FIR either One (1) or two (2) but &SEC must be equal to three (3), second query is produce the same result as well. But the last two queries print the string 'Something' when &FIR must be equal to 2 and &SEC equal to three (3).

I ran the queries in Oracle 9i - PL/SQL Developer environment:

One would assume that the laws of Boolean Algebra would hold and that if the expression succeeding the AND part were false, nothing would be printed

however this happens only in the second case and not the first:

if I input parameter values as &FIR=2 , &SEC=4 the first query returns the string 'SOMETHING' while the second query does not retrieve anything, and neither does the third or the fourth, for these values

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesOracle Development > ORroneous Behaviour in SQL


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway