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.
So I have a SQL query that Maximo-Actuate turns from this:
SELECT po.siteid, SUM(po.totalcost) totalcost, COUNT(po.ponum), NVL(po.po3,'none') bidwaiver
FROM po
WHERE po.orderdate BETWEEN :sdate AND :edate
AND 1=1
GROUP BY po.siteid, po.po3
into this:
SELECT po.siteid, SUM(po.totalcost) totalcost, COUNT(po.ponum), NVL(po.po3,'none') bidwaiver
FROM po
WHERE po.orderdate BETWEEN :sdate AND :edate
AND ( ( po.orderdate >= to_date('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and po.orderdate < to_date('2015-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') ))
GROUP BY po.siteid, po.po3
If it worked in all environments, I guess I wouldn't care but one Maximo environment falls over on a ORA-1830 error. Of course, that's the QA environment.
The sdate and edate params are specified as "date" in Actuate ERDPro, and in Maximo they are setup as params for po.orderdate with a datelookup.
Any clues? Also, why aren't sdate and edate being replaced with values in the transformed SQL?
-C
Sub Start( ) Super::Start( ) ' Insert your code here storeroom = storeroom itemnum = itemnum mroSite = mroSite
' If startDate = Now() Then' startDate = Null' Else
convertDate1 = Format$(startDate, "YYYYMMDD") & " 00:00:00"
' End If' If endDate = Now() Then' endDate = Null' Else
convertDate2 = Format$(endDate, "YYYYMMDD") & " 23:59:59"
' End IfEnd Sub
From: "maximal@wanko.com [MAXIMO]" <MAXIMO@yahoogroups.com>
To: MAXIMO@yahoogroups.com
Sent: Friday, February 5, 2016 8:21 AM
Subject: [MAXIMO List] Actuate transforms SQL, works one place not another?
So I have a SQL query that Maximo-Actuate turns from this:
SELECT po.siteid, SUM(po.totalcost) totalcost, COUNT(po.ponum), NVL(po.po3,'none') bidwaiver
FROM po
WHERE po.orderdate BETWEEN :sdate AND :edate
AND 1=1
GROUP BY po.siteid, po.po3
into this:
SELECT po.siteid, SUM(po.totalcost) totalcost, COUNT(po.ponum), NVL(po.po3,'none') bidwaiver
FROM po
WHERE po.orderdate BETWEEN :sdate AND :edate
AND ( ( po.orderdate >= to_date('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and po.orderdate < to_date('2015-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') ))
GROUP BY po.siteid, po.po3
If it worked in all environments, I guess I wouldn't care but one Maximo environment falls over on a ORA-1830 error. Of course, that's the QA environment.
The sdate and edate params are specified as "date" in Actuate ERDPro, and in Maximo they are setup as params for po.orderdate with a datelookup.
Any clues? Also, why aren't sdate and edate being replaced with values in the transformed SQL?
-C
#yiv1026212578 #yiv1026212578 -- #yiv1026212578ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1026212578 #yiv1026212578ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1026212578 #yiv1026212578ygrp-mkp #yiv1026212578hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1026212578 #yiv1026212578ygrp-mkp #yiv1026212578ads {margin-bottom:10px;}#yiv1026212578 #yiv1026212578ygrp-mkp .yiv1026212578ad {padding:0 0;}#yiv1026212578 #yiv1026212578ygrp-mkp .yiv1026212578ad p {margin:0;}#yiv1026212578 #yiv1026212578ygrp-mkp .yiv1026212578ad a {color:#0000ff;text-decoration:none;}#yiv1026212578 #yiv1026212578ygrp-sponsor #yiv1026212578ygrp-lc {font-family:Arial;}#yiv1026212578 #yiv1026212578ygrp-sponsor #yiv1026212578ygrp-lc #yiv1026212578hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1026212578 #yiv1026212578ygrp-sponsor #yiv1026212578ygrp-lc .yiv1026212578ad {margin-bottom:10px;padding:0 0;}#yiv1026212578 #yiv1026212578actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1026212578 #yiv1026212578activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1026212578 #yiv1026212578activity span {font-weight:700;}#yiv1026212578 #yiv1026212578activity span:first-child {text-transform:uppercase;}#yiv1026212578 #yiv1026212578activity span a {color:#5085b6;text-decoration:none;}#yiv1026212578 #yiv1026212578activity span span {color:#ff7900;}#yiv1026212578 #yiv1026212578activity span .yiv1026212578underline {text-decoration:underline;}#yiv1026212578 .yiv1026212578attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1026212578 .yiv1026212578attach div a {text-decoration:none;}#yiv1026212578 .yiv1026212578attach img {border:none;padding-right:5px;}#yiv1026212578 .yiv1026212578attach label {display:block;margin-bottom:5px;}#yiv1026212578 .yiv1026212578attach label a {text-decoration:none;}#yiv1026212578 blockquote {margin:0 0 0 4px;}#yiv1026212578 .yiv1026212578bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1026212578 .yiv1026212578bold a {text-decoration:none;}#yiv1026212578 dd.yiv1026212578last p a {font-family:Verdana;font-weight:700;}#yiv1026212578 dd.yiv1026212578last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1026212578 dd.yiv1026212578last p span.yiv1026212578yshortcuts {margin-right:0;}#yiv1026212578 div.yiv1026212578attach-table div div a {text-decoration:none;}#yiv1026212578 div.yiv1026212578attach-table {width:400px;}#yiv1026212578 div.yiv1026212578file-title a, #yiv1026212578 div.yiv1026212578file-title a:active, #yiv1026212578 div.yiv1026212578file-title a:hover, #yiv1026212578 div.yiv1026212578file-title a:visited {text-decoration:none;}#yiv1026212578 div.yiv1026212578photo-title a, #yiv1026212578 div.yiv1026212578photo-title a:active, #yiv1026212578 div.yiv1026212578photo-title a:hover, #yiv1026212578 div.yiv1026212578photo-title a:visited {text-decoration:none;}#yiv1026212578 div#yiv1026212578ygrp-mlmsg #yiv1026212578ygrp-msg p a span.yiv1026212578yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1026212578 .yiv1026212578green {color:#628c2a;}#yiv1026212578 .yiv1026212578MsoNormal {margin:0 0 0 0;}#yiv1026212578 o {font-size:0;}#yiv1026212578 #yiv1026212578photos div {float:left;width:72px;}#yiv1026212578 #yiv1026212578photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv1026212578 #yiv1026212578photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1026212578 #yiv1026212578reco-category {font-size:77%;}#yiv1026212578 #yiv1026212578reco-desc {font-size:77%;}#yiv1026212578 .yiv1026212578replbq {margin:4px;}#yiv1026212578 #yiv1026212578ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1026212578 #yiv1026212578ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1026212578 #yiv1026212578ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1026212578 #yiv1026212578ygrp-mlmsg select, #yiv1026212578 input, #yiv1026212578 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1026212578 #yiv1026212578ygrp-mlmsg pre, #yiv1026212578 code {font:115% monospace;}#yiv1026212578 #yiv1026212578ygrp-mlmsg * {line-height:1.22em;}#yiv1026212578 #yiv1026212578ygrp-mlmsg #yiv1026212578logo {padding-bottom:10px;}#yiv1026212578 #yiv1026212578ygrp-msg p a {font-family:Verdana;}#yiv1026212578 #yiv1026212578ygrp-msg p#yiv1026212578attach-count span {color:#1E66AE;font-weight:700;}#yiv1026212578 #yiv1026212578ygrp-reco #yiv1026212578reco-head {color:#ff7900;font-weight:700;}#yiv1026212578 #yiv1026212578ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1026212578 #yiv1026212578ygrp-sponsor #yiv1026212578ov li a {font-size:130%;text-decoration:none;}#yiv1026212578 #yiv1026212578ygrp-sponsor #yiv1026212578ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1026212578 #yiv1026212578ygrp-sponsor #yiv1026212578ov ul {margin:0;padding:0 0 0 8px;}#yiv1026212578 #yiv1026212578ygrp-text {font-family:Georgia;}#yiv1026212578 #yiv1026212578ygrp-text p {margin:0 0 1em 0;}#yiv1026212578 #yiv1026212578ygrp-text tt {font-size:120%;}#yiv1026212578 #yiv1026212578ygrp-vital ul li:last-child {border-right:none !important;}#yiv1026212578
Damn Wes, that was quick. I'll file that one away, but I figured it out myself. Like a dope, I overthought it. (Dopes overthink?)
See, I had the SQL part right, and ERPro ran it just fine, because the param passing was direct, i.e. no rewrites to Actuate, just straight to the engine.
Maximo, though, has that whole parameter section when you register, and THAT is where I screwed up. Instead of just saying I had sdate and edate, I added the po.orderdate to the attribute, thinking this was a good idea. It was not, it was redundant and also a big pain in the ass.
Once I removed the attributes, operators, and left just the param and prompt, it worked like a charm.
Thanks for lookin' Wes, I know you've got four side jobs in addition to your regular one!
-C
---In MAXIMO@yahoogroups.com, <wwilliams@rocketmail.com> wrote :
Sub Start( ) Super::Start( ) ' Insert your code here storeroom = storeroom itemnum = itemnum mroSite = mroSite
' If startDate = Now() Then' startDate = Null' Else
convertDate1 = Format$(startDate, "YYYYMMDD") & " 00:00:00"
' End If' If endDate = Now() Then' endDate = Null' Else
convertDate2 = Format$(endDate, "YYYYMMDD") & " 23:59:59"
' End IfEnd Sub
From: "maximal@wanko.com mailto:maximal@wanko.com [MAXIMO]" <MAXIMO@yahoogroups.com mailto:MAXIMO@yahoogroups.com>
To: MAXIMO@yahoogroups.com mailto:MAXIMO@yahoogroups.com
Sent: Friday, February 5, 2016 8:21 AM
Subject: [MAXIMO List] Actuate transforms SQL, works one place not another?
So I have a SQL query that Maximo-Actuate turns from this:
SELECT po.siteid, SUM(po.totalcost) totalcost, COUNT(po.ponum), NVL(po.po3,'none') bidwaiver
FROM po
WHERE po.orderdate BETWEEN :sdate AND :edate
AND 1=1
GROUP BY po.siteid, po.po3
into this:
SELECT po.siteid, SUM(po.totalcost) totalcost, COUNT(po.ponum), NVL(po.po3,'none') bidwaiver
FROM po
WHERE po.orderdate BETWEEN :sdate AND :edate
AND ( ( po.orderdate >= to_date('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and po.orderdate < to_date('2015-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') ))
GROUP BY po.siteid, po.po3
If it worked in all environments, I guess I wouldn't care but one Maximo environment falls over on a ORA-1830 error. Of course, that's the QA environment.
The sdate and edate params are specified as "date" in Actuate ERDPro, and in Maximo they are setup as params for po.orderdate with a datelookup.
Any clues? Also, why aren't sdate and edate being replaced with values in the transformed SQL?
-C