Replace function not working in Birt?

Hey guys I'm trying to use a replace function in a query in my birt report and it doesn't seem to be working. When I run this query outside BIRT it works fine. I have my query in the open function of my dataset. Here's what I have (maybe the issue is because I'm trying to replace something that has '\\' in it???): 
 
 
maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName()); 
maximoDataSet.open(); 
 
var sqlText = new String(); 
 
sqlText = "select replace(filename, '\\camsprdactjam1\d$', '<a class='bbc_url' href='http://camsengprd:7001'>http://camsengprd:7001</a>') filename, lirr_seq, description, ownertable, location, reference, lirr_imglayout from ( " + 
"SELECT urlname filename, doclinks.lirr_seq, lirr_desc description, doclinks.OWNERTABLE, " + 
" lirr_photoloc location, '' reference, lirr_imglayout " + 
" FROM docinfo, workorder, doclinks " + 
" WHERE wonum = '" + params["wonum"] + "' " + 
" AND docinfo.DOCINFOID = doclinks.DOCINFOID " + 
" AND workorder.workorderid = doclinks.ownerid " + 
" and docinfo.doctype = 'PHOTOS' " + 
" and doclinks.ownertable = 'WORKORDER' " + 
" and workorder.siteid = 'ES' " + 
"union " + 
"select max(filename) filename, min(lirr_seq) lirr_seq, wm_concat(description) description, ownertable, " + 
"wm_concat(DISTINCT location) location, lirr_getphotoref(lirr_wonum, sourcefile) reference, lirr_imglayout from( " + 
"select urlname filename, " + 
" v_docs.lirr_seq, " + 
" v_docs.lirr_desc description, " + 
" v_docs.ownertable, " + 
" v_docs.lirr_photoloc LOCATION, " + 
" v_docs.description sourcefile, " + 
" v_docs.document, " + 
" v_docs.lirr_imglayout, " + 
" real_m.lirr_wonum " + 
" from " + 
" (SELECT MAX (measurementid) measurementid, lirr_wonum, assetnum, " + 
" metername, siteid " + 
" FROM measurement GROUP BY lirr_wonum, assetnum, metername, siteid) real_m, " + 
" (SELECT a.assetnum, b.lirr_wonum, b.metername, " + 
" c.LIRR_SEQ, c.ownertable,c.lirr_desc, c.lirr_photoloc, c.LIRR_IMGLAYOUT, " + 
" d.doctype, d.docinfoid, d.document, d.urlname, d.description " + 
" FROM asset a, measurement b, doclinks c, docinfo d " + 
" WHERE a.assetnum = b.assetnum " + 
" AND a.siteid = b.siteid " + 
" AND b.lirr_wonum = c.lirr_reference " + 
" AND c.docinfoid = d.docinfoid " + 
" AND c.ownertable = 'MEASUREMENT' " + 
" AND d.doctype = 'PHOTOS' " + 
" AND c.ownerid = b.measurementid) v_docs " + 
" where real_m.lirr_wonum = v_docs.lirr_wonum " + 
" AND real_m.assetnum = v_docs.assetnum " + 
" AND real_m.metername = v_docs.metername " + 
" AND real_m.lirr_wonum = '" + params["wonum"] + "' " + 
" ) " + 
" group by lirr_wonum, sourcefile, ownertable, lirr_imglayout " + 
")" ; 
 
 
maximoDataSet.setQuery(sqlText);

Find more posts tagged with

Comments

megabri

I fixed the issue. Turns out I was right I had to put '\' before the slashes so it would take it literally.