Eclipse Community Forums - RDF feed
https://www.eclipse.org/forums/
Eclipse Community ForumsDate Parameter
https://www.eclipse.org/forums/index.php/mv/msg/119551/364113/#msg_364113
Originally posted by: mrong.facilities.rutgers.edu
Greeting,
I try to use a parameter to filter date field in Oracle.
I created a dynamic dataset for lookup column. It shows the following
format on the report parameter: June 25, 2008 10:10AM. But when I select
this value and run the report, it doesn't give me any record.
Thanks in advance.
-M]]>2008-07-22T15:39:29-00:00Re: Date Parameter
https://www.eclipse.org/forums/index.php/mv/msg/119551/364139/#msg_364139
Originally posted by: jasonweathersby.alltel.net
Mark,
Can you post the report?
Jason
Mark wrote:
> Greeting,
>
> I try to use a parameter to filter date field in Oracle.
>
> I created a dynamic dataset for lookup column. It shows the following
> format on the report parameter: June 25, 2008 10:10AM. But when I select
> this value and run the report, it doesn't give me any record.
>
> Thanks in advance.
>
> -M
>]]>2008-07-23T15:07:10-00:00Re: Date Parameter
https://www.eclipse.org/forums/index.php/mv/msg/119551/786540/#msg_786540
I have pasted my code below... Please let me know how should I declare the date parameter.
if(params["glaccount"].value) {
params["glaccount"] = params["glaccount"].toUpperCase();
//params["where"]+= " and w.glaccount like '"+params["glaccount"]+"'";toDateString()
}
//df = new Packages.java.text.SimpleDateFormat("MM-dd-yyyy");
//begindate = df.parse(row["reportdate"]);
//begindate;
if (params["begindate"].value){
//params["begindate"]=params["begindate"].toDateString();
//params["where"]+= " and w.reportdate >= "
// + MXReportSqlFormat.getDateFunction(params["begindate"]);
}
if (params["enddate"].value){
//params["enddate"]=params["enddate"].toDateString();
//params["where"]+= " and w.reportdate <= '"
//+ MXReportSqlFormat.getDateFunction(params["enddate"])+ "'";
}
var sqlText = new String();
// Add query to sqlText variable.
sqlText = " select pl.refwo, pl.ponum,pl.polinenum,"
+" pl.unitcost * (sum(distinct pl.orderqty)-isnull(sum(m.quantity/m.conversion),0))* (1+(isnull(t.taxrate,0)/100)) obnr into #polinetemp"
+" from matrectrans m, workorder w,"
+" (select taxcode,taxrate from tax t1 where effective = (select max(effective) "
+" from tax t2 where t2.taxcode = t1.taxcode group by taxcode)) t,"
+" (select pl.refwo, pl.ponum,pl.polinenum,pl.unitcost,pl.orderqty,pl.tax1code,pl.receiptscomplete"
+" from poline pl, po where pl.ponum = po.ponum and po.status not in ('CAN','CLOSE')) pl "
+" where w.glaccount like '"+params["glaccount"]+"'"
//+" and w.reportdate >= '"+ params["begindate"] +"'"
//+" and w.reportdate <= '"+ params["enddate"] + "'"+"'23:59:59'"
//+" and w.reportdate <= '"+params["enddate"]+"' + '23:59:59'"
+" and m.ponum =* pl.ponum and m.polinenum =* pl.polinenum and pl.refwo = w.wonum and pl.tax1code *= t.taxcode "
+" and pl.receiptscomplete <> '1' " //+ params["where"]
+"group by pl.refwo, pl.ponum,pl.polinenum,pl.unitcost,t.taxrate"
+" select refwo wonum, sum(obnr) obnr into #temp from #polinetemp where isnull(obnr,0) >= 0 group by refwo"
+" FROM workorder workorder, stard.dbo.billing_source_dept b, #temp t"
+" where workorder.glaccount LIKE '"+params["glaccount"]+"'"
//+" and w.reportdate >= '" + MXReportSqlFormat.getDateFunction(params["begindate"])+"'"
//+" and w.reportdate <= '" + MXReportSqlFormat.getDateFunction(params["enddate"])+ "'"+"'23:59:59'"
+" and workorder.reportdate >= '"+ params["begindate"] +"'"
+" AND workorder.reportdate <= '"+params["enddate"]+"' + ' 23:59:59'"
+" and workorder.wonum not in (select distinct BILLING_NUMBER from facbilld.dbo.billing_detail "
+" where source in ('max','sts','vit','sec')"
+" and bill_date_time is not null and bill_date_time <> '')"
+" and workorder.glaccount is not null and workorder.status not in ('CAN', 'CLOSE') "
+" and workorder.worktype not in ('BY','RO') and ((b.source in ('max','sts','vit','sec') "
+" and left(workorder.glaccount, 5) = b.deptid) or left(workorder.glaccount, 5) = '55352') "
+" AND workorder.wopm2 not in ('4') AND workorder.wopm2 is not null"
+" AND workorder.wopm2 <> '' AND (substring(workorder.wopm4, 1, 3) <> '160' "
+" or (substring(workorder.wopm4,1,5) >= '16062' and substring(workorder.wopm4,1,5) <= '16099') "
+" or substring(workorder.woPM5,1,2) in ('43','44','46')) AND (substring(workorder.wopm4, 1, 5)) not in ('53250')"
+" and workorder.wonum *= t.wonum " //+ params["where"]
+" ORDER BY workorder.wonum"
;
maximoDataSet.setQuery(sqlText);]]>Rahul Sawant2012-01-30T14:52:31-00:00Re: Date Parameter
https://www.eclipse.org/forums/index.php/mv/msg/119551/786670/#msg_786670
I am not to familiar with Maximo, but you should be able to set the date
format in the design in the parameters editor. What you may want to do
is to print out the sql that you are generating in the script and verify
it. Add something like:
importPackage( Packages.java.io );
out = new PrintWriter( new FileWriter( "c:/temp/myquery.txt", true ) );
out.println( "Query passed to Maximo is " + sqlText);
out.close();
to the bottom of the script and then check the txt file to see what is
being generated.
Jason
On 1/30/2012 9:52 AM, Rahul Sawant wrote:
> Hi,
>
> I have pasted my code below... Please let me know how should I declare
> the date parameter.
>
> Your help is much appreciated.
>
> maximoDataSet =
> MXReportDataSetProvider.create(this.getDataSource().getName(),
> this.getName());
> maximoDataSet.open();
>
> if(maximoDataSet.isOracle()){
> dbClause = "nvl";
> asClause = "";
> db2QueryAlias = "";
> }
> else{
> dbClause = "coalesce";
> }
>
> if(params["glaccount"].value) {
> params["glaccount"] = params["glaccount"].toUpperCase();
> //params["where"]+= " and w.glaccount like
> '"+params["glaccount"]+"'";toDateString()
> }
>
> //df = new Packages.java.text.SimpleDateFormat("MM-dd-yyyy");
> //begindate = df.parse(row["reportdate"]);
> //begindate;
>
> if (params["begindate"].value){
> //params["begindate"]=params["begindate"].toDateString();
> //params["where"]+= " and w.reportdate >= " // +
> MXReportSqlFormat.getDateFunction(params["begindate"]);
> }
>
> if (params["enddate"].value){
> //params["enddate"]=params["enddate"].toDateString();
> //params["where"]+= " and w.reportdate <= '" //+
> MXReportSqlFormat.getDateFunction(params["enddate"])+ "'";
> }
>
>
>
> var sqlText = new String();
> // Add query to sqlText variable.
> sqlText = " select pl.refwo, pl.ponum,pl.polinenum,"
> +" pl.unitcost * (sum(distinct
> pl.orderqty)-isnull(sum(m.quantity/m.conversion),0))*
> (1+(isnull(t.taxrate,0)/100)) obnr into #polinetemp"
> +" from matrectrans m, workorder w," +" (select taxcode,taxrate from tax
> t1 where effective = (select max(effective) "
> +" from tax t2 where t2.taxcode = t1.taxcode group by taxcode)) t,"
> +" (select pl.refwo,
> pl.ponum,pl.polinenum,pl.unitcost,pl.orderqty,pl.tax1code,pl.receiptscomplete"
>
> +" from poline pl, po where pl.ponum = po.ponum and po.status not in
> ('CAN','CLOSE')) pl "
>
> +" where w.glaccount like '"+params["glaccount"]+"'"
> //+" and w.reportdate >= '"+ params["begindate"] +"'"
> //+" and w.reportdate <= '"+ params["enddate"] + "'"+"'23:59:59'"
> //+" and w.reportdate <= '"+params["enddate"]+"' + '23:59:59'" +" and
> m.ponum =* pl.ponum and m.polinenum =* pl.polinenum and pl.refwo =
> w.wonum and pl.tax1code *= t.taxcode " +" and pl.receiptscomplete <> '1'
> " //+ params["where"]
> +"group by pl.refwo, pl.ponum,pl.polinenum,pl.unitcost,t.taxrate"
>
> +" select refwo wonum, sum(obnr) obnr into #temp from #polinetemp where
> isnull(obnr,0) >= 0 group by refwo"
>
> +" SELECT distinct workorder.glaccount, workorder.wonum,
> workorder.description, workorder.actmatcost,"
> +" workorder.acttoolcost, workorder.actlabcost, workorder.esttoolcost,
> workorder.estlabcost, workorder.wopm4,"
> +" workorder.wopm2, workorder.reportdate, workorder.worktype,
> workorder.wopm5, workorder.targstartdate,"
> +" workorder.actlabhrs, workorder.wo5, isnull(obnr,0) obnr"
>
> +" FROM workorder workorder, stard.dbo.billing_source_dept b, #temp t"
>
> +" where workorder.glaccount LIKE '"+params["glaccount"]+"'"
> //+" and w.reportdate >= '" +
> MXReportSqlFormat.getDateFunction(params["begindate"])+"'"
> //+" and w.reportdate <= '" +
> MXReportSqlFormat.getDateFunction(params["enddate"])+ "'"+"'23:59:59'"
> +" and workorder.reportdate >= '"+ params["begindate"] +"'"
> +" AND workorder.reportdate <= '"+params["enddate"]+"' + ' 23:59:59'"
> +" and workorder.wonum not in (select distinct BILLING_NUMBER from
> facbilld.dbo.billing_detail "
> +" where source in ('max','sts','vit','sec')"
> +" and bill_date_time is not null and bill_date_time <> '')"
> +" and workorder.glaccount is not null and workorder.status not in
> ('CAN', 'CLOSE') "
> +" and workorder.worktype not in ('BY','RO') and ((b.source in
> ('max','sts','vit','sec') "
> +" and left(workorder.glaccount, 5) = b.deptid) or
> left(workorder.glaccount, 5) = '55352') "
> +" AND workorder.wopm2 not in ('4') AND workorder.wopm2 is not null"
> +" AND workorder.wopm2 <> '' AND (substring(workorder.wopm4, 1, 3) <>
> '160' "
> +" or (substring(workorder.wopm4,1,5) >= '16062' and
> substring(workorder.wopm4,1,5) <= '16099') "
> +" or substring(workorder.woPM5,1,2) in ('43','44','46')) AND
> (substring(workorder.wopm4, 1, 5)) not in ('53250')"
> +" and workorder.wonum *= t.wonum " //+ params["where"]
> +" ORDER BY workorder.wonum"
> ;
>
> maximoDataSet.setQuery(sqlText);]]>Jason Weathersby2012-01-30T17:35:16-00:00