|
On Apr 3, 2017, at 12:59 PM, midrange-l-request@xxxxxxxxxxxx wrote:
Send MIDRANGE-L mailing list submissions to
midrange-l@xxxxxxxxxxxx
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/midrange-l
or, via email, send a message with subject or body 'help' to
midrange-l-request@xxxxxxxxxxxx
You can reach the person managing the list at
midrange-l-owner@xxxxxxxxxxxx
When replying, please edit your Subject line so it is more specific
than "Re: Contents of MIDRANGE-L digest..."
*** NOTE: When replying to this digest message, PLEASE remove all text unrelated to your reply and change the subject line so it is meaningful.
Today's Topics:
1. RE: Looking for IBM i folks in the Edmonton Alberta area
(Englander, Douglas)
2. Re: Looking for IBM i folks in the Edmonton Alberta area
(Jon Paris)
3. sql round conundrum (Smith, Mike)
4. RE: sql round conundrum (Birgitta Hauser)
5. Re: Re: SQL UDF to return one column from a row
(Mark Murphy/STAR BASE Consulting Inc.)
6. RE: sql round conundrum (Smith, Mike)
7. RE: sql round conundrum (Rob Berendt)
----------------------------------------------------------------------
message: 1
date: Mon, 3 Apr 2017 12:46:24 +0000
from: "Englander, Douglas" <Douglas_Englander@xxxxxxxxxxxxx>
subject: RE: Looking for IBM i folks in the Edmonton Alberta area
Jon,
You may also try the "Midrange Consult400" or "Midrange-Jobs" site to post your position, depending on what you're looking for.
Doug
------------------------------
message: 2
date: Mon, 3 Apr 2017 10:42:47 -0400
from: Jon Paris <jon.paris@xxxxxxxxxxxxxx>
subject: Re: Looking for IBM i folks in the Edmonton Alberta area
I already suggested that they try that Doug. If it hasn't appeared then ...
I'm only very peripherally involved in this.
Jon Paris
www.partner400.com
www.SystemiDeveloper.com
On Apr 3, 2017, at 8:46 AM, Englander, Douglas <Douglas_Englander@xxxxxxxxxxxxx> wrote:
Jon,
You may also try the "Midrange Consult400" or "Midrange-Jobs" site to post your position, depending on what you're looking for.
Doug
--
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
------------------------------
message: 3
date: Mon, 3 Apr 2017 15:34:16 +0000
from: "Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx>
subject: sql round conundrum
I have 2 machines both on 7.1, but dev machine is slightly ahead on PTFS.
I have run a SQL round statement
UPDATE MYLIB/WTAVGS SET WTOSSHR = round(wtosshr * 1.5)
It successfully updated 409 records.
However when I run this on my production machine, I get a message that
Number of arguments for function ROUND not valid.
Short of getting the PTFs matching on the machines because I need to get this file updated today.
How might I modify the round to match the results from my dev box?
Thanks
Mike
NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.
------------------------------
message: 4
date: Mon, 3 Apr 2017 17:44:49 +0200
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: RE: sql round conundrum
What Releases are you on? ROUND had always 2 parameters, the expressions and
the number of decimal positions.
That you can specify only a single parameter was not introduced before one
of the latest TRs.
An other reason could be that the decimal comma (like in Europe) instead of
a decimal point is used within your job.
Mit freundlichen Gr??en / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Smith, Mike
Sent: Montag, 3. April 2017 17:34
To: Midrange Systems Technical Discussion (midrange-l@xxxxxxxxxxxx)
<midrange-l@xxxxxxxxxxxx>
Subject: sql round conundrum
I have 2 machines both on 7.1, but dev machine is slightly ahead on PTFS.
I have run a SQL round statement
UPDATE MYLIB/WTAVGS SET WTOSSHR = round(wtosshr * 1.5)
It successfully updated 409 records.
However when I run this on my production machine, I get a message that
Number of arguments for function ROUND not valid.
Short of getting the PTFs matching on the machines because I need to get
this file updated today.
How might I modify the round to match the results from my dev box?
Thanks
Mike
NOTICE: This message, including any attachment, is intended as a
confidential and privileged communication. If you have received this message
in error, or are not the named recipient(s), please immediately notify the
sender and delete this message.
--
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
------------------------------
message: 5
date: Mon, 3 Apr 2017 12:03:02 -0400
from: "Mark Murphy/STAR BASE Consulting Inc."
<mmurphy@xxxxxxxxxxxxxxx>
subject: Re: Re: SQL UDF to return one column from a row
I think in this case you are correct, but not in every case. I have a set of UDTF's that calculate bill of materials for a part because using a view performed exceedingly poorly. With the UDTF I can pass in a part that I want the BOM generated for, using the View, it tries to calculate all possible BOM's. The UDTF is way faster.
Mark Murphy
Atlas Data Systems
mmurphy@xxxxxxxxxxxxxxx
-----Mike Jones <mike.jones.sysdev@xxxxxxxxx> wrote: -----
To: midrange-l@xxxxxxxxxxxx
From: Mike Jones <mike.jones.sysdev@xxxxxxxxx>
Date: 03/31/2017 02:03PM
Subject: Re: SQL UDF to return one column from a row
To simplify queries and joins used repeatedly, views should be your primary
solution object of choice.
I dabbled a bit with UDFs that perform I/Os, and can confirm the often
dramatically worse performance, especially when large amounts of data are
present in the table(s) referenced. I would never again use a scalar UDF
that performs I/Os.
I have one or two, rarely used, table UDFs that perform I/Os over a small
table that I wrote a long time ago, that I used as a last resort. But, I
really should review them to see if I really needed a table UDF. They do
perform slowly.
Scalar UDFs that don't perform I/Os, thereby taking parameters inputs,
performing a calculation on them, and returning the result (without
performing I/Os), perform very well.
I agree 100% with Birgitta on this: "UDFs perform normally worse than
joins", but then again, I agree with her advice virtually every time.
Mike
date: Fri, 31 Mar 2017 07:27:06 +0200--
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: RE: SQL UDF to return one column from a row
What About:
Create View YourFirstView
As (Select a.*, yourText
From yourFile a Join YourTextFile on ....);
Create View NextView
As (Select *
From YourFirstView ....)
Then use the view you need (no need to code anything over and over again).
UDFs perform normally worse than joins. A UDF is for the query optimizer a
black box.
Joins are visible to the query optimizer, independent whether they are
entered directly or whether they are located in even nested views.
In this way the query optimizer can find better solutions when using views
contrary to UDFs.
Example: If you have a UDF that returns the customer name.
With an UDF a "chain" must be performed for each row. If the same customer
is returned in multiple rows, the UDF is called for each row.
If a JOIN is used instead, the optimizer might be able to find an access
plan that reads the customer information only once.
Mit freundlichen Gr??en / Best regards
Birgitta Hauser
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
------------------------------
message: 6
date: Mon, 3 Apr 2017 16:04:32 +0000
from: "Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx>
subject: RE: sql round conundrum
7.1 on both machines.
SF99701 41 on DEV
SF99701 38 on Prod.
I think I have it working
I changed it to UPDATE MYLIB/WTAVGS SET WTOSSHR = round(wtosshr * 1.5,0)
The field I'm updating is 9.0
I'm doing comparisons, but so far every thing is matching.
Thanks
Mike
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Monday, April 03, 2017 11:45 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Subject: RE: sql round conundrum
What Releases are you on? ROUND had always 2 parameters, the expressions and the number of decimal positions.
That you can specify only a single parameter was not introduced before one of the latest TRs.
An other reason could be that the decimal comma (like in Europe) instead of a decimal point is used within your job.
Mit freundlichen Gr??en / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Smith, Mike
Sent: Montag, 3. April 2017 17:34
To: Midrange Systems Technical Discussion (midrange-l@xxxxxxxxxxxx) <midrange-l@xxxxxxxxxxxx>
Subject: sql round conundrum
I have 2 machines both on 7.1, but dev machine is slightly ahead on PTFS.
I have run a SQL round statement
UPDATE MYLIB/WTAVGS SET WTOSSHR = round(wtosshr * 1.5)
It successfully updated 409 records.
However when I run this on my production machine, I get a message that Number of arguments for function ROUND not valid.
Short of getting the PTFs matching on the machines because I need to get this file updated today.
How might I modify the round to match the results from my dev box?
Thanks
Mike
NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.
--
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
--
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
NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.
------------------------------
message: 7
date: Mon, 3 Apr 2017 12:58:52 -0400
from: Rob Berendt <rob@xxxxxxxxx>
subject: RE: sql round conundrum
Birgitta,
Actually ROUND does not require 2 parameters.
Select Message_id, Message_type, severity, message_text,
round(severity * 1.23)
from table(qsys2.joblog_info('*')) a
;
Since qsys2.joblog_info is available at 7.1 since DB2 group ptf 32 you can
test it there.
http://ibm.biz/DB2foriServices
"If expression?2 is not specified, expression?1 is rounded to zero places
to the left of the decimal point."
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzscaround.htm
Note: That is surrounded by change markers to indicate it's something of
a recent change.
Here's the gospel:
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/DB2%20for%20i%20Functional%20Enhancements
What's new with IBM i 7.3
and IBM i 7.2 SF99702 Level 11,
and IBM i 7.1 SF99701 Level 40:
Expression-2 is optional
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/ROUND%28%29%20built-in%20function%20-%20enhanced
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 04/03/2017 11:45 AM
Subject: RE: sql round conundrum
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
What Releases are you on? ROUND had always 2 parameters, the expressions
and
the number of decimal positions.
That you can specify only a single parameter was not introduced before one
of the latest TRs.
An other reason could be that the decimal comma (like in Europe) instead
of
a decimal point is used within your job.
Mit freundlichen Gr??en / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so
they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Smith, Mike
Sent: Montag, 3. April 2017 17:34
To: Midrange Systems Technical Discussion (midrange-l@xxxxxxxxxxxx)
<midrange-l@xxxxxxxxxxxx>
Subject: sql round conundrum
I have 2 machines both on 7.1, but dev machine is slightly ahead on
PTFS.
I have run a SQL round statement
UPDATE MYLIB/WTAVGS SET WTOSSHR = round(wtosshr * 1.5)
It successfully updated 409 records.
However when I run this on my production machine, I get a message that
Number of arguments for function ROUND not valid.
Short of getting the PTFs matching on the machines because I need to get
this file updated today.
How might I modify the round to match the results from my dev box?
Thanks
Mike
NOTICE: This message, including any attachment, is intended as a
confidential and privileged communication. If you have received this
message
in error, or are not the named recipient(s), please immediately notify the
sender and delete this message.
--
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
--
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
------------------------------
Subject: Digest Footer
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) digest 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
------------------------------
End of MIDRANGE-L Digest, Vol 16, Issue 613
*******************************************
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
