Re: Combining SQL Updates

second run?

If this statement will run once and a while, the first run after clearing
the tables from memory would give the best indicator of performance.

If this statement will run repeatedly, then the third run (by default)
would give you a better indication of performance with an ODP...

if this is a one-time thing, then go with whatever is easier for you to
write. :)

I still think there's a better solution, if I get a chance I'll look
deeper...

Charles

On Tue, Jan 30, 2018 at 1:45 PM, <dlclark@xxxxxxxxxxxxxxxx> wrote:

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 01/29/2018
05:58:46 PM:

Try this, it might work but obviously I cannot test it on my system.

Update SOOSH01T O
Set SOWFSTSTID = case when (Select 1 from SOOSH00V03 I Where
O.SOOSH01TID = I.SOOSH01TID and SHPSTSCDE = 'Closed') = 1 then
(Select SOWFSTSTID from SOWFSTST Where WRKFLWCDE = 'DELIVERED')
when (Select 1 from SOOSH00V03 I Where
O.SOOSH01TID = I.SOOSH01TID and SHPSTSCDE <> 'Closed') = 1 then
(Select SOWFSTSTID from SOWFSTST Where WRKFLWCDE = 'WAREHOUSEREADY')
else SOWFSTSTID end

Thanks. I did get that to work (see after statistics). But I
thought I'd check with Visual Explain and, with two runs of each
statement, here are the results from the second run of each statement.
So, what do y'all think? Better to keep it simple even with more than one
pass of the file? Or, better to be more complex as long as you can do it
all in one statement?


Visual Explain Closed Not Closed Totaled AllInOne
---------------------- ------ ---------- ------- --------
Optimization Time (ms) 958 919 1,877 1,881
Run Time (ms) 380 231 611 882
Rows Fetched 1,014 191 1,205 1,205
CPU Time 8 3 11 18


Update SOOSH01T as Mst
Set Mst.SOWFSTSTID =
Case When (Select 1 from SOOSH00V03 as Sub
Where Sub.SOOSH01TID = Mst.SOOSH01TID
and Sub.SHPSTSCDE = 'Closed') = 1
Then (Select Lku.SOWFSTSTID from SOWFSTST as Lku
Where Lku.WRKFLWCDE = 'DELIVERED')
When (Select 1 from SOOSH00V03 as Sub
Where Sub.SOOSH01TID = Mst.SOOSH01TID
and Sub.SHPSTSCDE <> 'Closed') = 1
Then (Select Lku.SOWFSTSTID from SOWFSTST as Lku
Where Lku.WRKFLWCDE = 'WAREHOUSEREADY')
Else Mst.SOWFSTSTID
End
With NC;


Sincerely,

Dave Clark
--
int.ext: 91078
direct: (937) 531-6378
home: (937) 751-3300

Winsupply Group Services
3110 Kettering Boulevard
Dayton, Ohio 45439 USA
(937) 294-5331




************************************************************
*********************************
This email message and any attachments is for use only by the named
addressee(s) and may contain confidential, privileged and/or proprietary
information. If you have received this message in error, please
immediately notify the sender and delete and destroy the message and all
copies. All unauthorized direct or indirect use or disclosure of this
message is strictly prohibited. No right to confidentiality or privilege
is waived or lost by any error in transmission.
************************************************************
*********************************
--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
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: http://amzn.to/2dEadiD