OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query [message #191616]

Thu, 07 September 2006 05:38

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 398 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

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

Report message to a moderator

Previous Topic:	accessing database using Oracle SES
Next Topic:	error help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 05:29:53 CST 2024