Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Fri, 18 Aug 2006 09:31:00 +0200
From: Dyre.Tjeldvoll@Sun.COM
Subject: Re: LEFT JOIN with a subquery not working in Derby?
In-reply-to: <96787933F10FC7419E66FF094F105F091D71@snowball2005.maner.local>
Sender: Dyre.Tjeldvoll@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <x1o3lkpmo5zf.fsf@sun.com>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
References: <96787933F10FC7419E66FF094F105F091D71@snowball2005.maner.local>
User-Agent: Gnus/5.110004 (No Gnus v0.4) Emacs/22.0.50 (usg-unix-v)

"Halit M. Maner" <hmaner@maner.net> writes:

> The statement below has been working fine in Cloudscape 5.1 but it is not working in Derby (we are using 10.1.2.4).
>  
> ij> SELECT * FROM admapphdr LEFT  JOIN admappdtl ON (aah_id=aad_aah_id AND
> aad_seq_num = (SELECT MAX(a2.aad_seq_num) FROM admappdtl a2));
>  
> It is throwing:
>  
> ERROR 42972: An ON clause associated with a JOIN operator is not valid.
>  
> It works fine if we remove the SELECT MAX (but we need it!)...
>  
> admapphdr is a header table with an INTEGER id.
> admappdtl is admapphdr's detail table with the same id plus a sequence number for the various detail rows...
>  
> Is this a limitation of Derby or are we overlooking something?

Sorry about the late reply. You probably have found a workaround by
now. 

Couldn't you just perform the 'select MAX' first, and insert the
value in your join condition? (A pain, I know...)

In the general case where the sub-query returns multiple rows, you
could create a view of the sub-query, and then join
the two tables with this view in a three-way join.

-- 
dt

However, experience shows that for many people and many applications a
dose of paranoia is reasonable - Bjarne Stroustrup