| order by clasue not working - in matrix report [message #89715] |
Fri, 02 July 2004 22:40  |
Rajeev Katyal
Messages: 55
Registered: April 2002
|
Member |
|
|
suppose i have following tables
===================================================
SQL> desc mssav_sales_summary
Name Null? Type
------------------------------- -------- ----
DPT_DPT_CODE NOT NULL CHAR(3)
SBP_SBP_CODE NOT NULL CHAR(6)
SAS_YRMTH NOT NULL DATE
SAS_QTY NUMBER
SAS_VAL NUMBER
SQL> desc mssav_product_pack
Name Null? Type
------------------------------- -------- ----
SBP_CODE NOT NULL CHAR(6)
SBP_DESC NOT NULL VARCHAR2(20)
PRD_CODE CHAR(3)
PRD_DESC VARCHAR2(20)
SBP_NET_WT_IN_KG NUMBER
===================================================
--------i am giving following query----------
SELECT SAS_YRMTH,PRD_DESC,SUM((SAS_QTY*SBP_NET_WT_IN_KG))/1000 MTs
FROM
MSSAV_SALES_SUMMARY A,
MSSAV_PRODUCT_PACK B
WHERE
A.SBP_SBP_CODE=B.SBP_CODE and
SAS_YRMTH BETWEEN '01-APR-2002' AND '01-MAR-2004' AND
b.prd_code in('BTR','PCH','CHO','FMB')
group by SAS_YRMTH,PRD_DESC
ORDER BY
DECODE( TO_CHAR(SAS_YRMTH,'MON'),'APR',1,'MAY',2,'JUN',3,'JUL',4,
'AUG',5,'SEP',6,'OCT',7,'NOV',8,'DEC',9,'JAN',10,'FEB',11,'MAR',12),
prd_desc
------------it is returning rows as follows -------------
SAS_YRMTH PRD_DESC MTS
--------- -------------------- ---------
01-APR-02 (D) BUTTER 22.303408
01-APR-03 (D) BUTTER 31.08144
01-APR-02 (D) PROCESSED CHEESE 2.1024
01-APR-03 (D) PROCESSED CHEESE 1.572
01-APR-03 Flav. Milk Gl. Bottl 7.4852269
01-MAY-02 (D) BUTTER 43.560386
01-MAY-03 (D) BUTTER 64.976026
01-MAY-02 (D) PROCESSED CHEESE 1.8072
01-MAY-03 (D) PROCESSED CHEESE 1.944
01-MAY-03 Flav. Milk Gl. Bottl 21.976092
01-JUN-02 (D) BUTTER 49.156876
01-JUN-03 (D) BUTTER 62.033911
01-JUN-02 (D) PROCESSED CHEESE 2.0016
01-JUN-03 (D) PROCESSED CHEESE 1.5096
01-JUN-03 Flav. Milk Gl. Bottl 10.021488
See the output carefully , it is showing month wise comparison for the products..like after 01-apr-02 for butter it is showing 01-apr-03.This is the right output.This is the output i am getting when i am running the query on backend i.e. sql prompt.
But i wish to provide the same output in matrix report.
prd_desc will be shown in colums
sas_yrmth will be shown in rows
mts will go as cell value.
But when i run the report it does not show the rows in the exact order by clause.
in other words
it firstly shows all records of year 2002 and then of 2003.
like
01-apr-02 butter
01-may-02 butter
01-jun-02 butter
01-jun-02 chocolate
01-jul-02 process cheese
01-apr-03 butter
Means i want that 01-apr-03 should be shown immediatley after 01-apr-02.
At sql prompt everything is ok.....but the same query in report builder provides different output.why So ?
please help.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
