nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

with as query inside birt

oraBirt

Thank for your help in advance!1. Could anyone tell me what is wrong in the query? No data output from the query in Birt.2. Is there a way to debuy the query step by step? Like we debug in other languages, like in .net?3. is it possible that we can use data from one dataset from another dataset? [color=#000000;][font="consolas;"]sqlText = [/color][/font][color=#2a00ff;][font="consolas;"]"with wo_population as ( "[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" SELECT wonum, reportdate, istask, worktype, siteid, status "[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" FROM WORKORDER "[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" where "[/color][/font][color=#000000;][font="consolas;"] + params/color][/font][color=#2a00ff;][font="consolas;"]"where"[/color][/font][color=#000000;][font="consolas;"[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" and ISTASK=0 "[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" and worktype NOT in ('PM','1JOB' ,'JOB') "[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" GROUP BY WONUM, REPORTDATE, ISTASK, WORKTYPE, SITEID, STATUS "[/color][/font][color=#000000;][font="consolas;"] + [/color][/font][color=#2a00ff;][font="consolas;"]")"[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" , wo_APPR as("[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" SELECT WO_Population.WONUM, WOSTATUS.STATUS, WOSTATUS.CHANGEDATE, WO_Population.REPORTDATE, WO_Population.WORKTYPE "[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" FROM WO_Population , WOSTATUS "[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" where "[/color][/font][color=#000000;][font="consolas;"] + params/color][/font][color=#2a00ff;][font="consolas;"]"where"[/color][/font][color=#000000;][font="consolas;"[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" and WO_Population.WONUM = WOSTATUS.WONUM"[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" WOSTATUS.STATUS='APPR' "[/color][/font][color=#000000;][font="consolas;"] +[/color][/font][color=#2a00ff;][font="consolas;"]")"[/color][/font] [color=#2a00ff;][font="consolas;"]" , wo_COMP as("[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" SELECT WO_Population.WONUM, WOSTATUS.STATUS, WOSTATUS.CHANGEDATE, WO_Population.REPORTDATE, WO_Population.WORKTYPE "[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" FROM WO_Population , WOSTATUS "[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" where "[/color][/font][color=#000000;][font="consolas;"] + params/color][/font][color=#2a00ff;][font="consolas;"]"where"[/color][/font][color=#000000;][font="consolas;"[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" and WO_Population.WONUM = WOSTATUS.WONUM"[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" WOSTATUS.STATUS='COMP' "[/color][/font][color=#000000;][font="consolas;"] +[/color][/font][color=#2a00ff;][font="consolas;"]")"[/color][/font] [color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" select wo_appr.worktype, round( Avg(WO_COMP.CHANGEDATE - WO_APPR.CHANGEDATE), 2) AS avgTimeAPPRtoCOMP, Count(WO_APPR.WONUM) woCountCompletedbyWorkGroup "[/color][/font][color=#000000;][font="consolas;"]+[/color][/font][color=#2a00ff;][font="consolas;"]" from wo_appr, wo_COMP "[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" where "[/color][/font][color=#000000;][font="consolas;"] + params/color][/font][color=#2a00ff;][font="consolas;"]"where"[/color][/font][color=#000000;][font="consolas;"[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" and WO_APPR.WONUM = WO_COMP.WONUM "[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" group by wo_APPR.worktype "[/color][/font][color=#000000;][font="consolas;"]+ [/color][/font][color=#2a00ff;][font="consolas;"]" order by wo_appr.worktype "[/color][/font]

Find more posts tagged with

Comments

Hans_vd

<blockquote class="ipsBlockquote"> [color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;font-size:10pt;][color=rgb(0,0,0);][font="consolas;"]+ [/color][/color][/font][color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;font-size:10pt;][color=rgb(42,0,255);][font="consolas;"]" and WO_Population.WONUM = WOSTATUS.WONUM"[/color][/color][/font][color=rgb(0,0,0);][font="consolas;"]+ [/color][/font][color=rgb(42,0,255);][font="consolas;"]" WOSTATUS.STATUS='APPR' "[/color][/font][color=rgb(0,0,0);][font="consolas;"] +[/color][/font][color=rgb(42,0,255);][font="consolas;"]")"[/color][/font]</blockquote> <blockquote class="ipsBlockquote"> [color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;font-size:10pt;][color=rgb(0,0,0);][font="consolas;"]+ [/color][/color][/font][color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;font-size:10pt;][color=rgb(42,0,255);][font="consolas;"]" and WO_Population.WONUM = WOSTATUS.WONUM"[/color][/color][/font][color=rgb(0,0,0);][font="consolas;"]+ [/color][/font][color=rgb(42,0,255);][font="consolas;"]" WOSTATUS.STATUS='COMP' "[/color][/font][color=rgb(0,0,0);][font="consolas;"] +[/color][/font][color=rgb(42,0,255);][font="consolas;"]")"[/color][/font]</blockquote> There are two "and"s missing. Next to that of course I cannot see what the value of params["where"] is Why are you using a script to build the query instead of a regular data set? You can use the values of one query in another query by nesting table elements or you can create joint data sets.

oraBirt

Yes, you are right, missign two "and".Any example "use the values of one query in another query by nesting table elements or you can create joint data sets. "?Thanks very much!

wwilliams

>Any example "use the values of one query in another query by nesting table elements Look at the out of the box Maximo reports for examples of subreports and nesting tables.A couple examples are woprint.rptdesign, lochierarcy.rptdesign.

oraBirt

Thanks very much for your reply!1. not found lochierarcy.rptdesign.2. Not sure if subreport is the idea template or not? I have 5 other similar queries, the above one is "APPR to COMP", others like "NEW to WPLAN', "WPLAN to APPR" etc (only to change wostatus.status to these different values). but not all of the queries have exact same worktypes. the idea result is to put all data in one table for all the queries, not main report and subreport separately.Any advise would be appreciated!