Pass dataset parameter to query [message #538028] |
Fri, 04 June 2010 14:58 |
Missing name Missing name Messages: 94 Registered: November 2009 |
Member |
|
|
hi,
As part of a query I need to calculate how many days old a record is, on the report date. In DB2 this would read as
days(report date) - days(dateCreated) daysold
When I try to do this using the ? mechanism in the dataset builder it complains, I assume as I am not allowed to use the ? in this manner:
days(?) - days(dateCreated) daysold
Is there any other way to do this?
I can do it using a scripted dataset by retrieving the report date using params["ReportDate"].value and altering the this.queryText variable but its a bit hacky....
thanks
[Updated on: Fri, 04 June 2010 14:59] Report message to a moderator
|
|
|
Re: Pass dataset parameter to query [message #538068 is a reply to message #538028] |
Fri, 04 June 2010 19:51 |
|
Trevor,
What about using a computed column that uses one of the BIRT functions:
BirtDateTime.diffDay(row["ORDERDATE"],row["SHIPPEDDATE"])
You should be able to swap out the parameter to the diffDay with
params["myparm"].value
Jason
On 6/4/2010 10:58 AM, TREVOR.HIGGINS@FINEOS.COM wrote:
> hi,
> As part of a query I need to calculate how many days old a record is, on
> the report date. In DB2 this would read as
>
> days(report date) - days(dateCreated) daysold
>
>
> When I try to do this using the ? mechanism in the dataset builder it
> complains, I assume as I am not allowed to use the ? in this manner. Is
> there any other way to do this?
>
> I can do it using a scripted dataset by retrieving the report date using
> params["ReportDate"].value and altering the this.queryText variable but
> its a bit hacky....
>
> thanks
|
|
|
|
Re: Pass dataset parameter to query [message #538424 is a reply to message #538150] |
Mon, 07 June 2010 14:51 |
|
I do not know of another word around other than using the script method
you are doing.
On 6/5/2010 2:26 PM, TREVOR.HIGGINS@FINEOS.COM wrote:
> Hi Jason,
> I had considered that but its not really a runner as the query needs to
> do a "group by" days_diff column after calculating it. This needs to be
> done in the DB or else there will be millions of records returned.....
>
> Trevor
|
|
|
Powered by
FUDForum. Page generated in 0.02911 seconds