Pick up data (rows) inclusively

I noticed when I specified a retrieval from a range, not all qualified rows from a DB2 table are returned. For example, if I have in a DB2 table:
Date-1 / row-1
Date-1 / row-2
Date-1 / row-3
..
Date-2 / row-1
Date-2 / row-2
Date-2 / row-3
Date-2 / row-4

and my query is something like this:
select date_value, row_value
from table
where date_value between date-1 and date-2

BIRT would only return the all the rows from "Date-1 / row-1" thru "Date-2 / row-3" except the last row "Date-2 / row-4".

Does anyone know how to make sure that all rows (inclusively) get returned?

I ran the same query in a "native" DB2 Command Center and all rows were returned fine.

Thanks.

T. Tran

Find more posts tagged with

Comments

mwilliams

Hi T. Tran,

What version of BIRT are you using? I don't know of a reason why this would happen. Is this in the preview window? It could possibly just be hitting that last line before it hits the preview data limit. Have you ran it in the web viewer?

tdsc1237

Hi Michael,

I'm using Eclipse BIRT Designer Version 1.0.1 Build <20051130-1157>. The wrong result showed up in both "preview" and the web viewer. The output was just less than 10 rows, way below the data limit.

In my example, if the table has just 1 row "Date-3 / row-1", and if I specified the range "between Date-1 and date-3", then all rows between Date-1 and Date-2 would show up, without "date-3 / row-1". But I don't always have "date-3" data, plus if "date-3" has more than 1 row, it would retrieve date-3 rows where I don't need. Thanks for your help. T. Tran

mwilliams

T. Tran,

Are you required to use 1.0.1 for any reason? I don't have a copy of that to even attempt to test this on. A newer version may not have the same issue. I'll try to look into this more.

tdsc1237

Michael,

I had to use 1.0.1 because our users have that version. Let me try a later version to see if it makes any different. Thanks. T. Tran

tdsc1237

Michael,

I need to clarify something on the issue. If my SQL statement explicitly specifies the date range as so (between 'Oct 5, 2009' and 'Oct 6, 2009') then the retrieval is OK with all the records get processed.

But if I specify the range as "between ? and ?" so BIRT can prompt for the "Enter Parameters" dialog box, then it would not pick up the last logical record. All of these happen with "Eclipse BIRT Designer Version 1.0.1 Build <20051130-1157>"

I did the similar test with "Eclipse BIRT Designer Version 2.3.2.r232_20090202 Build <2.3.2.v20090304-1357>" then I don't have such problem. All records were properly retrieved.

Unfortunately I have to use BIRT 1.0.1 with this report. Our user only has this version.

Thanks for any help or guidance on this problem. T. Tran

mwilliams

T. Tran,

Yeah, that's tough. It definitely looks like it is what I thought it was, an issue with the older version. You might try bringing in the extra values in by adding that extra day to the date parameter and then filtering out the extra values in the designer with a dataSet filter. This may allow you to make sure you get that row. It's worth a shot. Let me know.