× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. May 2024

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

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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.