Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-11-04
8
Medium Priority
?
767 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 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
Industry Leaders: 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!

 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 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 78

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 1600 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

572 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