Here is my suggestion for re-writing your query:
SELECT c.*,
DECODE(c.pid,0,NULL,p.name)||DECODE(c.cid,0,NULL,DECODE(c.pid,0,NULL,',')
||b.name) contactperson
FROM contact c, company b, person p
WHERE c.cid = b.id (+)
AND c.pid = p.id (+)
ORDER BY contactperson;
As to why you're getting an error in PL/SQL, it is difficult to tell without
seeing the code. However, if you are using the SELECT statement exactly as shown
in your e-mail, remember that in PL/SQL every SELECT statement requires an INTO
clause.
Hope this helps.
Michael Serbanescu
pejbaanv_at_my-dejanews.com wrote:
>
> Got a Select Statement like the following:
>
> SELECT C.*, P.NAME||', '||B.NAME CONTACTPERSON
> FROM CONTACT C, COMPANY B, PERSON P
> WHERE (C.CID=B.ID) AND (C.PID = P.ID )
> UNION
> SELECT C.*, P.NAME CONTACTPERSON
> FROM CONTACT C, PERSON P
> WHERE(C.CID=0)AND (C.PID = P.ID )
> UNION
> SELECT C.*, B.NAME CONTACTPERSON
> FROM CONTACT C, COMPANY B
> WHERE (C.CID=B.ID) AND (C.PID=0)
> ORDER BY CONTACTPERSON;
>
> This is maybe not the best way to create the statement (suggestions are
> welcome).
>
> The meaning of this statement is to show everything in the CONTACT table.
> The identifiers are numbers. Therefor I use the name fields of Table PERSON
> and COMPANY to show which contact I am referring to. A Contact can have a
> Person link and/or a Company link.
>
> Contact
> ID CID PID
> 1 1 1
> 2 2 0
> 3 0 2
>
> COMPANY
> 1 TheCompany
> 2 MyCompany
>
> PERSON
> 1 Patrick
> 2 Theo
>
> Result of Query:
> ID CID PID CONTACTPERSON
> 2 2 0 MyCompany
> 1 1 1 Patrick, TheCompany
> 3 0 2 Theo
>
> The above works within a SQL envoirenment.
>
> When I put this query in a (PL/SQL) procedure I get an error on the order by
> statement
>
> Thnxs,
> Patrick
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Thu Jul 16 1998 - 23:45:29 CDT
