OraFAQ Forum: Reports & Discoverer » Matrix Report Query Problem

Home » Developer & Programmer » Reports & Discoverer » Matrix Report Query Problem

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

Matrix Report Query Problem [message #336787]

Tue, 29 July 2008 00:49

sweetkhaliq
Messages: 200
Registered: April 2006

Senior Member

Dear members
I am using reports 6 and oracle 9i. I am trying to create a matrix report in which i want to show products as rows and dates as columns. Dates should be fixed 7 days any from to dates given by user. I have used two tables prd_plan_m and product_code. I want to show plan_no and plan_qty based on product_code and date. I want to show all the products as rows whether it has plan or not and 7 days columns shows as heading weather in this there is any plan or not.
For this reason i have made this query.

SELECT ALL  p.code,pkg_prod.get_prod_name(p.code)prod_name,pkg_prod.get_prod_qty(p.code)prod_qty,P.PLAN_ID, P.PLAN_DESC, a.dates,
    P.PLAN_QTY, P.UNIT_ID, P.REMARKS
    FROM PRD_PLAN_M P ,(select rownum - 1 + to_date('20-jul-08', 'dd-mon-rrrr') dates
                      from all_objects
                      where rownum < to_date('26-jul-08', 'dd-mon-rrrr') -
                      to_date('20-jul-08', 'dd-mon-rrrr') + 2)a
    where  P.PLAN_DATE(+) = A.DATES
UNION
 SELECT E.P_CODE,E.PROD_NAME,E.PROD_QTY,null,null,null,null,null,null FROM PROD_CODE E

But this query shows duplicates rows. suppose one product_code in prd_plan_m has plan_qty shows one row and the row from product_code table also show the same row which have no plan qty.
Help me in this regard.
I will be very thankful to you .

thanks and regards

Report message to a moderator

Re: Matrix Report Query Problem [message #337110 is a reply to message #336787]

Tue, 29 July 2008 23:12

sweetkhaliq
Messages: 200
Registered: April 2006

Senior Member

I am still waiting for reply. I think moderator and senior members are busy on other groups like forms etc. Waiting for fruitful response.

Thanks And Regards

[Updated on: Tue, 29 July 2008 23:12]

Report message to a moderator

Re: Matrix Report Query Problem [message #337120 is a reply to message #337110]

Wed, 30 July 2008 00:33

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

Senior Member
Account Moderator

Quote:

suppose one product_code in prd_plan_m has plan_qty shows one row and the row from product_code table also show the same row which have no plan qty.

So these rows are not duplicates: one of them has PLAN_QTY, and the other one does not.

Could you create "Matrix with Group" report, in order to move certain fields to the group, while the rest would form the matrix?

As of

Quote:

I think moderator and senior members are ...

you probably know that this is just a forum, where noone owes you answer or provide help of any kind. We all do it in our free time, when we want and IF we want. It is noone's responsibility to answer any question here.

If your job suffers from you being unable to solve the problem, hire the consultant, pay his/her fee and enjoy. Otherwise, on the forum, you get what you've paid for. Just to quote

Anacedent

If you are ever dissatisfied with time, tenor, tone or quality of any response, you are entitled to a full & complete refund.

Report message to a moderator

Re: Matrix Report Query Problem [message #337183 is a reply to message #336787]

Wed, 30 July 2008 02:07

sweetkhaliq
Messages: 200
Registered: April 2006

Senior Member

Thanks LittleFoot
Although i have "Matrix with group" option in mind , may be it works but i want to solve it with query so i have made this query. Now my problem has sloved.

SELECT ALL p.code,pkg_prod.get_prod_name(p.code)prod_name,pkg_prod.get_prod_qty(p.code)prod_qty,P.PLAN_ID, P.PLAN_DESC, a.dates,
P.PLAN_QTY, P.UNIT_ID, P.REMARKS
FROM PRD_PLAN_M P, (select rownum - 1 + to_date('20-jul-08', 'dd-mon-rrrr') dates
                    from all_objects
                    where rownum < to_date('26-jul-08', 'dd-mon-rrrr') -
                    to_date('20-jul-08', 'dd-mon-rrrr') + 2)a
where P.PLAN_DATE(+) = A.DATES
UNION
SELECT E.P_CODE,E.PROD_NAME,E.PROD_QTY,null,null,null,null,null,null FROM PROD_CODE E
where e.p_code not in (select code from prd_plan_m m where m.plan_date between '20-jul-2008' and '26-jul-2008')

Thanks for reply

[Updated on: Wed, 30 July 2008 02:09]

Report message to a moderator

Previous Topic:	How Can be use PL/SQL Procedure In Discoverer..?
Next Topic:	Create dynamic 'where' which the user can input on the parameter form for my select statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 23:38:00 CST 2024