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

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.