Re: Has anyone ever created an Index Table that included a JOIN table?

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi, Alan

I tried this SELECT statement - got an error that the period before DEF 
is not valid - I wondered if you knew some new syntax!

So I replaced that with the comma, as I expected - the SELECT statement 
ran fine.

So I tried to create a view of it -

CREATE VIEW VHAMBERG/TESTVIEW AS
WITH ABC as
 (select acctnbr, acctbal from
vhamberg.ACCOUNTS where acctbal>0)
,DEF as
 (select custnbr, custname, acctbal from vhamberg.CUSTOMERS
  join vhamberg.ACCOUNTS on acctnbr = custnbr )
select custnbr, custname, acctbal
from DEF
order by acctbal desc, custnbr

That got an error that ORDER was invalid, which lines up with my 
understanding that we can't use ORDER BY in the SELECT in CREATE VIEW.

Another question - do you mean to use the common table expression ABC in 
the JOIN of DEF - makes sense if that's the case - that use of CTEs is 
useful, I think, because it helps to document more complex statements. 
You are looking at accounts with positive balance - this could all be 
done in a single JOIN - of course, the more complex, the more convoluted 
these things can become.

Thanks
Vern

On 5/2/2024 8:35 AM, Alan (cfuture) Cassidy wrote:
> I have created a few complex views joining several files.
> In the view, you do a "common table expression", which I prefer to all a "WITH clause", in which you can use all the join-on lists you need, and then feed that into your "main" clause where you can ORDER BY any combination of columns.
>
> For example, hope it's simple enough:
> WITH ABC as
>   (select acctnbr, acctbal from ACCOUNTS where acctbal>0)
> .DEF as
>   (select custnbr, custname, acctbal from CUSTOMERS
>    join ACCOUNTS on acctnbr = custnbr )
> select custnbr, custname, acctbal
> from DEF
> order by  acctbal desc, custnbr ;;
>
>
>
> > On 05/01/2024 6:35 PM EDT Mark Waterbury <mark.s.waterbury@xxxxxxxxxxxxx> wrote:
> >
> >   
> > Even with a DDS Join Logical file, I don't think it can have "composite" keys with fields from both files.   You can however construct something like that with OPNQRYF, since it creates an access path under the covers on the fly.
> >
> >      On Wednesday, May 1, 2024 at 06:30:44 PM EDT, Therrien, Paul via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx> wrote:
> >   
> >   ?Tim wants to create an Index over a view that joins one table with another table?
> > That doesn't sound feasible with SQL.
> > You could do it with a join LF and DDS, but SQL does haven't that animal in its bestiary...I don't think.
> >
> > -----Original Message-----
> > From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of VERNON HAMBERG Owner via MIDRANGE-L
> > Sent: Wednesday, May 1, 2024 6:20 PM
> > To: midrange-l@xxxxxxxxxxxxxxxxxx
> > Cc: VERNON HAMBERG Owner <vhamberg@xxxxxxxxxxxxxxx>; midrange-l@xxxxxxxxxxxxxxxxxx
> > Subject: Re: Has anyone ever created an Index Table that included a JOIN table?
> >
> > Hi Tim
> >
> >
> > What is an Index Table? And while I'm at it, I'm not sure about a JOIN table - what is that? I just looked these terms up and don't think I've heard them in relation (so-to-speak) on IBM i.
> >
> >
> > Regards
> > Vern
> >
> >
> > On Wed, 1 May, 2024 at 4:14 PM, Timothy Feldmeier <Timothy.Feldmeier@xxxxxxxx> wrote:
> >   
> >
> > To: midrange-l@xxxxxxxxxxxxxxxxxx
> >
> > Can this be done?
> >
> > Thank You
> >
> > Tim Feldmeier
> > Programmer Analyst, Applications Development
> > 1431 Opus Place, Suite 530 | Downers Grove, IL 60515
> > Email:  timothy.feldmeier@xxxxxxxx<mailto:timothy.feldmeier@xxxxxxxx><mailto:timothy.feldmeier@xxxxxxxx<mailto:timothy.feldmeier@xxxxxxxx>>
> > Phone: (331) 233-7921
> >
> >   [Email Logo]
> >
> > [Graphical user interface, website  Description automatically generated]<https://www.shiproadrunnerfreight.com/newsweek-recognizes-roadrunner-as-one-of-americas-most-trustworthy-companies-2022><https://www.shiproadrunnerfreight.com/newsweek-recognizes-roadrunner-as-one-of-americas-most-trustworthy-companies-2022/>>
> >
> > The information transmitted is intended only for the person or entity to which it is addressed and may contain proprietary, business-confidential and/or privileged material. If you are not the intended recipient of this message you are hereby notified that any use, review, retransmission, dissemination, distribution, reproduction or any action taken in reliance upon this message is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto: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<mailto: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<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
> >
> > .
> > --
> > 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.
> >
> > --
> > 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.
> >
> >    
> > --
> > 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.