RE: SQL question on join and limit rows retrieved

["Porterfield, Sean" <SPorterfield@xxxxxxxxxxxxxxxxxxxxxxx>]

> From: CRPence

> Porterfield, Sean wrote:
> > I have an RPGLE program that works and lists header/detail (one to
> > many) for selection.  This means a standard SETLL/READ or READE on the
> > header and CHAIN to detail once to get the first record.  The
> > remaining detail records are only retrieved when one entry is
> > selected.  (The first detail record is intended as an overview / brief
> > glimpse.)
> >
> > I want to duplicate this with SQL to allow for easy enhancements and
> > web enablement.
> >
> > <<SNIP>>

>    Making the described happen in SQL is effectively a /trick/ [for lack
>  of full details of the detail table; seems a reply already suggests there
>  is no /line number/ for example] which will most likely produce _different_
>  [not necessarily undesirable] results.
> That, and probably with a higher cost in all of CPU, memory, and temporary storage.

>    Given an already functional program, apparently already efficient with keyed
>  selection, how is changing to SQL expected to effect both easier enhancements
> and web enablement?  Is the intent to perform the query from the web application
>  rather than generating a result set by calling a stored procedure?

Easier enhancement means being able to filter the results by adjusting the SQL based on user input on the web page.  Currently the multiple views into the listing are separate options (view by branch, view by user, etc.)  Compared to the various tricks employed in the original programs to get the lists, adding to the WHERE clause is very easy.

I'm running the SQL in PHP, so web enablement is essentially done, other than the aforementioned filters and the requisite security that goes with accepting input to the SQL statement from the browser (even if it is "just" from the IT department.)

A brief glance at my result set showed that I was getting the desired record, but I agree there is no guarantee that it will always be so.  I may go back and modify the original RPG programs as well, specifically adding the line number seems to be a good idea.  The database file already has a sequence number field; it's just not used for whatever reason.
--
Sean Porterfield

This email is confidential, intended only for the named recipient(s) above and may contain information that is privileged.  If you have received this message in error or are not the named recipient(s), please notify the sender immediately and delete this email message from your computer as any and all unauthorized distribution or use of this message is strictly prohibited.  Thank you.