pejbaanv

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 - 07:32:59 CDT