Re: Hi there, having problems with the following:

You'll probably want something like null::varchar (or other appropriate
type) for the nulls.  It's trying to grab the type from the first
select and can't figure the type out for the null.  (Technically
speaking, it's probably supposed to be CAST(NULL AS VARCHAR) to
be technically sql complient, but hey)

On Fri, 23 Mar 2001, Jens Philipsen wrote:

> Hi there, having problems with the following:
> On Postgresql (on Linux)
> I have 3 tables:
> 
> 1)employee
> firstname
> lastname
> initials
> 
> 2)has_title
> initials
> title
> 
> 3)has_trade
> initials
> trade
> 
> 
> 
> Now, what I would like is to get a list of all employees with all
> their titles and trades - employees can have zero or several
> titles/trades.
> 
> For testing purposes I made a version in MS Access, in which this works:
> (SELECT medarbejder.initialer, fornavn, efternavn, telefon, titel, null AS fag
> FROM medarbejder, har_titel, har_fag
> where medarbejder.initialer=har_titel.initialer)
> UNION
> (SELECT medarbejder.initialer, fornavn, efternavn, telefon, null AS titel, fag
> FROM medarbejder, har_fag, har_titel
> WHERE medarbejder.initialer=har_fag.initialer)
> 
> But in Postgresql no such luck:
> 
> SELECT medarbejder.initialer, fornavn, efternavn, telefon, titel as titel, null AS fag
> FROM medarbejder, har_titel, har_fag 
> WHERE medarbejder.initialer=har_titel.initialer  
> UNION  
> SELECT medarbejder.initialer, fornavn, efternavn, telefon, null as titel, fag AS fag
> FROM medarbejder, har_fag, har_titel 
> WHERE medarbejder.initialer=har_fag.initialer
> 
> Only produces an error:
> "unable to transform varchar to unknown
> Each UNION | EXCEPT | INTERSECT clause must have compatible target"
> 
> Any suggestions?
> Thanx in advance
> Jens
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>