Home » Developer & Programmer » Reports & Discoverer » Matrix Report Query Problem
Matrix Report Query Problem [message #336787] |
Tue, 29 July 2008 00:49 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sweetkhaliq
Messages: 200 Registered: April 2006
|
Senior Member |
![sweetkhaliq%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
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
|
|
|
|
Re: Matrix Report Query Problem [message #337120 is a reply to message #337110] |
Wed, 30 July 2008 00:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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.
|
|
|
|
Re: Matrix Report Query Problem [message #337183 is a reply to message #336787] |
Wed, 30 July 2008 02:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
sweetkhaliq
Messages: 200 Registered: April 2006
|
Senior Member |
![sweetkhaliq%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
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
|
|
|
Goto Forum:
Current Time: Thu Sep 12 10:56:08 CDT 2024
|