Problem with DateTime parameters when substituting dynamically within Script

stinky

I have a Query for which part of it is built dynamically within script form on a "beforeOpen" notification of the DataSet.

The reason I need to build part of it dynamically is because the sql clause involves LIKE statments for which the % characters need to be used.
This is a recommended solution from within this forum, when you need to use the % character within a LIKE clause, since when the syntax is entered into the straight Query of the dataset, Actuate will recognize it as a report parameter and try to do a substitution on the % characters.

For example (simplified) within the "beforeOpen" script...

this.queryText = this.queryText +
" AND (alertlog.ackoper = ackOper.netxoper AND ackOper.name LIKE '%" + params["cr_ackedBy"].value + "%')" +
" AND alertlog.first1 BETWEEN to_date('" + params["cr_FromTime"].value + "','YYYY-MM-DD HH:MI:SS') AND to_date('" + params["cr_ToTime"].value + "','YYYY-MM-DD HH:MI:SS')"

I have been unable to get the particular BETWEEN clause to work within the script for the substituted datetime parameters.

The same datetime parameters work fine when tested within the straight Query section of the dataset. For example...

alertlog.first1 BETWEEN % AND %

Works like a champ when cr_FromTime and cr_ToTime are substituted in the normal way for the query section for a dateset. Note the default values for these datetime parameters are "2001-01-01 01:00:00" and "2010-01-01 23:00:00" respectively.

The following SQL also works fine, when directly entered into SQL interactvely.

SELECT DISTINCT alertlog.ackOper FROM alertlog WHERE
alertlog.first1 BETWEEN to_date('2001-01-01 01:00:00','YYYY-MM-DD HH:MI:SS') AND to_date('2010-01-01 12:00:00','YYYY-MM-DD HH:MI:SS');

Note that the to_date is needed, since without it, BIRT will immediately return a mismatch, complaining that an numeric value is expected and a character value is encountered, when the dataset results are attempted to be previewed.

For the following string substituted and used within the script approach...

" AND alertlog.first1 BETWEEN to_date('" + params["cr_FromTime"].value + "','YYYY-MM-DD HH:MI:SS') AND to_date('" + params["cr_ToTime"].value + "','YYYY-MM-DD HH:MI:SS')"

I get the following error when the dataset results is previewed....

Error.
Cannot Execute the statement.
SQL statement does not return a ResultSet object.
SQL error #1; [ActuateDD][Oracle JDBC Driver][Oracle]ORA-081841: (full) year must be between -4713 and +9999, and not be 0.

I can imagine this example and issue may be difficult to follow, so if someone could provide an answer to the following question, it would be extremely helpful.

Is there any way to see the final sql from BIRT that is built up from the Query and script processing that is being sent to SQL when one submits a Preview Results of a dataset?

Thank you in advance,

Stinky

mwilliams

Hi Stinky,

You should be able to set a variable to the queryText value in your dataSet script with:

qText = this.queryText;

Then, you can display this variable value in your report in a dynamic text box after a report item that is bound to the dataSet to see what your final query text is.