Solved

plsql - UNPIVOT: ORA-00907: missing right parenthesis (Oracle9i)

Posted on 2014-11-04
8
570 Views
Last Modified: 2014-11-05
Hi experts

i have this query

create table prd_UNPIVOT(
codigoMaterialReal number, Enero number, Febrero number, Marzo number, Abril number, Mayo number, Juni number
);


insert into prd_UNPIVOT values(592025000040252,11.6,11.7,12.7,13.7,14.7,15.7);
insert into prd_UNPIVOT values(600017000030044,12.78,12.78,12.78,12.8,12.78,12.78);
commit;

select * from prd_UNPIVOT

--col codigoMaterialReal form 99999999999999999
select codigoMaterialReal, month
from
(
    (
        select codigoMaterialReal, Enero, Febrero, Marzo, Abril, Mayo, Juni
  from prd_UNPIVOT
    )
    unpivot
    (
        month
        for mymonth in
            ( Enero, Febrero, Marzo, Abril, Mayo, Juni)
    )
)

it is works in: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

it is not work in: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
this error ORA-00907: missing right parenthesis
0
Comment
Question by:enrique_aeo
8 Comments
 

Author Comment

by:enrique_aeo
ID: 40421883
I ATTACHED error
errUNPIVOT.png
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 40421887
Oracle 9i doesn't have the PIVOT and UNPIVOT functions.  I believe those were new to 11g.

You will need to write your own using some of the old tricks likely using MAX/MIN functions.

There are examples out there.
0
 

Author Comment

by:enrique_aeo
ID: 40421912
It would be helpful to provide me the code
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 40421929
select codigoMaterialReal,Enero from prd_UNPIVOT
union all
select codigoMaterialReal,Febrero from prd_UNPIVOT
union all
select codigoMaterialReal,Marzo from prd_UNPIVOT
union all
select codigoMaterialReal,Abril from prd_UNPIVOT
union all
select codigoMaterialReal,Mayo from prd_UNPIVOT
union all
select codigoMaterialReal,Juni from prd_UNPIVOT
/

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 40422074
ok
You can replace the word by ENERO to month

CODIGOMATERIALREAL      ENERO
592025000040252            11.6
600017000030044            12.78
592025000040252            11.7
600017000030044            12.78
592025000040252            12.7
600017000030044            12.78
592025000040252            13.7
600017000030044            12.8
592025000040252            14.7
600017000030044            12.78
592025000040252            15.7
600017000030044            12.78
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40422086
>>You can replace the word by ENERO to month

Simple column alias on the first select:

select codigoMaterialReal,Enero month from prd_UNPIVOT
union all
...
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 40422094
rather than union 6 times, join to a fixed table and use decode or case
should be about 6 times more efficient.  If you extend it to a full year, then it should be 12 times more efficient than a 12-way union

SELECT codigomaterialreal,
       DECODE(n,  1, enero,  2, febrero,  3, marzo,  4, abril,  5, mayo,  6, juni) month
  FROM prd_unpivot,
       (    SELECT LEVEL n
              FROM DUAL
        CONNECT BY LEVEL <= 6);


or

SELECT codigomaterialreal,
       CASE n
           WHEN 1 THEN enero
           WHEN 2 THEN febrero
           WHEN 3 THEN marzo
           WHEN 4 THEN abril
           WHEN 5 THEN mayo
           WHEN 6 THEN juni
       END
           month
  FROM prd_unpivot,
       (    SELECT LEVEL n
              FROM DUAL
        CONNECT BY LEVEL <= 6);
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40424199
Complete query starting line 4 would be:
SQL> ed
Wrote file afiedt.buf

  1  WITH Prd_Unpivot ( Codigomaterialreal, Enero, Febrero, Marzo, Abril, Mayo, Junio )
  2    AS (SELECT 592025000040252, 11.6, 11.7, 12.7, 13.7, 14.7, 15.7 FROM DUAL UNION ALL
  3        SELECT 600017000030044, 12.78, 12.78, 12.78, 12.8, 12.78, 12.78 FROM DUAL)
  4    SELECT Codigomaterialreal
  5         , DECODE ( N, 1, 'Enero', 2, 'Febrero', 3, 'Marzo', 4, 'Abril', 5, 'Mayo', 6, 'Junio' ) Month
  6         , DECODE ( N, 1, Enero, 2, Febrero, 3, Marzo, 4, Abril, 5, Mayo, 6, Junio ) Valor
  7      FROM Prd_Unpivot P
  8         , (    SELECT LEVEL N
  9                  FROM DUAL
 10            CONNECT BY LEVEL <= 6) L
 11* ORDER BY Codigomaterialreal, N
SQL> /

CODIGOMATERIALREAL MONTH                                       VALOR
------------------ -------------------------------- ----------------
   592025000040252 Enero                                        11.6
   592025000040252 Febrero                                      11.7
   592025000040252 Marzo                                        12.7
   592025000040252 Abril                                        13.7
   592025000040252 Mayo                                         14.7
   592025000040252 Junio                                        15.7
   600017000030044 Enero                                       12.78
   600017000030044 Febrero                                     12.78
   600017000030044 Marzo                                       12.78
   600017000030044 Abril                                        12.8
   600017000030044 Mayo                                        12.78
   600017000030044 Junio                                       12.78

12 rows selected.

Open in new window

:p
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question