Re: pejbaanv

Hi,

within a union you can't specify column names in the order by stat. You've got to use column position instead:

        ORDER BY 2; pejbaanv_at_my-dejanews.com schrieb:
>
> 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

--

Regards

Matthias Gresz :-)

GreMa_at_t-online.de

          /|
         / |        
        /| |\
       /||  |\
      / O    |\         
     |        |\ Galopping Horse beats Running Man.
    /          |\
   /      /     |\
  /    __/|      |\
  \°   /  |       |\
   \/_/   |        |\