Re: HELP!!!!!

Hi,

normally you use the field number in a union sort.

So, try changing to SELECT P.NAME CONTACTPERSON, c.* and do ORDER BY 1

In article <6okrtp$c96$1_at_nnrp1.dejanews.com>,   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
>

-----== 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 - 09:30:29 CDT