OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Rowcount (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Rowcount [message #639644]

Mon, 13 July 2015 10:06

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

Hi friends,
I need to count no rows for below query for this, i used rowid as a column in the query but it is not getting please help me.......i need to split the records as 65532 as one sheet..........

SELECT a.inventory_item_id
,a.organization_id
,a.last_update_date
,a.last_updated_by
,a.creation_date
from mtl_system_items_b a, mtl_system_items_b b
where a.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID

Report message to a moderator

Re: Rowcount [message #639647 is a reply to message #639644]

Mon, 13 July 2015 10:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

what is expected & desired result?

Report message to a moderator

Re: Rowcount [message #639648 is a reply to message #639647]

Mon, 13 July 2015 10:18

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

Actually when i execute this query i can count of more than 7 lacks records in my toad..but my requirement is in such a way that i need to split 65532 records in one group and so on..(i am using this query in xml)..
Please help me in any possible scenarios..Thanq

[Updated on: Mon, 13 July 2015 10:19]

Report message to a moderator

Re: Rowcount [message #639649 is a reply to message #639648]

Mon, 13 July 2015 10:42

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

thiruvari wrote on Mon, 13 July 2015 20:48

7 lacks records

What is 7 lacks?

Quote:

Please help me in any possible scenarios..Thanq

Please help us to understand what you actually want.

[Updated on: Mon, 13 July 2015 10:44]

Report message to a moderator

Re: Rowcount [message #639650 is a reply to message #639649]

Mon, 13 July 2015 10:53

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

Sorry for that...
i mean to say that, when i execute the above query in toad and trying to count records through toad functionality "record count" result is 765360..

Report message to a moderator

Re: Rowcount [message #639651 is a reply to message #639650]

Mon, 13 July 2015 10:58

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

i just used ROWNUM as a column for the above query and i can view result 1,2,3,......so on for ROWNUM column...but i need to make one group for 65532 rows as one group and make use of this in xml...

Report message to a moderator

Re: Rowcount [message #639652 is a reply to message #639648]

Mon, 13 July 2015 11:08

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Can one retrieve only rows X to Y from a table?

Report message to a moderator

Re: Rowcount [message #639663 is a reply to message #639652]

Mon, 13 July 2015 15:40

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This question seems to be related with this one.

It seems that the OP uses Excel 2007 which supports 1,048,576 rows per sheet, so splitting the result into chunks of 65,536 rows doesn't make sense in that context.

Report message to a moderator

Re: Rowcount [message #639687 is a reply to message #639663]

Tue, 14 July 2015 04:40

arudesai
Messages: 7
Registered: July 2015

Junior Member

Try using ntile function, it splits rows in no of batch you want

SELECT ntile(12) over(order by INVENTORY_ITEM_ID ) batch, a.inventory_item_id
,a.organization_id
,a.last_update_date
,a.last_updated_by
,a.creation_date
from mtl_system_items_b a, mtl_system_items_b b
where a.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID

in your case put batch count as 12(765360\65,536)

hope this works

Cheers
Aru

Report message to a moderator

Re: Rowcount [message #639688 is a reply to message #639687]

Tue, 14 July 2015 04:48

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum and thanks for your solution.
Please read How to use [code] tags and make your code easier to read.
You can use SQL Formatter to help you format your query.

Report message to a moderator

Previous Topic:	Select Query based on closed / reopened flag
Next Topic:	Disabling unique constraints and data warehousing query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 09:12:32 CDT 2024