OraFAQ Forum: Reports & Discoverer » showing the number sequence of displayed data

Home » Developer & Programmer » Reports & Discoverer » showing the number sequence of displayed data

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

showing the number sequence of displayed data [message #480126]

Thu, 21 October 2010 05:43

Amar_bu
Messages: 32
Registered: July 2010
Location: riyadh

Member

Hi
I have query in that query i should display the number of row.I have tried to select the rownum but when i use it the data will be duplicated.
I have found something that related to use temprory table, or subquery but all of them will not work becuase the query depends on many tables and if i use temprory table filling the table and then reading from it will take too long time.
Any idea??

[Updated on: Thu, 21 October 2010 05:44]

Report message to a moderator

Re: showing the number sequence of displayed data [message #480134 is a reply to message #480126]

Thu, 21 October 2010 06:34

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

We are not working at your environment..and we don`t know how you are doing it and query you are using.
would you mind to share it ?
sriram

Report message to a moderator

Re: showing the number sequence of displayed data [message #480249 is a reply to message #480134]

Fri, 22 October 2010 02:02

Amar_bu
Messages: 32
Registered: July 2010
Location: riyadh

Member

This is the query

SELECT nvl(sum(I.revenue),i.period, rownum

 from pa_budget_versions             bv,
      pa_proj_fp_options             po,
      XX_PA_BUDGET_TYPES_V           b,
      PA_BUDGET_LINES                I,
      pa_projects_all                pa_all,
     
      
 WHERE bv.wp_version_flag = 'N'
       AND bv.version_type = 'REVENUE' 
       AND bv.budget_status_code = 'B'
       and bv.approved_rev_plan_type_flag= 'Y'
       and bv.CURRENT_FLAG = 'Y'
       and bv.FIN_PLAN_TYPE_ID = b.FIN_PLAN_TYPE_ID
       and b.NAME = 'Approved Budget' 
       AND pa_all.org_id = fnd_profile.VALUE('ORG_ID')
       and bv.budget_version_id=i.budget_version_id
        AND bv.budget_version_id = po.fin_plan_version_id
       AND bv.fin_plan_type_id = po.fin_plan_type_id
       AND bv.project_id = po.project_id
       AND po.fin_plan_option_level_code = 'PLAN_VERSION'
       AND bv.project_id = pa_all.project_id ;
      
group by i.period, to_char(i.period,'MM'),rownum
order by to_char(i.period,'MM')

[Updated on: Fri, 22 October 2010 02:04]

Report message to a moderator

Re: showing the number sequence of displayed data [message #480251 is a reply to message #480249]

Fri, 22 October 2010 02:19

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

Senior Member
Account Moderator

Quote:

i should display the number of row

I have no idea how ROWNUM makes your query to return duplicate records.

Anyway: you could create a summary column; its source should be one of columns returned by a query (by the way, the first one (NVL...) requires an alias), its type should be COUNT.

Report message to a moderator

Re: showing the number sequence of displayed data [message #480258 is a reply to message #480251]

Fri, 22 October 2010 02:43

Amar_bu
Messages: 32
Registered: July 2010
Location: riyadh

Member

yah,but when i count using the summary column i will display the total number of record
but what i want that the output be
1 22000
2 30000
3 40000
like that

Report message to a moderator

Re: showing the number sequence of displayed data [message #480291 is a reply to message #480258]

Fri, 22 October 2010 05:12

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

Senior Member
Account Moderator

You will get what you need, only if you use it the right way.

Yet another option is to include the ROW_NUMBER analytical function into the SELECT statement.

Report message to a moderator

Re: showing the number sequence of displayed data [message #480314 is a reply to message #480291]

Fri, 22 October 2010 07:05

Amar_bu
Messages: 32
Registered: July 2010
Location: riyadh

Member

Thanks

Report message to a moderator

Re: showing the number sequence of displayed data [message #481543 is a reply to message #480314]

Thu, 04 November 2010 01:55

mehediu
Messages: 46
Registered: February 2010
Location: Dhaka

Member

take your summery column in your required group and use it in that group frequency. you will get your expected result

thanks.

Report message to a moderator

Re: showing the number sequence of displayed data [message #482424 is a reply to message #481543]

Thu, 11 November 2010 06:40

fraction
Messages: 29
Registered: November 2010
Location: Pakistan

Junior Member

in summary colum. use count and reset it on sum(i.revenue)

Report message to a moderator

Re: showing the number sequence of displayed data [message #482449 is a reply to message #482424]

Thu, 11 November 2010 11:40

Amar_bu
Messages: 32
Registered: July 2010
Location: riyadh

Member

Thanks for all replay...I have achived what i want
Really thanks for all of your support

Report message to a moderator

Re: showing the number sequence of displayed data [message #482482 is a reply to message #482449]

Thu, 11 November 2010 22:45

fraction
Messages: 29
Registered: November 2010
Location: Pakistan

Junior Member

Please tell us how you did that ?

Report message to a moderator

Re: showing the number sequence of displayed data [message #482682 is a reply to message #482482]

Mon, 15 November 2010 05:27

Amar_bu
Messages: 32
Registered: July 2010
Location: riyadh

Member

HI
sorry for being late in replay. I have used analytical function row number

SELECT nvl(sum(I.revenue),
       row_number() over (order by  to_number(to_char(i.start_date,'MM')) ) as pay_no
 from pa_budget_versions             bv,
      pa_proj_fp_options             po,
      XX_PA_BUDGET_TYPES_V           b,
      PA_BUDGET_LINES                I,
      pa_projects_all                pa_all,
     
      
 WHERE bv.wp_version_flag = 'N'
       AND bv.version_type = 'REVENUE' 
       AND bv.budget_status_code = 'B'
       and bv.approved_rev_plan_type_flag= 'Y'
       and bv.CURRENT_FLAG = 'Y'
       and bv.FIN_PLAN_TYPE_ID = b.FIN_PLAN_TYPE_ID
       and b.NAME = 'Approved Budget' 
       AND pa_all.org_id = fnd_profile.VALUE('ORG_ID')
       and bv.budget_version_id=i.budget_version_id
        AND bv.budget_version_id = po.fin_plan_version_id
       AND bv.fin_plan_type_id = po.fin_plan_type_id
       AND bv.project_id = po.project_id
       AND po.fin_plan_option_level_code = 'PLAN_VERSION'
       AND bv.project_id = pa_all.project_id ;
      
group by i.start_date
order by to_char(i.start_date,'MM')

also, i could use count but analytical function is faster than count summary column

Report message to a moderator

Previous Topic:	MATRIX Report
Next Topic:	10g reports

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 03:27:10 CDT 2024