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



Yes - that was the second suggestion, and the better of the two. I should have just removed the first one.

On Thu, 2021-02-25 at 09:40 -0500, smith5646midrange@xxxxxxxxx wrote:

Understand that with this question, I am trying to understand the logic.

I've worked a lot of hours over the last 4 days so I'm not sure I am

thinking straight anymore. :(


Wouldn't it be more efficient to just run 5 SQL statements like the

following instead of the 6 discussed earlier?


Update file set vendor = '0'||vendor where vendor <> ' '




-----Original Message-----

From: MIDRANGE-L <

<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

midrange-l-bounces@xxxxxxxxxxxxxxxxxx

On Behalf Of Kevin

Bucknum

Sent: Thursday, February 25, 2021 9:27 AM

To:

<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

midrange-l@xxxxxxxxxxxxxxxxxx


Subject: Re: Need some help with SQL case/when performance


You are correct. I thought about that after I typed the second part. So 6

passes or 5. But each pass should be more of a "set" transaction, and

hopefully will run faster. I've had good luck before when I had multiple

ways to reformat data based on contents with making multiple passes instead

of one all encompassing pass.


On Thu, 2021-02-25 at 09:20 -0500,

<mailto:smith5646midrange@xxxxxxxxx>

smith5646midrange@xxxxxxxxx

wrote:


I'm not understanding your two passes?



I assume that you mean pass 1 will prefix the 0 on all 5 fields whether it


is blank or not and pass 2 will clean up what have a value of a single 0


only ("0 ") but I can't do that with a single SQL so I have to run 5


SQLs to clean up the single 0 so now I am running 6 passes...


Update to set all fields with the 0 prefix


Update file set vendor = ' ' where vendor = '0 '.


Update file set maxinv = ' ' where maxinv = '0 '.


And 3 more, one for each remaining field.



What did I miss?



-----Original Message-----


From: MIDRANGE-L <


<mailto:

<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

midrange-l-bounces@xxxxxxxxxxxxxxxxxx




<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

midrange-l-bounces@xxxxxxxxxxxxxxxxxx



On Behalf Of Kevin


Bucknum


Sent: Thursday, February 25, 2021 8:54 AM


To:


<mailto:

<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

midrange-l@xxxxxxxxxxxxxxxxxx




<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

midrange-l@xxxxxxxxxxxxxxxxxx




Subject: 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,


<mailto:

<mailto:smith5646midrange@xxxxxxxxx>

smith5646midrange@xxxxxxxxx




<mailto:smith5646midrange@xxxxxxxxx>

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




<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




<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:


<mailto:

<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx




<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

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




<https://lists.midrange.com/mailman/listinfo/midrange-l>

https://lists.midrange.com/mailman/listinfo/midrange-l




or email:


<mailto:

<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx




<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

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




<https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l



.



Please contact


<mailto:

<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx




<mailto:support@xxxxxxxxxxxxxxxxxxxx>

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




<https://amazon.midrange.com>

https://amazon.midrange.com







[

<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:

<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

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:

<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

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

<mailto:support@xxxxxxxxxxxxxxxxxxxx>

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





[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



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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.