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; } } }

Data set not displaying results

peraka

Hi, my query returnns expected results, in toad but birt dataset returns 0 rows. Query has many equi joins..
No runtime parameters. Can someone help me with your suggestion s?
Will post the sql query if needed.

Find more posts tagged with

Comments

Yogesh Maharwade

are you using scripted data source or something else...?

peraka

<blockquote class='ipsBlockquote' data-author="'piwya'" data-cid="84971" data-time="1320679278" data-date="07 November 2011 - 08:21 AM">
are you using scripted data source or something else...? </blockquote>
Yes. Here is the query. 
 
select 
workorder.wonum,workorder.jpnum,workorder.description as wodescription,workorder.reportdate, 
locoper.swcproduct,workorder.SWCADDRSTNAME,asset.description as assetdesc, workorder.SWCADDRSUBURB, 
workorder.SWCASSESSPRIORITY ,workorder.SWCPROBLEMTYPE,workorder.SWCLEAKCEASEDDATE,workorder.swcincidentsev, 
FCA.description , FCB.description,FCC.description,workorder.location as level3, LOCHIERARCHY.parent as level2, 
loclevel1.parent as level1, workorder.assetnum,workorder.worktype,workorder.status, 
locoper.SWCOPAREA 
from 
workorder,locoper,locations , lochierarchy , lochierarchy loclevel1 , failurecode FCA, failurecode FCB, 
failurecode FCC, failurereport FA,asset,failurereport FB, failurereport FC 
where 
workorder.swcincidentsev in ( 'I', 'E', 'M') 
and 
(WORKORDER.WONUM = FA.WONUM AND FCA.FAILURECODE = FA.FAILURECODE AND FA.TYPE = 'REMEDY') 
AND (WORKORDER.WONUM = FB.WONUM AND FCB.FAILURECODE = FB.FAILURECODE AND FB.TYPE = 'CAUSE') 
AND (WORKORDER.WONUM = FC.WONUM AND FCC.FAILURECODE = FC.FAILURECODE AND FC.TYPE = 'PROBLEM') 
and 
workorder.location=locations.location and 
workorder.siteid=locations.siteid and 
locations.location = locoper.location and 
workorder.siteid=locoper.siteid and 
locations.location = lochierarchy.location 
and 
loclevel1.location = lochierarchy.parent 
and workorder.ASSEtnum = asset.assetnum 
order by 1

Yogesh Maharwade

can you upload your rptdesign .i m not sure but its something like scripting problem.

Hans_vd

Does this only happen with this query?
Does it work when you just enter "SELECT * FROM workorder" for example?
Are you sure you are going to the exact same database?

Are you just getting 0 rows, or is there an error message?

peraka

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="84978" data-time="1320682294" data-date="07 November 2011 - 09:11 AM">
Does this only happen with this query? 
Does it work when you just enter "SELECT * FROM workorder" for example? 
Are you sure you are going to the exact same database? 
K 
Are you just getting 0 rows, or is there an error message? </blockquote>
If I access only fields of workorder I get proper results. When I go for joins it doesn't yield results.

Hans_vd

I would go like this: start with "SELECT * FROM workorder" and then build up the query step by step until it goes wrong.

peraka

<blockquote class='ipsBlockquote' data-author="'piwya'" data-cid="84977" data-time="1320682213" data-date="07 November 2011 - 09:10 AM">
can you upload your rptdesign .i m not sure but its something like scripting problem. </blockquote>
I have attached the file. Please suggest me.

peraka

<blockquote class='ipsBlockquote' data-author="'peraka'" data-cid="85007" data-time="1320724456" data-date="07 November 2011 - 08:54 PM">
I have attached the file. Please suggest me. </blockquote>
I have commented the rows in fetch method. Please don't get confused.

lrnrit

<blockquote class='ipsBlockquote' data-author="'peraka'" data-cid="85008" data-time="1320724581" data-date="07 November 2011 - 08:56 PM">
I have commented the rows in fetch method. Please don't get confused. </blockquote>
 
Hi, 
 
I found one problem with your query is that while concatenating the query parts 
you are not leaving any whitespaces 
 
+"workorder.wonum,workorder.jpnum,workorder.description as wodescription," . 
 
Try concatenating the query parts with whitespaces at start and end like this ( whitespaces is shown by underscore for illustration purpose ): 
 
+"__workorder.wonum,workorder.jpnum,workorder.description as wodescription,__ " 
 
 
let me know if it works. 
 
lrnit

peraka

Hi Irnit, thank you so much for your input. It's working now. Maybe I have to tune the query, it's taking lot of time to return results.

Yogesh Maharwade

hi
sorry for late replying. actually i was busy with my project.thats y not get back 2 you.

