Re: SQL question

[Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>]

Use the combination: join lateral … and limit 1




tir. 4. jan. 2022 kl. 06.31 skrev Charles Wilt <charles.wilt@xxxxxxxxx>:

> It usually does to an RPG developer...
>
> But a subselect is almost always a poor performing choice.
>
> Take a look at this presentation,
>
> https://www.linkedin.com/posts/charles-wilt-2a4a303_sql-for-the-rpg-developer-activity-6641756015354355712-woZR
>
> Charles
>
> On Mon, Jan 3, 2022 at 5:02 PM x y <xy6581@xxxxxxxxx> wrote:
>
> > Hmmm.  FETCH FIRST ROW ONLY comes to mind in a subselect where the key is
> > item number and lead time.
> >
> > On Mon, Jan 3, 2022 at 2:30 PM James H. H. Lampert via MIDRANGE-L <
> > midrange-l@xxxxxxxxxxxxxxxxxx> wrote:
> >
> > > Suppose we have a line-item file, and a part master file.
> > >
> > > The line item records have a part number. And it cross-references to
> the
> > > part master file.
> > >
> > > But if a given part is stocked in two or more warehouses, then the part
> > > master file has a record for it at each warehouse that stocks it.
> > >
> > > Now, suppose we have a join between the line item file and the part
> > > master file, on the part number. For each line item, the join is
> > > returning a record for every matching part master record.
> > >
> > > Not especially good. Is there a way, that works in DB2/400, to limit
> > > that to only one join record per line item? And to select, for example,
> > > the one with the low value in the "lead time" field?
> > >
> > > --
> > > JHHL
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at https://archive.midrange.com/midrange-l.
> > >
> > > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
> related
> > > questions.
> > >
> > > Help support midrange.com by shopping at amazon.com with our affiliate
> > > link: https://amazon.midrange.com
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/midrange-l.
> >
> > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> > questions.
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate
> > link: https://amazon.midrange.com
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com