Please let me know where is the error in the query

From: R.V.Reddy <rvreddy2000_at_gmail.com>
Date: 14 Sep 2005 02:38:46 -0700
Message-ID: <1126690726.457627.105150@g47g2000cwa.googlegroups.com>

I am getting amazing results from these queries. Only thing I am changin just from table to one table. Basically I would like

to get total quantity by forecast date wise. If I use SUM(CF.FRCST_QTY) the results are correct by date wise. If I use

SUM(MGR.FRCST_QTY) the results are wrong. Here I should get the forecast date wise. I am giving two queries and the results

also here. Please suggest where I went wrong.

SELECT
ITM.LUCENT_SITE ||'-'|| DECODE(Y.CUSTOMER_STD_NAME, 'CELESTICA',ITM.LUCENT_CODE_ID||'-INTERSITE',ITM.LUCENT_CODE_ID||'-FCST') Order_Type
, ITM.LUCENT_SITE ||'-'|| ITM.LUCENT_CODE_ID||'-'||'FCST' Order_Id
, ' ' Line
, ITM.CUSTOMER_PART_NO Part_Name
, ITM.LUCENT_SITE Part_Site
, ' ' Model
, ' ' StartUnit
, TO_CHAR(DECODE(SIGN(CF.FRCST_SHIP_DATE-TO_DATE('31-DEC-2037',
'DD-MON-YYYY')),1,TO_DATE('31-DEC-2037', 'DD-MON-YYYY'),CF.FRCST_SHIP_DATE),'MM/DD/YYYY') DueDate
, TO_CHAR(DECODE(SIGN(CF.FRCST_SHIP_DATE-TO_DATE('31-DEC-2037',
'DD-MON-YYYY')),1,TO_DATE('31-DEC-2037', 'DD-MON-YYYY'),CF.FRCST_SHIP_DATE),'MM/DD/YYYY') PromisedDate
, SUM(MGR.FRCST_QTY) Quantity

Changed one
, ' ' ShippedQuantity
, ' ' UnitSellingPrice
, ' ' Status
, ' ' Notes
, ' ' RequestDate
, ' ' U_Lucent_Order
, ' ' U_Lucent_Spec
, ' ' U_Lucent_Item
, TO_CHAR(DECODE(SIGN(CF.FRCST_SHIP_DATE-TO_DATE('31-DEC-2037',
'DD-MON-YYYY')),1,TO_DATE('31-DEC-2037', 'DD-MON-YYYY'),CF.FRCST_SHIP_DATE),'MM/DD/YYYY') U_CRSD
, ' ' U_ITEM_RECEIPT_DATE
, ' ' U_End_Cust_Id
, ' ' U_End_Cust_Name
, ' ' U_Application_Detail
, ' ' U_Tax_Benefit_Order
, ' ' U_Booked_Status
, ' ' U_Booked_Date
, ' ' U_Order_Management_Contact
, ' ' U_Item_Planner_Code
, ' ' U_Warehouse_Source
, ' ' U_Fcst_Source
, ' ' U_QtyInPrePack
, ' ' U_Fld50

FROM CURRENT_FORECAST CF, LUCENTWP_ITEM ITM, CUSTOMER Y, MATERIAL_GROSS_REQUIREMENT MGR
WHERE CF.SITE_CD = Y.SITE_CD

AND CF.CUSTOMER_NO = Y.CUSTOMER_NO
AND CF.RCRD_STATUS_CD IN('A',NULL)
AND CF.SITE_CD = ITM.SITE_CD
AND CF.ITEM_NO = ITM.ITEM_NO
AND CF.SITE_CD = MGR.SITE_CD
AND CF.ITEM_NO = MGR.CHILD_ITEM_NO

GROUP BY
ITM.LUCENT_SITE ||'-'|| DECODE(Y.CUSTOMER_STD_NAME, 'CELESTICA',ITM.LUCENT_CODE_ID||'-INTERSITE',ITM.LUCENT_CODE_ID||'-FCST')
,ITM.LUCENT_SITE ||'-'|| ITM.LUCENT_CODE_ID||'-'||'FCST'
,ITM.CUSTOMER_PART_NO
,ITM.LUCENT_SITE
,TO_CHAR(DECODE(SIGN(CF.FRCST_SHIP_DATE-TO_DATE('31-DEC-2037',
'DD-MON-YYYY')),1,TO_DATE('31-DEC-2037', 'DD-MON-YYYY'),CF.FRCST_SHIP_DATE),'MM/DD/YYYY'); Results like

                       Qty  ( displaying same qty for

                        all the dates)
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	04/04/2005	04/04/2005	9,271.00
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	04/11/2005	04/11/2005	9,271.00
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	04/18/2005	04/18/2005	9,271.00
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	04/25/2005	04/25/2005	9,271.00
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	05/02/2005	05/02/2005	9,271.00
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	05/09/2005	05/09/2005	9,271.00

2nd Query

--------------------------------------------------------Earlier one


, ' ' ShippedQuantity

, ' ' UnitSellingPrice

, ' ' Status

, ' ' Notes

, ' ' RequestDate

, ' ' U_Lucent_Order

, ' ' U_Lucent_Spec
, ' ' U_Lucent_Item
, TO_CHAR(DECODE(SIGN(CF.FRCST_SHIP_DATE-TO_DATE('31-DEC-2037',
'DD-MON-YYYY')),1,TO_DATE('31-DEC-2037', 'DD-MON-YYYY'),CF.FRCST_SHIP_DATE),'MM/DD/YYYY') U_CRSD
, ' ' U_ITEM_RECEIPT_DATE
, ' ' U_End_Cust_Id
, ' ' U_End_Cust_Name
, ' ' U_Application_Detail
, ' ' U_Tax_Benefit_Order
, ' ' U_Booked_Status
, ' ' U_Booked_Date
, ' ' U_Order_Management_Contact
, ' ' U_Item_Planner_Code
, ' ' U_Warehouse_Source
, ' ' U_Fcst_Source
, ' ' U_QtyInPrePack
, ' ' U_Fld50

FROM CURRENT_FORECAST CF, LUCENTWP_ITEM ITM, CUSTOMER Y, MATERIAL_GROSS_REQUIREMENT MGR
WHERE CF.SITE_CD = Y.SITE_CD

AND CF.CUSTOMER_NO = Y.CUSTOMER_NO
AND CF.RCRD_STATUS_CD IN('A',NULL)
AND CF.SITE_CD = ITM.SITE_CD
AND CF.ITEM_NO = ITM.ITEM_NO
AND CF.SITE_CD = MGR.SITE_CD
AND CF.ITEM_NO = MGR.CHILD_ITEM_NO

                        Qty  ( displaying by date


                       wise and this is correct one)

CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	04/04/2005	04/04/2005	14,728.00
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	04/11/2005	04/11/2005	7,504.00
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	04/18/2005	04/18/2005	11,928.00
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	04/25/2005	04/25/2005	10,584.00
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL	05/02/2005	05/02/2005	7,588.00
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	05/09/2005	05/09/2005	6,020.00
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	05/16/2005	05/16/2005	6,132.00
CL-CZ-FCST	CL-CZ-FCST	 	109181644	CL 	05/23/2005	05/23/2005	12,068.00

Please suggest where I did wrong. Received on Wed Sep 14 2005 - 04:38:46 CDT