Question

9
Replies
1876
Views
Suribabu Member since 2016 1 post
HSBC
Posted: 2 years ago
Last activity: 1 year 6 months ago
Closed

Property DataType conversion from String to DateTime in Report Definition Filter

In Report Definition filter, one property is string which contains datetime value in GMT format.

pxcreatedatetime >= temppage.startdate

In above condition, startdate is string which should be changed to DateTime to check filter condition in report definition.

One FunctionAlias is created to change the datetime format with below code but runtime exception is occurred.

I cannot change the startdate to datetime at property level because this is used at many places and impact will be high at application level.

Error:
** An error occured on executing the query for the report definition - There was a problem getting a list: code: 902 SQLState: 42000 Message: ORA-00902: invalid datatype DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype | SQL Code: 902 | SQL State: 42000
Below is my code for Function Alias.
<p:choose>
<p:when test=".pyParameters(1).pyDBDataType == 'CLOB'">
CASE WHEN {1} IS NOT NULL
THEN
CAST ( CAST ({1} AS VARCHAR(4000)) AS DATETIME )
END
</p:when>
<p:otherwise>
CASE WHEN {1} IS NOT NULL
THEN
CAST ( {1} AS DATETIME )
END
</p:otherwise>
</p:choose>
Reporting
Moderation Team has archived post
Share this page LinkedIn