As irnit said if u not put spaces it gets concatenating spaces.thats the reason you get no rows

let us know if u have question...

regards
piwya

peraka

<blockquote class='ipsBlockquote' data-author="'piwya'" data-cid="85044" data-time="1320768662" data-date="08 November 2011 - 09:11 AM">
hi 
sorry for late replying. actually i was busy with my project.thats y not get back 2 you. 
 
As irnit said if u not put spaces it gets concatenating spaces.thats the reason you get no rows 
 
 
let us know if u have question... 
 
regards 
piwya </blockquote>
Thanks for your reply. Have a small question. We have a report with 3 input parameters. First 2 input parameters are mandatory. When I give 3rd input parameter, report returns no data. When I input only above 2 parameters it gives whole data which is very huge. Not sure why its not returning data. But when I ran the query it gives data. 
 
Please comment on the above. 
 
Thnaks, 
Suresh.

Yogesh Maharwade

hmmm
is your third parameter is date. if yes then you have to do some changes in script. will you re upload your report with all 3 parameter

regards
piwya

peraka

<blockquote class='ipsBlockquote' data-author="'piwya'" data-cid="85162" data-time="1320919587" data-date="10 November 2011 - 03:06 AM">
hmmm 
is your third parameter is date. if yes then you have to do some changes in script. will you re upload your report with all 3 parameter 
 
regards 
piwya </blockquote>
Hi Piwya, 3rd parameter is not a date parameter. I observe this in many reports. If I run the query in the database it returns data. But in report preview it doesn't return data. 
 
Please comment. 
 
Thanks.

Yogesh Maharwade

Hi

Make sure that your parameter name and parameter name inside dataset both are same...which 3 parameter you want to pass ...?will you upload your report..?

peraka

<blockquote class='ipsBlockquote' data-author="'piwya'" data-cid="85254" data-time="1321263396" data-date="14 November 2011 - 02:36 AM">
Hi 
 
Make sure that your parameter name and parameter name inside dataset both are same...which 3 parameter you want to pass ...?will you upload your report..? </blockquote>
Hi Piwya, 
 
This report was working fine few days ago. It started behaving same in all the environments Dev, Prod etc.. 
 
Suresh.

peraka

<blockquote class='ipsBlockquote' data-author="'peraka'" data-cid="84976" data-time="1320681482" data-date="07 November 2011 - 08:58 AM">
Yes. Here is the query. 
 
select 
workorder.wonum,workorder.jpnum,workorder.description as wodescription,workorder.reportdate, 
locoper.swcproduct,workorder.SWCADDRSTNAME,asset.description as assetdesc, workorder.SWCADDRSUBURB, 
workorder.SWCASSESSPRIORITY ,workorder.SWCPROBLEMTYPE,workorder.SWCLEAKCEASEDDATE,workorder.swcincidentsev, 
FCA.description , FCB.description,FCC.description,workorder.location as level3, LOCHIERARCHY.parent as level2, 
loclevel1.parent as level1, workorder.assetnum,workorder.worktype,workorder.status, 
locoper.SWCOPAREA 
from 
workorder,locoper,locations , lochierarchy , lochierarchy loclevel1 , failurecode FCA, failurecode FCB, 
failurecode FCC, failurereport FA,asset,failurereport FB, failurereport FC 
where 
workorder.swcincidentsev in ( 'I', 'E', 'M') 
and 
(WORKORDER.WONUM = FA.WONUM AND FCA.FAILURECODE = FA.FAILURECODE AND FA.TYPE = 'REMEDY') 
AND (WORKORDER.WONUM = FB.WONUM AND FCB.FAILURECODE = FB.FAILURECODE AND FB.TYPE = 'CAUSE') 
AND (WORKORDER.WONUM = FC.WONUM AND FCC.FAILURECODE = FC.FAILURECODE AND FC.TYPE = 'PROBLEM') 
and 
workorder.location=locations.location and 
workorder.siteid=locations.siteid and 
locations.location = locoper.location and 
workorder.siteid=locoper.siteid and 
locations.location = lochierarchy.location 
and 
loclevel1.location = lochierarchy.parent 
and workorder.ASSEtnum = asset.assetnum 
order by 1 </blockquote>
The above query works fine. If I add the below line to the where condition that also works fine in database. But in dataset it doesn't return any rows. 
 
where 
workorder.swcincidentsev in ( 'I', 'E', 'M') 
and trunc(workorder.reportdate)=(trunc(sysdate)-1) 
 
Please suggest. 
 
Suresh.

Yogesh Maharwade

hi,

Use this line

to_date(workorder.reportdate)=to_date(sysdate)-1

also check your database ,is there any entrees for day before sysdate.

Hope it helps