Home » SQL & PL/SQL » SQL & PL/SQL » query
query [message #191616] Thu, 07 September 2006 05:38 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have to solve a query as :
if (PO.NEEDDATE in (@Basedate) to (@Basedate + 6) then po.QTY} else 0
This is one of the column to be displayed along with item and there is a group by done on Item.So I have to take a SUM function with the DECODE as well as with the SUM(po.qty) poqty
since this is inner query. I have to display both item and quantity. But since this is grouped this is being SUMMED twice.

There are some other conditions too, but I have shown here the part where I am facing the problem.

SELECT po.item, SUM(DECODE(SIGN(TO_DATE(
                         (SELECT DECODE(TO_CHAR(SYSDATE,'d'),
                            1,TO_CHAR(SYSDATE,'dd-mon-yy'),
                            2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
                            3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
                            4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
                            5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
                            6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
                              TO_CHAR(SYSDATE-7,'dd-mon-yy')
                                         ) 
                            FROM dual
                           ) BaseDate
                        )
                       -TO_DATE(po.needdate,'dd-mon-yy')
                    ),
                1,0,
                0,0,
                  DECODE(SIGN(TO_DATE(BaseDate + 6)
                              - TO_DATE(po.needdate,'dd-mon-yy')
                              ),
                          1,0,
                            (SELECT SUM(po.qty) poqty from stsc.planorder po
                               WHERE po.needdate >= BaseDate
                                 AND po.needdate < BaseDate + 6
                             )
                           
                         )
         )     )
         
         FROM PLANORDER po
         GROUP BY po.item


Thanks,
Mona
  • Attachment: Document.sql
    (Size: 11.13KB, Downloaded 76 times)

[Updated on: Thu, 07 September 2006 06:43]

Report message to a moderator

Re: query [message #191703 is a reply to message #191616] Thu, 07 September 2006 17:24 Go to previous message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

How about something like this

select po.item
, sum(select po.qty from dual where po.need_date between BaseDate AND BaseDate + 6)
from PLANORDER po
group by po.item

Br
Kim
Previous Topic: accessing database using Oracle SES
Next Topic: error help
Goto Forum:
  


Current Time: Tue Dec 06 04:28:04 CST 2016

Total time taken to generate the page: 0.06646 seconds