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