OraFAQ Forum: Reports & Discoverer » How to fetch data based on the given criteria.....

Home » Developer & Programmer » Reports & Discoverer » How to fetch data based on the given criteria..... (Oracle11g, 10.0.2, AIX/Windows)

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

How to fetch data based on the given criteria..... [message #565245]

Sat, 01 September 2012 05:16

shivakm
Messages: 5
Registered: September 2012
Location: Muscat, Sultanate of Oman

Junior Member

Dear All,

I am a bit new to oracle, Please can some help me.

I need to design a report out of the below data:
1. bprf_no will be my primary field
2. report parameters will be the bill_month & no_of_months

Based on above 2 parameters I need to scan through the data for BILL_MONTH <= '20-Jun-2012' and NO_OF_MONTHS <= 6 the other criteria being the AVG_IND in (1,2).

In brief the criteria will be to pick all BPRF_NO having AVG_ID in (1, 2) consecutively till a break (AVG_IND not in (1, 2) starting from the given BILL_MONTH and going below this period, that is BILL_MONTH <= '30-Jun-2012'.

For the below data, if my parameters are: BILL_MONTH <= '30-Jun-2012' and NO_OF_MONTHS <= 3, only the underscore added data should be picked (as they fulfill the criteria) and the report output will be like:

BPRF_NO BILL_MONTH NO_OF_TIMES
-------------------------------------------------
BP05 30-Jun-2012 6
BP06 30-Jun-2012 6
BP07 30-Jun-2012 6
BP08 30-Jun-2012 6

Here the NO_OF_TIMES is the count, that is no of times the BPRF_NO falls into the above mentioned criteria consecutively starting from the provided BILL_MONTH.

BPRF_NO BILL_MONTH VOID_STATUS AVG_IND
------- ----------- ------------ --------
BP01 30-Jun-12 0 0
BP02 30-Jun-12 0 0
BP03 30-Jun-12 0 1
BP04 30-Jun-12 0 1
_BP05 30-Jun-12 0 1_
_BP06 30-Jun-12 0 2_
_BP07 30-Jun-12 0 2_
_BP08 30-Jun-12 0 2_
BP09 30-Jun-12 0 0
BP10 30-Jun-12 0 0
BP10 30-Jun-12 0 0
BP01 31-May-12 0 0
BP02 31-May-12 0 0
BP03 31-May-12 0 1
BP04 31-May-12 0 1
_BP05 31-May-12 0 1_
_BP06 31-May-12 0 2_
_BP07 31-May-12 0 2_
_BP08 31-May-12 0 2_
BP09 31-May-12 0 0
BP10 31-May-12 0 0
BP01 30-Apr-12 0 0
BP02 30-Apr-12 0 0
BP03 30-Apr-12 0 0
BP04 30-Apr-12 0 0
_BP05 30-Apr-12 0 1_
_BP06 30-Apr-12 0 2_
_BP07 30-Apr-12 0 2_
_BP08 30-Apr-12 0 2_
BP09 30-Apr-12 0 0
BP10 30-Apr-12 0 0
BP01 31-Mar-12 0 0
BP02 31-Mar-12 0 0
BP03 31-Mar-12 0 1
BP04 31-Mar-12 0 1
_BP05 31-Mar-12 0 1_
_BP06 31-Mar-12 0 2_
_BP07 31-Mar-12 0 2_
_BP08 31-Mar-12 0 2_
BP09 31-Mar-12 0 0
BP10 31-Mar-12 0 0
BP01 29-Feb-12 0 0
BP02 29-Feb-12 0 0
BP03 29-Feb-12 0 1
BP04 29-Feb-12 0 1
_BP05 29-Feb-12 0 1_
_BP06 29-Feb-12 0 2_
_BP07 29-Feb-12 0 2_
_BP08 29-Feb-12 0 2_
BP09 29-Feb-12 0 0
BP10 29-Feb-12 0 0
BP01 31-Jan-12 0 0
BP02 31-Jan-12 0 0
BP03 31-Jan-12 0 0
BP04 31-Jan-12 0 0
_BP05 31-Jan-12 0 1_
_BP06 31-Jan-12 0 2_
_BP07 31-Jan-12 0 2_
_BP08 31-Jan-12 0 2_
BP09 31-Jan-12 0 0
BP10 31-Jan-12 0 0

My below query is fetching me wrong data:

----- Query -----
select bprf_no, no_of_month--count(*)
from
(
select a.bprf_no, count(*) no_of_month
from
(
select
bill_month, avg_ind, bill_month, add_months(bill_month, +1) bm_1, bprf_no
from vw_ibs_bill
where bill_month <= '30-Jun-2012'
and avg_ind in (1, 2)
order by bprf_no desc, bill_month desc
) a, vw_ibs_bill b
where a.bprf_no = b.bprf_no
and a.bill_month = b.bill_month
and a.avg_ind in (1, 2)
and bill_month = bm_1
group by a.bprf_no
having count(a.bprf_no) >= '&no_of_month'
)
group by bprf_no, no_of_month
order by 1
/

BPRF_NO BILL_MONTH NO_OF_TIMES
-------------------------------------------------
BP03 30-Jun-2012 4
BP04 30-Jun-2012 4
BP05 30-Jun-2012 6
BP06 30-Jun-2012 6
BP07 30-Jun-2012 6
BP08 30-Jun-2012 6
Here BP03 & BP04 should not come into the listing itself.

Report message to a moderator

Re: How to fetch data based on the given criteria..... [message #565247 is a reply to message #565245]

Sat, 01 September 2012 07:12

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

With regard to your question, you must provide the CREATE TABLE statement and a few INSERT statements, to give some data people can actually see. Second, whenever you copy/paste something you must enclose it in [code] tags, like this

orcl>
orcl> select * from dual;

D
-
X

orcl>

or it is impossible to read. Third, your final output cannot have been generated by your final query. No-one can help unless you provide real information.

Report message to a moderator

Previous Topic:	special characters in oracle report
Next Topic:	how to call report automatically

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 18:51:55 CDT 2024