Re: SQL Question

["R. Bruce Hoffman, Jr." <rbruceh@xxxxxxxxxxxxx>]

-----Original Message-----
From: John Taylor <john.taylor@telusplanet.net>
To: MIDRANGE-L@midrange.com <MIDRANGE-L@midrange.com>
Cc: bvstone@taylorcorp.com <bvstone@taylorcorp.com>
Date: Thursday, June 29, 2000 8:09 PM
Subject: Re: SQL Question


>Brad,
>
>As far as I know, the DB2/400 SQL implementation does not provide a way to
>specify the number of records per matching set that you want returned.
>Therefore, you need a sub-select. You can try something like this:
>
>For a complete list of orders joined to the first item record:
>
>Select a.OrderNo, a.Customer, b.Seqno, b.Product
>From Orders a Join Items b
>Where b.Seqno =
>        ( Select Min(c.Seqno) From Items c
>              Where c.OrderNo = a.OrderNo
>              Group by c.OrderNo )
>


sidebar...  you don't (shouldn't) need the the group by clause in a
correlated subquery as the selection is already limited to a single order
number.  In V4R2 (definitely) and up you don't.  The requirement here is
that the correlated subquery return a single value, and with just the where
clause, it does so.

===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified AS/400 Professional System Administrator
 -- IBM Certified AS/400 Professional Network Administrator
 -- IBM Certified Specialist - AS/400 Administrator
 -- IBM Certified Specialist - RPG IV Developer

"If all you have is RPG, then everything looks like a 400!"


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---