Home » SQL & PL/SQL » SQL & PL/SQL » Help in pivot query (Oracle 8.1.7, Windows XP)
Help in pivot query [message #292137] Tue, 08 January 2008 00:21 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Dear Experts,

I have a pivot query like this

select A.LICNO,
MAX(DECODE(CODE,'T0102',B.QTY)) "SVC-1",
MAX(DECODE(CODE,'T0103',B.QTY)) "SVC-2",
MAX(DECODE(B.CODE,'T0104',B.QTY)) "SVC-3",
MAX(DECODE(B.CODE,'T0105',B.QTY)) "SVC-4",
MAX(DECODE(B.CODE,'T0106',B.QTY)) "SVC-5",
MAX(DECODE(B.CODE,'T0107',B.QTY)) "SVC-6",
from mrh a,mrj b
where a.RO_DTIME BETWEEN '01-JAN-07 AND '31-DEC-07' and a.id=b.rohd
GROUP BY a.LICNO;

I am getting the result but my doubt is if other than T0102,T0103,T0104,T0105,T0106,T0107 I want to put under column OTHERS.

Can anyone help me?
Re: Help in pivot query [message #292138 is a reply to message #292137] Tue, 08 January 2008 00:24 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
DECODE(B.CODE, 'T0102', NULL
             , 'T0103', NULL
             , 'T0104', NULL
             , 'T0105', NULL
             , 'T0106', NULL
             , 'T0107', NULL
             , B.QTY) 


Ross Leishman
Re: Help in pivot query [message #292293 is a reply to message #292137] Tue, 08 January 2008 08:13 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
kumarvk wrote on Tue, 08 January 2008 01:21


where a.RO_DTIME BETWEEN '01-JAN-07 AND '31-DEC-07' and a.id=b.rohd



First, this code will give you error 1756.

Second, this is a sloppy and invalid query if a.RO_DTIME is a DATE column.

FOO SCOTT>l
  1  select 1 from dual
  2* where sysdate between '01-JAN-07' AND '31-DEC-07'
FOO SCOTT>/
where sysdate between '01-JAN-07' AND '31-DEC-07'
                                      *
ERROR at line 2:
ORA-01843: not a valid month

Previous Topic: Simultaneous UPDATE/SELECT, is this possible
Next Topic: DUPLICATION OF RESULT
Goto Forum:
  


Current Time: Wed Dec 07 02:41:47 CST 2016

Total time taken to generate the page: 0.09068 seconds