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.
Dear BIRT experts,
I'd like to simplify my sql query using function() in BIRT, but the error persist. "Sqlstate 37000
[servername\databaseserver]Could not find stored procedure 'undefined'."
The BIRT cannot read the sqlText query.
But when I test the query in the web browser, it runs okay.
I'll give you further information regarding my function():
Initialize() method.
paramStartDate=MXReportSqlFormat.getStartDayTimestampFunction(params["startdate"]);
paramEndDate= MXReportSqlFormat.getEndDayTimestampFunction(params["enddate"]);
var paramWorkType;
function sqlQuery(startdate, enddate, worktype){
var statusArray = new Array();
statusArray[0] = "WAPPR";
statusArray[1] = "APPR";
statusArray[2] = "INPRG";
statusArray[3] = "DONE";
statusArray[4] = "RECORDED";
statusArray[5] = "CLOSE";
statusArray[6] = "CAN";
statusArray[7] = "WMATL";
statusArray[8] = "REROLL";
var whereclause = " and historyflag=0 and wopm4 in ('EBO','EEC','EMH','ETU','OSS','OPS','OPP','OHS','OCP') and istask = 0 and custwojp5 in ('S1', 'S2') ";
var mysqltext;
for (i=0; i<=8; i++){
if (i<=7) {
mysqltext = " select reportdate,worktype,location, assetnum, wopm4,wonum,'"+ statusArray[i] +"' as types from workorder "
+ " WHERE status = '"+ statusArray[i] + "' "+ whereclause + " union " ;
}else {
mysqltext = " select reportdate, worktype,location,assetnum,wopm4,wonum,'"+ statusArray[i] +"' as types from workorder "
+ " WHERE status = '"+ statusArray[i] + "' "+ whereclause + " and workorder.reportdate >= '"+ startdate +"' and workorder.reportdate <='"+ enddate + "' and workorder.worktype='"+ worktype +"'";
}
}
return (startdate , enddate, worktype);
}
function sqlLoad_CI(){
paramWorkType = "CI";
sqltext2 = sqlQuery(paramStartDate,paramEndDate, paramWorkType);
}
==DATASET==
--Open Method--
CIDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
CIDataSet.open();
var sqlText;
sqlText = sqlLoad_CI();
CIDataSet.setQuery(sqlText);
Please give me any clues to solve this matter if you don't mind.
Thank you.
Best Regards,
Tommy
case closed.
Thank you
--- In MAXIMO@yahoogroups.com, "Tommy H Sumarsono" <hediaka@...> wrote:
>
> Dear BIRT experts,
>
> I'd like to simplify my sql query using function() in BIRT, but the error persist. "Sqlstate 37000
> [servername\databaseserver]Could not find stored procedure 'undefined'."
> The BIRT cannot read the sqlText query.
>
> But when I test the query in the web browser, it runs okay.
>
>
>
> I'll give you further information regarding my function():
>
> Initialize() method.
>
> paramStartDate=MXReportSqlFormat.getStartDayTimestampFunction(params["startdate"]);
> paramEndDate= MXReportSqlFormat.getEndDayTimestampFunction(params["enddate"]);
> var paramWorkType;
>
> function sqlQuery(startdate, enddate, worktype){
>
>
> var statusArray = new Array();
> statusArray[0] = "WAPPR";
> statusArray[1] = "APPR";
> statusArray[2] = "INPRG";
> statusArray[3] = "DONE";
> statusArray[4] = "RECORDED";
> statusArray[5] = "CLOSE";
> statusArray[6] = "CAN";
> statusArray[7] = "WMATL";
> statusArray[8] = "REROLL";
>
> var whereclause = " and historyflag=0 and wopm4 in ('EBO','EEC','EMH','ETU','OSS','OPS','OPP','OHS','OCP') and istask = 0 and custwojp5 in ('S1', 'S2') ";
>
> var mysqltext;
>
>
> for (i=0; i<=8; i++){
> if (i<=7) {
> mysqltext = " select reportdate,worktype,location, assetnum, wopm4,wonum,'"+ statusArray[i] +"' as types from workorder "
> + " WHERE status = '"+ statusArray[i] + "' "+ whereclause + " union " ;
> }else {
> mysqltext = " select reportdate, worktype,location,assetnum,wopm4,wonum,'"+ statusArray[i] +"' as types from workorder "
> + " WHERE status = '"+ statusArray[i] + "' "+ whereclause + " and workorder.reportdate >= '"+ startdate +"' and workorder.reportdate <='"+ enddate + "' and workorder.worktype='"+ worktype +"'";
> }
>
> }
>
> return (startdate , enddate, worktype);
>
>
> }
>
>
> function sqlLoad_CI(){
>
> paramWorkType = "CI";
> sqltext2 = sqlQuery(paramStartDate,paramEndDate, paramWorkType);
>
>
> }
>
>
> ==DATASET==
> --Open Method--
> CIDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
> CIDataSet.open();
> var sqlText;
> sqlText = sqlLoad_CI();
> CIDataSet.setQuery(sqlText);
>
>
>
> Please give me any clues to solve this matter if you don't mind.
>
>
> Thank you.
>
>
> Best Regards,
>
> Tommy
>