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);
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
enrique_aeoAsked:
Who is Participating?
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:
I ATTACHED error
errUNPIVOT.png
0
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.
0
enrique_aeoAuthor Commented:
It would be helpful to provide me the code
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

0
enrique_aeoAuthor Commented:
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
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
...
0
sdstuberCommented:
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

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
MikeOM_DBACommented:
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
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.