Simple Query [message #607026] |
Sat, 01 February 2014 11:36 |
|
Shazin
Messages: 10 Registered: October 2011 Location: India
|
Junior Member |
|
|
Hi Guys,
We have a simple query as below:
Select pad.* from rpa_import_nearbyprocessing nearby, rpa_import_pad_latest pad where nearby.iss_carrier = pad.iss_carrier and nearby.ticket = pad.ticket_no and nearby.date_of_issue = pad.date_of_issue and rownum < 2 and pad.bypass = 0
and exists (Select Org, C1, 1 from rpa_import_nearbyprocessing nearby where nearby.iss_carrier = pad.iss_carrier and nearby.ticket = pad.ticket_no and nearby.date_of_issue = pad.date_of_issue)
When in the select we give pad.* it gives a different result and when we give nearby.* it gives a different select. Please can you guys advise the reason for this.
Cheers,
Shax
|
|
|
|
|
|
Re: Simple Query [message #607031 is a reply to message #607029] |
Sat, 01 February 2014 12:08 |
|
Shazin
Messages: 10 Registered: October 2011 Location: India
|
Junior Member |
|
|
Dear Manu,
Thanks a ton for sharing your development experience but my question was not so simple. This shows how much one can attain experience by posting unncessary posts in the forum and accumulating the number 385
When I run the below code:
SELECT nearby.*, pad.*
--org,c1,1
FROM rpa_import_nearbyprocessing nearby, rpa_import_pad_latest pad
WHERE nearby.iss_carrier = pad.iss_carrier
AND nearby.ticket = pad.ticket_no
AND nearby.date_of_issue = pad.date_of_issue
AND ROWNUM < 2
AND pad.bypass = 0
AND EXISTS (
SELECT org, c1, 1
FROM rpa_import_nearbyprocessing nearby
WHERE nearby.iss_carrier = pad.iss_carrier
AND nearby.ticket = pad.ticket_no
AND nearby.date_of_issue = pad.date_of_issue)
I get a different Org, C1 values but when I run the below:
SELECT --nearby.*, pad.*
org,c1,1
FROM rpa_import_nearbyprocessing nearby, rpa_import_pad_latest pad
WHERE nearby.iss_carrier = pad.iss_carrier
AND nearby.ticket = pad.ticket_no
AND nearby.date_of_issue = pad.date_of_issue
AND ROWNUM < 2
AND pad.bypass = 0
AND EXISTS (
SELECT org, c1, 1
FROM rpa_import_nearbyprocessing nearby
WHERE nearby.iss_carrier = pad.iss_carrier
AND nearby.ticket = pad.ticket_no
AND nearby.date_of_issue = pad.date_of_issue)
I get a different output. This is to do something with the subquery part of the code and not just a case of aliasing. As oracle is handling them differntly and truley I dont have any idea about it.
You are correct Oracle is not my piece of cake but I am an expert in what I do and there is nothing in that for me to be boasting around. Please let the knowledgeable people in the forum answer this.
Cheers,
Shax
|
|
|
|
|
Re: Simple Query [message #607035 is a reply to message #607034] |
Sat, 01 February 2014 12:36 |
manubatham20
Messages: 566 Registered: September 2010 Location: Seattle, WA, USA
|
Senior Member |
|
|
Shazin,
You are just answering the questions you want, and not answering other questions.
post complete ddl statements to avoid any confusion.
I am suspicious on
Use order by on unique/pk and check the results again.
Manu
[Updated on: Sat, 01 February 2014 12:37] Report message to a moderator
|
|
|
Re: Simple Query [message #607036 is a reply to message #607031] |
Sat, 01 February 2014 12:42 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Shax, your posts are a bit confusing. When you say "a different result" are you referring to the rows that are selected or to the columns that are projected?
|
|
|
|
|
|
|
Re: Simple Query [message #607041 is a reply to message #607040] |
Sat, 01 February 2014 12:55 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Furthermore, I think the plan will show a SEMI JOIN which is another non-deterministic operation.
ps - like the avatar, BS. I think I may have seen something similar on the OTN forums).
|
|
|
|
|
Re: Simple Query [message #607049 is a reply to message #607037] |
Sat, 01 February 2014 14:18 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Shazin wrote on Sat, 01 February 2014 18:44Sorry Mate, this is not related to Rownum functionality I disagree (you are taking a random row from the resultset, I'm betting that that is EXACTLY the problem).
Quote:as have already tested that. Excellent, glad to be proved wrong, please post a copy and paste of your session that carries out those tests.
Quote: Please believe me the answer would be straightforward and will be related to Oracle Architecture as to how Oracle reads data. Really, do tell. You already seem to have the answer, I'm wondering why you are asking the questions?
Quote: Believe me DDL and DML would not help. Again, I disagree. But you must know better eh? Being so experienced with Oracle development places you in a perfect position to tell us what is and is not required to answer your question.
[Updated on: Sat, 01 February 2014 14:19] Report message to a moderator
|
|
|
Re: Simple Query [message #607053 is a reply to message #607049] |
Sat, 01 February 2014 14:26 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
> this is not related to Rownum functionality as have already tested that.
you can be sure & wrong at the same time.
|
|
|