Home » SQL & PL/SQL » SQL & PL/SQL » ora-00939 when using CASE
ora-00939 when using CASE [message #213863] Fri, 12 January 2007 10:11 Go to next message
sandudana
Messages: 48
Registered: March 2006
Member
Hello everybody,

I am working on 10.2.0.2.0 database that has receintely been 'patch'-ed.
From that moment on, I have this piece of code that doesn't function any more and for which I get the ora-00939 error:

insert into DWT_DIM_TIPO_INCARICO(TIPOINC, PADRE,DESCRIZIONE, DATA_INSERT, PROVENIENZA)
select id_tipo_incarico, substr(padre,4) padre, descrizione, sysdate data_insert, provenienza
from
(
  SELECT TO_CHAR(ID_TIPO_INCARICO) id_tipo_incarico,
  CASE 
     WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (102,145,146,147,221,222,231,252,276,290,305,306,307,317,324,340,374,477,478,494) THEN 'SI-DOCENTI A CONTRATTO'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (331,141,142,1,475,476) THEN 'SI-AFFIDAMENTI ESTERNI'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (6,143,144,223,233,274,296,297,298,322,330,397,473,474,480,493) THEN 'SI-AFFIDAMENTI INTERNI'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (116,272,289) THEN 'SI-ASSEGNISTI DI RICERCA'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (294,309,348,413,414,355,129,299,391,128,278,277,367,254,251,308,398,342,344,407,403,357,406,338,368,361,362,399,369) THEN 'SI-CONDUTTORI DI TIROCINI E TUTORS'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (312,117,408,313,311) THEN 'SI-DOTTORANDI'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (122,283,244,282,291,268,245,246,247) THEN 'SI-ADDETTO ALL ESERCITAZIONI'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (124,336,339,255,326,310,341,323,404,400) THEN 'SI-ADDETTO AI LABORATORI'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (130,375,220) THEN 'SI-ESAMI'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (123,235,358,347,345,256,249,334) THEN 'SI-PRECORSI'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (125,390,320,321,389,281,280,388,364,386,392) THEN 'SI-SEMINARI'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (402,120,405,359,376,126,121,393) THEN 'SI-TEST DI AMMISSIONE, CONCORSI E PROVE FINALI'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (396,394,370,373,411,366,253,351,201,349,350,401) THEN 'SI-COORDINAMENTO E PROGETTAZIONE'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (363,346,410) THEN 'SI-COLLOQUI'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (372,365,107,114,295,106,105) THEN 'SI-CARICHE ISTITUZIONALI'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (412,113,112,111,292,409,360,356,104,108,109,110,115,371) THEN 'SI-PRESIDENZE E DIREZIONI'
	 WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (384,385,382) THEN 'SI-DIRIGENTI MEDICI/MIUCA'
     WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (103) THEN 'SI-MUTUAZIONE'
     WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (327,328) THEN 'SI-DOCENTI TEOLOGIA'
     WHEN TO_NUMBER(ID_TIPO_INCARICO) IN (101) THEN 'SI'
  END AS PADRE, DESCRIZIONE, 'M' provenienza
  FROM MACDOCE_TBL_TIPO_INCARICO
)
where substr(padre,1,2)='SI'


I receive ora-00939 'too many arguments for function'.
MACDOCE_TBL_TIPO_INCARICO is a view to a remote table on a 10.2.0.2 db.

Are there some type of restrictions when using 'CASE' ? This piece of code worked fine before applying the patch.

I even tried creting a view called test which contains this CASE. The view was created successfully, I can query the data but when I try to do

INSERT INTO DWT_DIM_TIPO_INCARICO(TIPOINC, PADRE,DESCRIZIONE, DATA_INSERT, PROVENIENZA)
SELECT * FROM test

I still receive ora-00939.

Any help will be appreciated.
Thank you

Daniela S
Re: ora-00939 when using CASE [message #213987 is a reply to message #213863] Sat, 13 January 2007 06:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If this patch ruined your code, check the contents of the patch (release notes). If nothing related should have been in the patch, I suggest you open a tar on Metalink.
Sounds very much like a bug.

I must admit I did not really look at your code, only gave it a quick glance.
Re: ora-00939 when using CASE [message #214077 is a reply to message #213987] Sun, 14 January 2007 19:07 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try to get a minimal query that still gets the same error.

- Delete everything from the query except the CASE statement.
- Remove the INSERT clause - make it a straight SELECT.
- Replace the remote table locally and try it without the DB link.
- Delete one WHEN clause at a time until the error goes away.
- Then reinstate the last line deleted, and try deleting some others. Repeat until you get the smallest CASE statement that gives an error.

Also check for triggers on the table - the error could be raised from there.

Ross Leishman
Previous Topic: About Column Names
Next Topic: Queries
Goto Forum:
  


Current Time: Sun Dec 04 04:40:29 CST 2016

Total time taken to generate the page: 0.04316 seconds