pivot query

user8167598

hi

I get an invalid character when running this query:

select dept,sales,

--activemonth,

rtrim(to_char(activemonth,'Month')) as currentM,

length(rtrim(to_char(activemonth,'Month'))) as length1

from dateorder

pivot

{

  sum(sales) as total

  for currentM in ('March')

};

can anyone help please

table / insert file attached

Thankjs

CREATE TABLE "STORE"."DATEORDER"

   (    "DEPT" VARCHAR2(11 BYTE),

    "ACTIVEMONTH" DATE,

    "LASTUPDATED" DATE DEFAULT SYSDATE,

    "SALES" NUMBER(6,2)

   );

  

--------------------------------------------------------

--  File created - Saturday-February-08-2014  

--------------------------------------------------------

REM INSERTING into DATEORDER

SET DEFINE OFF;

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('Books',to_date('24-MAR-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),82.34);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('Books',to_date('11-OCT-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),283.42);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('Books',to_date('01-FEB-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),31.04);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('Books',to_date('16-FEB-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),46.07);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('Toys',to_date('28-JUN-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),179);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('Toys',to_date('13-OCT-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),285.77);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('Toys',to_date('22-MAY-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),141.99);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('Toys',to_date('04-NOV-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),307.67);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('China',to_date('03-MAY-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),122.32);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('China',to_date('03-JAN-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),2.71);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('China',to_date('13-MAY-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),132.12);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('China',to_date('20-OCT-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),292.59);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('Stationery',to_date('05-DEC-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),338.05);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('Stationery',to_date('04-JUL-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),184.75);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('Stationery',to_date('12-MAY-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),131.87);

Insert into DATEORDER (DEPT,ACTIVEMONTH,LASTUPDATED,SALES) values ('Stationery',to_date('25-JAN-14','DD-MON-RR'),to_date('08-FEB-14','DD-MON-RR'),24.49);

commit;

select dept,sales,

--activemonth,

rtrim(to_char(activemonth,'Month')) as currentM,

length(rtrim(to_char(activemonth,'Month'))) as length1

from dateordera

pivot

{

  sum(sales) as total

  for currentM in ('March')

};

KarK

Your query has syntax error apart from the logic:

Try the below:

select * from 
(
select dept,sales,
--activemonth,
rtrim(to_char(activemonth,'Month')) as currentM,
length(rtrim(to_char(activemonth,'Month'))) as length1
from dateorder
)
pivot
(
  sum(sales) as total
  for currentM in ('March')
);

OUTPUT:

DEPT           LENGTH1 'March'_TOTAL
----------- ---------- -------------
Stationery           7
Books                8
Toys                 7
Toys                 8
Toys                 3
Stationery           4
China                7
Books                5         82.34
Books                7
Toys                 4
China                3


DEPT           LENGTH1 'March'_TOTAL
----------- ---------- -------------
Stationery           8
Stationery           3


13 rows selected.

KarK

Your query has syntax error apart from the logic:

Try the below:

select * from 
(
select dept,sales,
--activemonth,
rtrim(to_char(activemonth,'Month')) as currentM,
length(rtrim(to_char(activemonth,'Month'))) as length1
from dateorder
)
pivot
(
  sum(sales) as total
  for currentM in ('March')
);

OUTPUT:

DEPT           LENGTH1 'March'_TOTAL
----------- ---------- -------------
Stationery           7
Books                8
Toys                 7
Toys                 8
Toys                 3
Stationery           4
China                7
Books                5         82.34
Books                7
Toys                 4
China                3


DEPT           LENGTH1 'March'_TOTAL
----------- ---------- -------------
Stationery           8
Stationery           3


13 rows selected.

Partha Sarathy S

Try this.

select * from (
select dept,sales,
--activemonth,
rtrim(to_char(activemonth,'Month')) as currentM,
length(rtrim(to_char(activemonth,'Month'))) as length1
from dateorder)
pivot
(
  sum(sales) as total
  for currentM in ('March')
);

Don't know what you are trying to achieve, but it would be better to do like this.

select DEPT,
       LENGTH1,
       NVL("'March'_TOTAL",0)
from (
select dept,sales,
--activemonth,
rtrim(to_char(activemonth,'Month')) as currentM,
length(rtrim(to_char(activemonth,'Month'))) as length1
from dateorder)
pivot
(
  sum(sales) as total
  for currentM in ('March')
);

AnnEdmund

You mean this?

SELECT dept,
       SUM(DECODE(currentM,'March',sales)) Total,
       currentM
FROM(SELECT dept,
       sales,
       to_char(activemonth,'fmMonth') AS currentM
 FROM DATEORDER)
 WHERE currentM = 'March'
 GROUP BY dept,currentM;


OUTPUT:-



DEPT         
TOTAL CURRENTM



----------- ---------- ---------



Books        
82.34 March

AnnEdmund

Like this with PIVOT? If this is not your requirement then tell your requirement and expected output

SELECT *
FROM(SELECT dept,
       sales,
       to_char(activemonth,'fmMonth') AS currentM
 FROM DATEORDER)
PIVOT(SUM(sales) Total FOR currentM IN('March'))


OUTPUT:-
-------




DEPT    
'March'_TOTAL



----------- -------------
Stationery
China



Books           
82.34



Toys

user8167598

Thanks ! I should have gone to spec savers !