RE: In SQL - which isquicker

Maybe, maybe not. You could run both with the debug turned on and see what
the optimizer says.
Remember sometimes changing the predicates around on the query changes the
optimization.


--
Jim Oberholtzer
Agile Technology Architects


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan
Shore
Sent: Friday, May 26, 2017 10:49 AM
To: Midrange Systems Technical Discussion
Subject: RE: In SQL - which isquicker

Thanks for your reply Luis
I understand what you are saying - but will using the CTE be quicker/more
efficient than what I already have?

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@xxxxxxxxxxxx] On Behalf Of Luis
Rodriguez
Sent: Friday, May 26, 2017 11:34 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: In SQL - which isquicker

Alan,

Why not simply change your query to a CTE? Something like:

Create table QTEMP.ORDHDRPZ as (
WITH T1 as
(SELECT *
FROM prodfa.ordhdrp b
join prodfa.custcompny z on
b.bcmpy = z.custcmpny
where b.HUFL03 <> 'RE' and b.status not in ('RC', 'RX', 'VC', 'VX') and
iDate((b.Orddte - 72),'*MDY')

= (current_date - 5 year))

SELECT distinct A.MCST#
FROM prodfa.mediap a join T1 b
on a.mdord# = digits(b.bammdd) concat
digits(b.bayy) concat digits(b.baxxx) concat b.ordnum

) With data ;

Regards,
Luis





On Fri, May 26, 2017 at 11:24 AM Alan Shore <ashore@xxxxxxxx> wrote:

Hi everyone
Before I forget - we are on V7r1
I hope my question makes sense

I have this SQL query

create table QTEMP.MEDI5YEARS as
(SELECT distinct A.MCST#
FROM prodfa.mediap a join prodfa.ordhdrp b on a.mdord# =
digits(b.bammdd) concat
digits(b.bayy) concat digits(b.baxxx) concat b.ordnum join
prodfa.custcompny z on b.bcmpy = z.custcmpny where b.HUFL03 <> 'RE'
and b.status not in ('RC', 'RX', 'VC', 'VX') and iDate((b.Orddte -
72),'*MDY')

= (current_date - 5 year))

with data

By splitting this script into 2 (or more scripts), would THIS be
quicker/more efficient - by creating a smaller work file to join with

create table QTEMP.ORDHDRPZ as
(SELECT *
FROM prodfa.ordhdrp b
join prodfa.custcompny z on
b.bcmpy = z.custcmpny
where b.HUFL03 <> 'RE' and b.status not in ('RC', 'RX', 'VC', 'VX')
and iDate((b.Orddte - 72),'*MDY')

= (current_date - 5 year))

with data;

create table QTEMP.MEDI5YEARS as
(SELECT distinct A.MCST#
FROM prodfa.mediap a join QTEMP.ordhdrpz b on a.mdord# =
digits(b.bammdd) concat
digits(b.bayy) concat digits(b.baxxx) concat b.ordnum) with data;




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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://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: http://amzn.to/2dEadiD