This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
Maximo 7.5 DB2 10.1
Can someone help me understand why the following KPI is returning '0'?
select (select count(*) from workorder where status = 'WAPPR' and lr_doctype = 'DR' and siteid = 'WEST_MIRA' ) / (select count(*) from workorder where lr_doctype = 'DR' and siteid = 'WEST_MIRA') * 100 from dummy_table
The following are the individual query results:
select count(*) from workorder where status = 'WAPPR' and lr_doctype = 'DR' and siteid = 'WEST_MIRA'
(48 records)
select count(*) from workorder where lr_doctype = 'DR' and siteid = 'WEST_MIRA'
(54 records)
I am not quite sure how SQL sometimes calculates....but the following code example might produce a numerical answer for you:
SELECT convert(float,(select count(*) from workorder where status = 'WAPPR' and worktype = 'CM' )) /
convert(float,(select count(*) from workorder where worktype = 'CM' )) * 100 from dummy_table;
w/br
John Reeve
Thanks John, long time no chat.
I tried the following but it throws the error when trying to update the KPI:
BMXAA2485E - The KPI could not be run successfully due to an unexpected error. Please contact your system administrator.
SELECT convert(float,(select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and (status = 'COMP' and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(lr_doctype) = 'DR' and historyflag = 0 and istask = 0))) / convert(float,(select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(lr_doctype) = 'DR' and historyflag = 0 and istask = 0)) * 100 from dummy_table
Best regards,
Mike Levinson
What does your original query return when you run it in a SQL tool?
On Tue, Jan 28, 2014 at 12:10 PM, <mlevinson@ledgesoft.com> wrote:
>
>
> Thanks John, long time no chat.
>
> I tried the following but it throws the error when trying to update the
> KPI:
> BMXAA2485E - The KPI could not be run successfully due to an unexpected
> error. Please contact your system administrator.
>
> SELECT convert(float,(select count(*) from workorder where siteid =
> (select defsite from maxuser where userid = :USER) and (status = 'COMP'
> and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(lr_doctype) =
> 'DR' and historyflag = 0 and istask = 0))) / convert(float,(select count(*)
> from workorder where siteid = (select defsite from maxuser where userid =
> :USER) and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and
> upper(lr_doctype) = 'DR' and historyflag = 0 and istask = 0)) * 100 from
> dummy_table
>
>
> Best regards,
>
> Mike Levinson
>
>
Chris,
No matter what I tried I could not get the query to run in the DB2 SQL tool. I originally tested this in my local VM (Oracle DB) and it worked but I am new to DB2 and am having trouble getting any syntax to run correctly in DB2 ( I am new to DB2).
If its anything like sql server then try this
select (select count(*) from workorder where status = 'WAPPR' and lr_doctype = 'DR' and siteid = 'WEST_MIRA' ) *1.0 / (select count(*) from workorder where lr_doctype = 'DR' and siteid = 'WEST_MIRA') * 100 from dummy_table
Rgds Ian
BPD Zenith Ltd
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of mlevinson@ledgesoft.com
Sent: 28 January 2014 19:08
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Maximo pecentage KPI
Maximo 7.5 DB2 10.1
Can someone help me understand why the following KPI is returning '0'?
select (select count(*) from workorder where status = 'WAPPR' and lr_doctype = 'DR' and siteid = 'WEST_MIRA' ) / (select count(*) from workorder where lr_doctype = 'DR' and siteid = 'WEST_MIRA') * 100 from dummy_table
The following are the individual query results:
select count(*) from workorder where status = 'WAPPR' and lr_doctype = 'DR' and siteid = 'WEST_MIRA'
(48 records)
select count(*) from workorder where lr_doctype = 'DR' and siteid = 'WEST_MIRA'
(54 records)
GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N 2PB.
**************************************************************************************************************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
**************************************************************************************************************
It could be that you are getting division by zero
Use
SELECT convert(float,(select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and (status = 'COMP' and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(lr_doctype) = 'DR' and historyflag = 0 and istask = 0)))
/ nullif((convert(float,(select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(lr_doctype) = 'DR' and historyflag = 0 and istask = 0))),0) * 100 from dummy_table
Also note the use of
where siteid = (select defsite from maxuser where userid = :USER)
will result in only one site being selected that of the user running the kpicron task this may be the result you want
Rgds Ian
BPD Zenith Ltd
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of mlevinson@ledgesoft.com
Sent: 28 January 2014 20:10
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] RE: Maximo pecentage KPI
Thanks John, long time no chat.
I tried the following but it throws the error when trying to update the KPI:
BMXAA2485E - The KPI could not be run successfully due to an unexpected error. Please contact your system administrator.
SELECT convert(float,(select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and (status = 'COMP' and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(lr_doctype) = 'DR' and historyflag = 0 and istask = 0))) / convert(float,(select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(lr_doctype) = 'DR' and historyflag = 0 and istask = 0)) * 100 from dummy_table
Best regards,
Mike Levinson
GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N 2PB.
**************************************************************************************************************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
**************************************************************************************************************
I think :USER and :&USERNAME& are interchangeable but if its not a division by zero issue then you could try changing the values
Ps note I ran the below code against v7.5 sql server and it was fine I substituted
‘MAXADMIN’ for :USER
And upper(lr_doctype) = 'DR' for upper(worktype) = 'PM'
In my previous post I suggested using *1.0 which is a lot easier than convert(float,(select…..)), having read the DB2 manual I think it should work i.e. if you multiply a decimal by an integer it returns a decimal so you only have to do it once i.e. on the first select
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of Ian Wright
Sent: 29 January 2014 09:00
To: MAXIMO@yahoogroups.com
Subject: RE: [MAXIMO List] RE: Maximo pecentage KPI
It could be that you are getting division by zero
Use
SELECT convert(float,(select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and (status = 'COMP' and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(lr_doctype) = 'DR' and historyflag = 0 and istask = 0)))
/ nullif((convert(float,(select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(lr_doctype) = 'DR' and historyflag = 0 and istask = 0))),0) * 100 from dummy_table
Also note the use of
where siteid = (select defsite from maxuser where userid = :USER)
will result in only one site being selected that of the user running the kpicron task this may be the result you want
Rgds Ian
BPD Zenith Ltd
From: MAXIMO@yahoogroups.com<mailto:MAXIMO@yahoogroups.com> [mailto:MAXIMO@yahoogroups.com] On Behalf Of mlevinson@ledgesoft.com<mailto:mlevinson@ledgesoft.com>
Sent: 28 January 2014 20:10
To: MAXIMO@yahoogroups.com<mailto:MAXIMO@yahoogroups.com>
Subject: [MAXIMO List] RE: Maximo pecentage KPI
Thanks John, long time no chat.
I tried the following but it throws the error when trying to update the KPI:
BMXAA2485E - The KPI could not be run successfully due to an unexpected error. Please contact your system administrator.
SELECT convert(float,(select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and (status = 'COMP' and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(lr_doctype) = 'DR' and historyflag = 0 and istask = 0))) / convert(float,(select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and (woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(lr_doctype) = 'DR' and historyflag = 0 and istask = 0)) * 100 from dummy_table
Best regards,
Mike Levinson
GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N 2PB.
****************************************************************************************************************************************************************************************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
****************************************************************************************************************************************************************************************
Ian,
I cannot say thank you enough!!!! This worked perfectly:
select (select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and status = 'COMP' and lr_doctype = 'DR') *1.0 / (select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and lr_doctype = 'DR') * 100 from dummy_table
Remember it will still fail if there is a division by zero see one of my later posts for solution
Glad to help
Ian
BPD Zenith Ltd
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of mlevinson@ledgesoft.com
Sent: 29 January 2014 13:32
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] RE: Maximo pecentage KPI
Ian,
I cannot say thank you enough!!!! This worked perfectly:
select (select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and status = 'COMP' and lr_doctype = 'DR') *1.0 / (select count(*) from workorder where siteid = (select defsite from maxuser where userid = :USER) and lr_doctype = 'DR') * 100 from dummy_table
GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N 2PB.
**************************************************************************************************************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
**************************************************************************************************************