Home » SQL & PL/SQL » SQL & PL/SQL » function
function [message #270057] Tue, 25 September 2007 09:32 Go to next message
dimis283
Messages: 38
Registered: July 2007
Location: greece
Member
I want to create the function with this code

CREATE OR REPLACE Function Emy_function2()
.....
.....
Select Distinct Ppa.id Ppa_ID, 
case when  ppa.id in  (select cor.ppa_id from MSRTG cor where  cor.ppa_id=Ppa.id  ) then
( select org.description from  NISATION  org 
where org.id  in (select cor.org_id from ORG cor where  cor.ppa_id=Ppa.id and rownum=1 ))
                       else  '' end
                  description
into id,id1
from my_table Ppa

.......
.......

But there is an error PLS-00103.
What is wrong?
There are 2 tables my_table and ORG, having a relation many to many and MSRTG is the "relation" table.But some values at my_table are not related with ORG.I want to return all records from my_table and the description from ORG into acursor.
Is there a different way?

[Updated on: Tue, 25 September 2007 09:33]

Report message to a moderator

Re: function [message #270062 is a reply to message #270057] Tue, 25 September 2007 09:56 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Did you look up what the error means?
Re: function [message #270063 is a reply to message #270057] Tue, 25 September 2007 09:57 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Can you show the whole error message, as that will give a clue as to where the problem lies?
Re: function [message #270074 is a reply to message #270057] Tue, 25 September 2007 10:52 Go to previous messageGo to next message
dimis283
Messages: 38
Registered: July 2007
Location: greece
Member
The error says (it is in greek and I am not at that pc now) that "Found 'case' when expected symbol like...", at beggining I thought that is because I run the query at plsql code (and maybe in a cursor) and that is why this message is there because the qyery runs as standalone.I guess that I have no type error, anyway I can not look for that now.
At beggining the query were
Select Distinct Ppa.id Ppa_ID, ORG.description...
from my_table Ppa,ORG
where org.id=(select cor.org_id from MSRTG cor where  MSRTG.ppa_id=Ppa.id and rownum=1 )
....

but the values of ppa.id that not have org.description do not coming as results.
Dimis

[Updated on: Tue, 25 September 2007 10:54]

Report message to a moderator

Re: function [message #270101 is a reply to message #270074] Tue, 25 September 2007 14:16 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
How about posting the full error message and not just regurgitate what it says. Typos and important missed parts make anything else senseless.

8i does not support case statements in PL/SQL.
Previous Topic: Help : Problem with Limit clause in BULK COLLECT
Next Topic: Initialize a CLOB Locator?
Goto Forum:
  


Current Time: Sat Dec 10 12:41:10 CST 2016

Total time taken to generate the page: 0.12231 seconds