Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Newbie SQL help needed - URGENT

Newbie SQL help needed - URGENT

From: Pawan <pawanputrahanuman_at_yahoo.com>
Date: 21 May 2003 17:19:22 -0700
Message-ID: <84857442.0305211619.4d850c01@posting.google.com>


I am writing a query but am not the desired result. Need help please. Here is the code



select
a.storeID,
a.STORE_NM,
SUM(decode(upper(a.MTH),'JAN',a.MTHQ,null)) ||'|'||
SUM(decode(upper(a.MTH),'FEB',a.MTHQ,null)) ||'|'||
SUM(decode(upper(a.MTH),'MAR',a.MTHQ,null))||chr(10)||
SUM(decode(upper(a.MTH),'JAN',a.FCST_AMT,null)) ||'|'||
SUM(decode(upper(a.MTH),'FEB',a.FCST_AMT,null)) ||'|'||
SUM(decode(upper(a.MTH),'MAR',a.FCST_AMT,null))||chr(10)||
SUM(decode(upper(a.MTH),'JAN',a.DSAR_FRD_AMT_PRIOR,null)) ||'|'||
SUM(decode(upper(a.MTH),'FEB',a.DSAR_FRD_AMT_PRIOR,null)) ||'|'||
SUM(decode(upper(a.MTH),'MAR',a.DSAR_FRD_AMT_PRIOR,null)) ||'|'||chr(10)||
SUM(decode(upper(a.MTH),'JAN',a.DSAR_FRD_AMT,null))||'|'||
SUM(decode(upper(a.MTH),'FEB',a.DSAR_FRD_AMT,null))||'|'||
SUM(decode(upper(a.MTH),'MAR',a.DSAR_FRD_AMT,null))||'|'||chr(10)||
SUM(decode(upper(b.CATG),'ANL',b.ALLOC_AMT,null)) ||'|'||
SUM(decode(upper(b.CATG),'SKC',b.ALLOC_AMT,null))||'|'||
SUM(decode(upper(b.CATG),'CLD',b.ALLOC_AMT,null)) ||'|'||
SUM(decode(upper(b.CATG),'DIA',b.ALLOC_AMT,null)) ||'|'||
SUM(decode(upper(b.CATG),'OTH',b.ALLOC_AMT,null)) ||'|'||chr(10)||
SUM(decode(upper(b.CATG),'ANL',b.PLAN_PMT,null)) ||'|'||
SUM(decode(upper(b.CATG),'SKC',b.PLAN_PMT,null))||'|'||
SUM(decode(upper(b.CATG),'CLD',b.PLAN_PMT,null)) ||'|'||
SUM(decode(upper(b.CATG),'DIA',b.PLAN_PMT,null)) ||'|'||
SUM(decode(upper(b.CATG),'OTH',b.PLAN_PMT,null)) ||'|'||chr(10)||
SUM(decode(upper(b.CATG),'ANL',b.PRIOR_PMT_AMT,null)) ||'|'||
SUM(decode(upper(b.CATG),'SKC',b.PRIOR_PMT_AMT,null))||'|'||
SUM(decode(upper(b.CATG),'CLD',b.PRIOR_PMT_AMT,null)) ||'|'||
SUM(decode(upper(b.CATG),'DIA',b.PRIOR_PMT_AMT,null)) ||'|'||
SUM(decode(upper(b.CATG),'OTH',b.PRIOR_PMT_AMT,null)) ||'|'||chr(10)||
SUM(decode(upper(b.CATG),'ANL',b.PMT_AMT,null)) ||'|'||
SUM(decode(upper(b.CATG),'SKC',b.PMT_AMT,null))||'|'||
SUM(decode(upper(b.CATG),'CLD',b.PMT_AMT,null)) ||'|'||
SUM(decode(upper(b.CATG),'DIA',b.PMT_AMT,null)) ||'|'||
SUM(decode(upper(b.CATG),'OTH',b.PMT_AMT,null)) ||'|'||
c.BRD_ALT_DS,

c.EVENT_START_DT,
c.PRICE_AMT
from VOLUME a,
TRADE b,
PROMO c
where a.storeID in ( '007300')
and a.storeID =b.storeID
and a.storeID =c.storeID
group by a.storeID,a.STORE_NM,c.BRD_ALT_DS,c.EVENT_START_DT,c.PRICE_AMT

RESULTS
SQL> / STORE STORE_NM
------ -----------------------------------
SUM(DECODE(UPPER(A.MTH),'JAN',A.MTHQ,NULL))||'|'||SUM(DECODE(UPPER(

EVENT_STAR PRICE_AMT
---------- ----------

007300 FOOD INC
||

510300|894000|1662000
1564097.16|1671413.52|2870744.28|
1020685.2|2299272.48|1888686.96|
3225000|378000|351000||154500|
5015520|102774|25455||120|
8832101.1|3443561.52|1222076.1||21888|
1719421.2|185466.96|47736||115.2|Product1 2003-01-01 3.94

007300 FOOD INC
||

510300|894000|1662000
1564097.16|1671413.52|2870744.28|
1020685.2|2299272.48|1888686.96|
3225000|378000|351000||154500|
5015520|102774|25455||120|
8832101.1|3443561.52|1222076.1||21888|
1719421.2|185466.96|47736||115.2|Product2 2003-02-01 3.65

007300 FOOD INC
||

510300|894000|1662000
1564097.16|1671413.52|2870744.28|
1020685.2|2299272.48|1888686.96|
3225000|378000|351000||154500|
5015520|102774|25455||120|
8832101.1|3443561.52|1222076.1||21888|
1719421.2|185466.96|47736||115.2|Product3 2003-02-22 6.64

007300 FOOD INC
||

510300|894000|1662000
1564097.16|1671413.52|2870744.28|
1020685.2|2299272.48|1888686.96|
3225000|378000|351000||154500|
5015520|102774|25455||120|
8832101.1|3443561.52|1222076.1||21888|
1719421.2|185466.96|47736||115.2|Product3 2003-01-01 9.58

007300 FOOD INC
||

510300|894000|1662000
1564097.16|1671413.52|2870744.28|
1020685.2|2299272.48|1888686.96|
3225000|378000|351000||154500|
5015520|102774|25455||120|
8832101.1|3443561.52|1222076.1||21888|
1719421.2|185466.96|47736||115.2|Product4 2003-01-01 12.99

Will like to get this:

007300 FOOD INC
||

510300|894000|1662000
1564097.16|1671413.52|2870744.28|
1020685.2|2299272.48|1888686.96|
3225000|378000|351000||154500|
5015520|102774|25455||120|

8832101.1|3443561.52|1222076.1||21888|
1719421.2|185466.96|47736||115.2|Product1|2003-01-01||| 3.94
1719421.2|185466.96|47736||115.2|Product2|2003-02-01||| 3.65
1719421.2|185466.96|47736||115.2|Product3|2003-02-22||| 6.64
1719421.2|185466.96|47736||115.2|Product3|2003-01-01||| 9.58
1719421.2|185466.96|47736||115.2|Product4|2003-01-01|||12.99
Received on Wed May 21 2003 - 19:19:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US