Home » Developer & Programmer » Reports & Discoverer » How to fetch data based on the given criteria..... (Oracle11g, 10.0.2, AIX/Windows)
How to fetch data based on the given criteria..... [message #565245] Sat, 01 September 2012 05:16 Go to next message
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.
Re: How to fetch data based on the given criteria..... [message #565247 is a reply to message #565245] Sat, 01 September 2012 07:12 Go to previous message
John Watson
Messages: 4374
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.

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


Current Time: Wed Jul 23 22:09:20 CDT 2014

Total time taken to generate the page: 0.11382 seconds