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



I've found that instead of do a =select max... type logic, that
pre-selecting the max records results in much faster performance. I
changed a query from:

select
a.ediname, a.edists, max(a.edits)
from prod_mod/edistspf a
where a.edits =
(select max(z.edits) from prod_mod/edistspf z
where a.ediname=z.ediname)
group by ediname, edists
order by ediname


to


select
a.ediname, a.edists, a.edits
from prod_dta/edistspf a
inner join (select ediname, max(edits) as lastdate
from prod_dta/edistspf group by ediname) lastping
on a.ediname=lastping.ediname and a.edits=lastping.lastdate
order by a.ediname



This greatly reduced the query runtime.



Loyd Goodbar
Senior programmer/analyst
BorgWarner
TS Water Valley
662-473-5713
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Saturday, September 15, 2007 12:39
To: 'Midrange Systems Technical Discussion'
Subject: RE: Need ideas for SQL join select to replace logical view

From: bill.blalock@xxxxxxxx

Hi Joe:

The SQL you suggested didn't quiet do the job when I tested it under
query
manager. It started looping and reprocessing records. I am still
working
with it.

Actually, Bill, it works fine for me. It just takes a LOOOONG time
because
of the nature of the query. Here's what worked:

select I_ID, I_EVENT, I_TMSP, coalesce(O_OWNER, '*NONE')
from invent left outer join owner on I_ID = O_ID
where O_TMSP = (select MAX(O_TMSP) from owner
where O_ID = I_ID and O_TMSP <= I_TMSP)

I named my tables OWNER and INVENT, and I called my control number field
ID
and my status field EVENT.


O_ID O_OWNER O_TMSP
1 JOE 80915120100
1 BILL 80915121000

I_ID I_EVENT I_TMSP
1 EVENT 1 80915120200
1 EVENT 2 80915120300
1 EVENT 3 80915120400
1 EVENT 4 80915121200
1 EVENT 5 80915121300


I get this:

I_ID I_EVENT I_TMSP COALESCE
1 EVENT 1 80,915,120,200 JOE
1 EVENT 2 80,915,120,300 JOE
1 EVENT 3 80,915,120,400 JOE
1 EVENT 4 80,915,121,200 BILL
1 EVENT 5 80,915,121,300 BILL

Exactly what you wanted. I do have an issue that the LEFT OUTER JOIN
isn't
working quite the way I thought it would, and frankly I don't have a
good
answer for it. Without going into a lot of detail, the only way around
this
that I can find is using a CTE with a dummy first record UNIONed to the
OWNER file.

I suppose the easier way would just be an exception SELECT:

Select * from INVENT where I_TMSP <
(SELECT MIN(O_TMSP) FROM OWNER WHERE I_ID = O_ID)

Then UNION the results of that with the original. Man this stuff gets
ugly
quick. I hope an SQL expert can show us a better way!


Building a work file to represent the data needed is very easy with
RPG
for this problem. Using level breaks and matching records is even
less IO
than my proposed solution.

Yup. It's definitely the right tool for the job.


I am trying to wrap my head around SQL. This is a very basic problem
in
my inventory project ... matching the owner of an inventory item at
the
point in time with what happened to that item. In this case the
ownership
of the item is one process (hence the owner file) and what happens to
inventory is another process (hence the status file).

And it's not one that SQL is particularly well suited for: trying to
correlate non-matching tables. It can be done, of course, but if you
don't
have the right indexes in place, it could take a really long time.

Joe



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.