The vendor 5A field is changed to the vendor 6A field by the CHGPF so vendor 5A no longer exists. There are not two vendor fields.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan Shore via MIDRANGE-L
Sent: Thursday, February 25, 2021 9:52 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: Need some help with SQL case/when performance
Put an extra case in and look at the whole field being spaces, leave it as spaces Vendor = case
When Vendor6a = ' ' then Vendor6a = ' '
when vendor = ' ' then ' ' else '0' || vendor end
or something like that
Where Vendor6a is the 6 character field
And I am assuming vendor is the leading character of the 6 character field
Alan Shore
Solutions Architect
IT Supply Chain Execution
[cid:image001.png@01D70B5B.E9E0C3F0]
60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx
'If you're going through hell, keep going.'
Winston Churchill
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Kevin Bucknum
Sent: Thursday, February 25, 2021 8:54 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] Re: Need some help with SQL case/when performance
Two pass it? First pass slug the 0. Second pass blank out any with just zero? Make 5 runs updating one field at a time with a where clause?
On Thu, 2021-02-25 at 08:48 -0500, smith5646midrange@xxxxxxxxx<mailto:smith5646midrange@xxxxxxxxx> wrote:
We are converting files and I have a BIG performance problem.
The files are being converted via CHGPF. Some of the fields contain numeric
values stored in alpha fields. The client does not like to use null capable
fields so I think they did this to identify "no value". I also know this
file is pretty old so null might not have been widely used back when it was
created.
For an example of the data.our 5 digit vendor is stored in a 5A field.
After the CHGPF, it is a 6A field so the vendor is stored as "12345 ". I am
running an SQL that inserts a 0 in front so it will be "012345". However,
not all vendors have a value. Some are blank and we don't want to create
the value "0 ".
I used the logic (forgive the syntax if it is wrong)
Vendor = case when vendor = ' ' then ' ' else '0' || vendor end
The problem that I am having is the performance on this seems horrible. We
have a file of 68M records and it has 5 of these types of fields. Therefore
the case/when exists 5 times in the update. The SQL update ran for about 12
hours. When I created an RPGLE with normal F Specs and reads and writes, it
ran in 20 minutes. I have run this several times on several different
machines and the performance seems similar on all runs.
The dilemma with this is the values in the records vary. Of the 5 fields,
how many of them in each record will be populated and which ones will vary.
So, my options as I saw them were either using the case/when or running 5
different SQL updates each updating a single field where a clause selecting
that field running over 68M rows each time. At the time, the case/when
seemed like the better option.
Does anyone have any thoughts on what might be going on that would cause
such an extreme difference between the SQL case/when and native RPGLE? Does
anyone have a different idea how to approach this using SQL? I can go back
to the RPG logic but it seems "old school".
This request is strictly for ideas on conversion. I can't change the field
types or allow null or change the programs that are using them.
Thanks in advance.
[
https://www.medtronsoftware.com/img/MedtronMinilogo.bmp<
https://www.medtronsoftware.com/img/MedtronMinilogo.bmp>] Kevin Bucknum Senior Programmer Analyst MEDDATA / MEDTRON
120 Innwood Drive
Covington LA 70433
Local: 985-893-2550
Toll Free: 877-893-2550
https://www.medtronsoftware.com<
https://www.medtronsoftware.com>
CONFIDENTIALITY NOTICE
This document and any accompanying this email transmission contain confidential information, belonging to the sender that is legally privileged. This information is intended only for the use of the individual or entity named above. The authorized recipient of this information is prohibited from disclosing this information to any other party and is required to destroy the information after its stated need has been fulfilled. If you are not the intended recipient, or the employee of agent responsible to deliver it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of these documents is STRICTLY PROHIBITED. If you have received this email in error, please notify the sender immediately to arrange for return or destruction of these documents.
--
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<
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<
https://archive.midrange.com/midrange-l>.
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com<
https://amazon.midrange.com>
The Nature’s Bounty Co. is now The Bountiful Company! Our email address will be changing from @nbty.com to @bountifulcompany.com. Please update your email address book so that important communications are not interrupted.
--
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.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
