Home » SQL & PL/SQL » SQL & PL/SQL » Simple Query
Simple Query [message #607026] Sat, 01 February 2014 11:36 Go to next message
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 #607027 is a reply to message #607026] Sat, 01 February 2014 11:38 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Tell me, who given you that query, I can bet you never wrote this yourself.
Re: Simple Query [message #607028 is a reply to message #607027] Sat, 01 February 2014 11:39 Go to previous messageGo to next message
Shazin
Messages: 10
Registered: October 2011
Location: India
Junior Member
Why would have I asked this question if I would have written this query?
Re: Simple Query [message #607029 is a reply to message #607028] Sat, 01 February 2014 11:41 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

If you are not Oracle Developer, then just understand these are two different table aliases (pad and nearby).

When you give nearby, result is from rpa_import_nearbyprocessing table, when you use pad, result is from rpa_import_pad_latest table.
You can you both of them together even.

Manu
Re: Simple Query [message #607031 is a reply to message #607029] Sat, 01 February 2014 12:08 Go to previous messageGo to next message
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 #607033 is a reply to message #607031] Sat, 01 February 2014 12:29 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Shazin,

I am not able to relate your first and second question at all.

First you wrote :
When in the select we give pad.* it gives a different result and when we give nearby.*


Then changed your question, you can provide these details earlier.
Please understand that we are not mindreader and post complete ddl statements to avoid any confusion.

Is Org, C1 values are present in both tables?
Also I will say with confidence, your select list has nothing to do with subquery.

Manu
Re: Simple Query [message #607034 is a reply to message #607033] Sat, 01 February 2014 12:31 Go to previous messageGo to next message
Shazin
Messages: 10
Registered: October 2011
Location: India
Junior Member
Dear Manu,

If Org, C1 would have been present in both the tables then the mentioned query would have failed with the below Oracle error:

Quote:
ORA-00918: column ambiguously defined


Cheers,
Shaxx
Re: Simple Query [message #607035 is a reply to message #607034] Sat, 01 February 2014 12:36 Go to previous messageGo to next message
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

   AND ROWNUM < 2


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 Go to previous messageGo to next message
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 #607037 is a reply to message #607035] Sat, 01 February 2014 12:44 Go to previous messageGo to next message
Shazin
Messages: 10
Registered: October 2011
Location: India
Junior Member
Sorry Mate, this is not related to Rownum functionality as have already tested that. Please believe me the answer would be straightforward and will be related to Oracle Architecture as to how Oracle reads data. Believe me DDL and DML would not help.

Thanks for your help with this.

Cheers,
Shax
Re: Simple Query [message #607038 is a reply to message #607036] Sat, 01 February 2014 12:44 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

John,

As Shax said:
I get a different Org, C1 values


So I think it's values (rows) that are selected.

Manu
Re: Simple Query [message #607039 is a reply to message #607037] Sat, 01 February 2014 12:45 Go to previous messageGo to next message
Shazin
Messages: 10
Registered: October 2011
Location: India
Junior Member
Hi John,

Sorry for the confusion. I meant the rows that are selected.

Cheers,
Shax.
Re: Simple Query [message #607040 is a reply to message #607039] Sat, 01 February 2014 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I meant the rows that are selected.
The rows that are select & returned are "random" or least not predictable or consistent when ROWNUM < 2 is used without any ORDER BY clause.
Re: Simple Query [message #607041 is a reply to message #607040] Sat, 01 February 2014 12:55 Go to previous messageGo to next message
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 #607042 is a reply to message #607041] Sat, 01 February 2014 13:17 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

John,

I don't want to diverse the topic of OP.

Can you PM me a link regarding this?

Quote:
I think the plan will show a SEMI JOIN which is another non-deterministic operation.


It's really surprising for me, and I am curious to read more about non deterministic operations in Oracle and its effect on output.

Manu
Re: Simple Query [message #607046 is a reply to message #607042] Sat, 01 February 2014 13:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>ps - like the avatar, BS. I think I may have seen something similar on the OTN forums).
The hummingbird feeder is less than 10 feet from my laptop & gets regular visits throughout the day.
Re: Simple Query [message #607049 is a reply to message #607037] Sat, 01 February 2014 14:18 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Shazin wrote on Sat, 01 February 2014 18:44
Sorry 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 Go to previous message
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.
Previous Topic: How to find day of the week, starting with Monday
Next Topic: dynamic sql
Goto Forum:
  


Current Time: Fri Mar 29 01:21:05 CDT 2024