Can't SUBSTITUTE AUG days in your report?

Hello I am trying to write a sql statement that returns this report:

-------- Supplier Order Status Report -------- Page:


Supplier Supplier                  Order Order    Receive     Number
  Code Name                        No. Date     Date       of Days STATUS
---------- -------------------- ---------- -------- -------- --------- --------
   101 ACE Auto                   1000 04/03/15 04/06/15      3.00 Complete
                                  1001 04/03/15 04/05/15      2.00 Complete
                                  1002 04/04/15 04/05/15      1.00 Complete
                                  1003 04/04/15 04/05/15      1.00 Complete
                                  1004 04/03/15 04/06/15      3.00 Complete
                                  1013 04/13/15                    Open
********** ********************                              ---------
Avg Days                                                          2.00

   102 Better Auto Buys           1005 04/04/15 04/04/15       .00 Complete
                                  1006 04/03/15 04/06/15      3.00 Complete
                                  1010 04/13/15                    Open
                                  1011 04/13/15 04/13/15       .00 Complete
********** ********************                              ---------
Avg Days                                                          1.00

   103 Cars R Us                  1007 04/05/15 04/07/15      2.00 Complete
                                  1012 04/13/15                    Open
********** ********************                              ---------
Avg Days                                                          2.00

   104 Delta Parts                1008 04/05/15 04/06/15      1.00 Complete
                                  1009 04/03/15 04/05/15      2.00 Complete
********** ********************                              ---------
Avg Days                                                          1.50

      

this is what i have:

SET LINESIZE 100
TTITLE CENTER "------SUPPLIER ORDER STATUS REPORT------- Page:"

COLUMN SUPPLIER_CODE HEADING "Supplier|Code"
COLUMN SUPPLIER_NAME HEADING "Supplier|Name"
COLUMN ORD_NUM HEADING "Order|No."
COLUMN ORD_DATE HEADING "Order|Date"
COLUMN ORD_RECDATE HEADING "Recieve|Date"

BREAK ON SUPPLIER_CODE ON SUPPLIER_NAME 
SELECT S.SUPPLIER_CODE, SUPPLIER_NAME, ORD_NUM, ORD_DATE, ORD_RECDATE,   (ORD_RECDATE-ORD_DATE) AS "Number|of Days", DECODE(ORD_RECDATE, NULL, 'OPEN','COMPLETE') AS STATUS
FROM SUPPLIER S, ORD O
WHERE S.SUPPLIER_CODE = O.SUPPLIER_CODE
ORDER BY S.SUPPLIER_CODE;

      

My problem is when I try to CALCULATE AUG DAYS nothing happens in sqlplus or sql developer? ps I know this is not in the code, I left it.

+3


source to share


1 answer


add AVG(ORD_RECDATE-ORD_DATE) OVER (PARTITION BY s.supplier_code) AS avg_days

to your offerSELECT



+1


source







All Articles