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

:) I won't tell anyone, Alan! Sorry about misreading the comma, I'll blame in on my email client, Thunderbird.

I do think this is a good example of being easier to understand details more easily when enclosed in common table expressions, I prefer them to nested table expressions much of the time.

Cheers
Vern

On 5/4/2024 9:55 AM, Alan (cfuture) Cassidy wrote:

Good questions, Vern! I look at that and ask myself what I was thinking. And I believe a view does not have an ORDER BY clause. I hope our resident SQL guru doesn't see that!

That's not a period before the DEF, it's a comma. And I probably was intending to do a JOIN.

SO disregard this one..

On 05/03/2024 8:50 AM EDT Vern Hamberg via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

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.

--
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.