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

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

select * from prd_UNPIVOT

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

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

it is not work in: Oracle9i Enterprise Edition Release - 64bit Production
this error ORA-00907: missing right parenthesis
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

enrique_aeoAuthor Commented:
slightwv (䄆 Netminder) Commented:
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.
enrique_aeoAuthor Commented:
It would be helpful to provide me the code
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
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

enrique_aeoAuthor Commented:
You can replace the word by ENERO to month

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
slightwv (䄆 Netminder) Commented:
>>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
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);


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
  FROM prd_unpivot,
       (    SELECT LEVEL n
              FROM DUAL
        CONNECT BY LEVEL <= 6);

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.