|
No, the child is not a temporal table. It has no index with the Status as
the key, but has an Index with the 4 keys which selects only 'A' status
records. Is system smart enough to use that index?
On Fri, Dec 20, 2019 at 10:31 AM Rob Berendt <rob@xxxxxxxxx> wrote:
Any chance that the child table is a temporal table?no
Is there something other than our traditional joining which may be better
on a temporal table? For example querying the main table for rows with
previous temporal ranges.wrote:
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Charles Wilt
Sent: Friday, December 20, 2019 10:15 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: Help with SQL
CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.
Join is going to be your best performance...
Ideally you'd want an index on child with (k1,k2,k3,k4, status)
Charles
On Fri, Dec 20, 2019 at 8:11 AM Vinay Gavankar <vinaygav@xxxxxxxxx>
specified
Thanks guys. That worked fine.
The problem was my pitiable ignorance that the Status could be
onextremely
the JOIN clause. I thought that the Join fields need to be present inboth
tables.
I would still appreciate any performance pointers, if any.
Vinay
On Fri, Dec 20, 2019 at 8:29 AM Rob Berendt <rob@xxxxxxxxx> wrote:
Actually that worked fine.
I do not pretend to be a DB2 performance maven. So, with his
Oflarge data I am not sure which would be the better performer.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf
notmidrange-l@xxxxxxxxxxxxxxxxxxAlan Shore via MIDRANGE-L
Sent: Friday, December 20, 2019 8:17 AM
To: Midrange Systems Technical Discussion <
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: Help with SQL
CAUTION: This email originated from outside of the organization. Do
'C',knowclick links or open attachments unless you recognize the sender and
midrange-l@xxxxxxxxxxxxxxxxxxthe content is safe.Behalf
So the following wouldn't work?
select p.k1, p.k2, p.k3, p.k4
from vkparent p exception join vkchild c on
p.k1=c.k1 and
p.k2=c.k2 and
p.k3=c.k3 and
p.k4=c.k4 and
c.status = 'A'
Cannot test this as I am in a meeting without access to the AS/400
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On
Of Rob Berendt
Sent: Friday, December 20, 2019 8:06 AM
To: Midrange Systems Technical Discussion <
Subject: [EXTERNAL] RE: Help with SQL
I think you're right. That STATUS column throws a wrinkle in it.
create table vkparent (
k1 char(1),
k2 char(1),
k3 char(1),
k4 char(1),
primary key(k1, k2, k3, k4)
)
;
create table vkchild (
k1 char(1),
k2 char(1),
k3 char(1),
k4 char(1),
status char(1),
k5 char(1),
d1 char(1),
primary key(k1, k2, k3, k4, k5)
);
insert into vkparent values('A', 'A', 'A', 'A'); insert into vkparent
values('B', 'B', 'B', 'B'); insert into vkparent values('C', 'C',
c.k4'D','C'); insert into vkparent values('D', 'D', 'D', 'D'); insert intovkchild
values('A', 'A', 'A', 'A', 'A', '1', 'X'); insert into vkchildvalues('B',
'B', 'B', 'B', 'I', '1', 'X'); insert into vkchild values('D', 'D',
'A','D', 'I', '1', 'X'); insert into vkchild values('D', 'D', 'D', 'D',
'2', 'Y');
select p.k1, p.k2, p.k3, p.k4
from vkparent p
where (p.k1, p.k2, p.k3, p.k4) not in ( select c.k1, c.k2, c.k3,
c.statuswantfrom vkchild c where c.status = 'A') ; This line won't get what you
because 'D' has both an active and an inactive row.
select p.k1, p.k2, p.k3, p.k4, c.status
from vkparent p left outer join vkchild c on
p.k1=c.k1 and p.k2=c.k2 and p.k3=c.k3 and p.k4=c.k4 where
isOf
null or c.status<>'A'
;
l
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf
notmidrange-l@xxxxxxxxxxxxxxxxxxVinay Gavankar
Sent: Friday, December 20, 2019 7:28 AM
To: Midrange Systems Technical Discussion <
Subject: Re: Help with SQL
CAUTION: This email originated from outside of the organization. Do
theknowclick links or open attachments unless you recognize the sender and
the content is safe.
I don't think the Left Outer Join or Exception join would work, as
theJOIN
would be on 4 keys, and the sql would return only the records where
isJOIN is not successful. So it would return only records where there
noK5
did.match in child table. Maybe I am wrong, but that is what my test sql
Of
On Fri, Dec 20, 2019 at 7:19 AM Rob Berendt <rob@xxxxxxxxx> wrote:
I did a simple google for sql list missing child rows
Basically it boiled down to a few choices between
NOT EXISTS
NOT IN
LEFT OUTER JOIN
EXCEPTION JOIN
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf
notmidrange-l@xxxxxxxxxxxxxxxxxxVinay Gavankar
Sent: Friday, December 20, 2019 7:14 AM
To: Midrange Systems Technical Discussion <
Subject: Help with SQL
CAUTION: This email originated from outside of the organization. Do
knowclick links or open attachments unless you recognize the sender and
the content is safe.million
Hi,
We have a Table with K1, K2, K3, K4 as Primary keys with about 800
records. A child table, which is partitioned, has K1, K2, K3, K4,
may(Seqabout
Number) as Primary keys and a Status field (value of A or I) with
10possible,
billion records. Child table having no record for the 4 keys is
though highly unlikely.Table,
I need to get a list of records (just the key fields) from the main
which has no record in the child table with Status = 'A' (may or
mailingnotmailing
this?have an 'I' Status record).
Can someone please help me with some efficient sql code to achieve
TIA.
Vinay
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
affiliatelist
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@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our
mailinglink: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
affiliatelist
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@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our
link: https://amazon.midrange.com--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
affiliatelist
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@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our
mailinglink: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
affiliatelist
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@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our
mailinglink: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
affiliatelist
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@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our
mailinglink: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
affiliatelist
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@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our
listlistlink: https://amazon.midrange.com--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxxlist
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@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
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@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
As an Amazon Associate we earn from qualifying purchases.
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.