Forum Stats

  • 3,851,925 Users
  • 2,264,053 Discussions
  • 7,904,906 Comments

Discussions

pivot query

user8167598
user8167598 Member Posts: 114
edited Feb 8, 2014 5:51AM in SQL & PL/SQL

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')

};

Tagged:
user8167598

Best Answer

  • KarK
    KarK Member Posts: 766
    edited Feb 8, 2014 4:47AM Answer ✓

    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.
    
    
    

Answers

  • KarK
    KarK Member Posts: 766
    edited Feb 8, 2014 4:47AM Answer ✓

    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
    Partha Sarathy S Member Posts: 1,200 Silver Trophy
    edited Feb 8, 2014 4:05AM

    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')
    );
    
    user8167598Partha Sarathy S
  • AnnEdmund
    AnnEdmund Member Posts: 1,466 Gold Trophy

    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
  • AnnEdmund
    AnnEdmund Member Posts: 1,466 Gold Trophy
    edited Feb 8, 2014 5:39AM

    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
    user8167598AnnEdmund
  • user8167598
    user8167598 Member Posts: 114

    Thanks ! I should have gone to spec savers !

This discussion has been closed.