Solved

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

Posted on 2014-11-04
8
607 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Author Comment

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

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 77

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 77

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 74

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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