topic GRC 5.3 Open SQL select syntax question in Application Development and Automation Discussions

GRC 5.3 Open SQL select syntax question

former_member788628 — Sat, 27 Feb 2010 03:18:36 GMT

I am in opensql witin GRC and I am trying to select all the rows from this table but I want t restrict my where clause on the attribute ation date > 2/26/2010 but I need the syntax to convert the actiondate ( 2009-10-19 15:16:56.403 ) so that the where clause understands that I want rows with an actiondate > 2/25/2010.

select * from VIRSA_AE_RQ_HST

where pathname='AVESKA_AUTOAPPROVE'

Attribute (column table names ) for table VIRSA_AE_RQ_HST

REQNO

HST_ACTION

ACTIONDATE

USERID

ACTION_VALUE

DESCRIPTION

PATHNAME

STAGE_NAME

DEPENDENT_ID

Sample Data

1177 ROLE_APPROVED 2009-10-19 15:16:56.403 75000010 YRS:IT:GEN:USER_GEN D11CLNT100 AVESKA_AUTOAPPROVE AUTO_APPROVE 29179 29177

Desired SQL

SELECT * FROM VIRSA_AE_RQ_HST

WHERE

pathname='AVESKA_AUTOAPPROVE'

AND

actiondate > '2/25/2010'

Edited by: Rene Griffith on Feb 26, 2010 9:19 PM

Re: GRC 5.3 Open SQL select syntax question

Former Member — Tue, 02 Mar 2010 12:03:23 GMT

Hi,

Did you try actiondate > to_date('2/25/2010','mm/dd/yyyy')

Regards

Sudhir

Re: GRC 5.3 Open SQL select syntax question

former_member788628 — Fri, 12 Mar 2010 14:38:12 GMT

I tried executing this statement but getting this message in opensql

ERROR: The SQL statement "SELECT * FROM VIRSA_AE_RQ_HST WHERE pathname='AVESKA_AUTOAPPROVE' AND actiondate > to_date('2/25/2010','mm/dd/yyyy') " contains the syntax error[s]: - 5:21 - SQL syntax error: the token "(" was not expected here

Re: GRC 5.3 Open SQL select syntax question

Former Member — Fri, 12 Mar 2010 15:41:27 GMT

Hi Rene, In SQL server below query works fine, please try this

SELECT * FROM SAPEP7DB.VIRSA_AE_RQ_HST WHERE

PATHNAME='AVESKA_AUTOAPPROVE' and

ACTIONDATE > convert(char,'2/25/2010',101)

Regards

Re: GRC 5.3 Open SQL select syntax question

former_member788628 — Fri, 12 Mar 2010 15:57:28 GMT

Thanks but it is complaining about this too. This one is definitley puzzling with the date. Thanks for your persistsence to help.

ERROR: The SQL statement "SELECT * FROM SAPEP7DB.VIRSA_AE_RQ_HST WHERE PATHNAME='AVESKA_AUTOAPPROVE' and ACTIONDATE > convert(char,'2/25/2010',101) " contains the syntax error[s]: - 3:14 - Intermediate-SQL syntax error: CONVERT or TRANSLATE function not allowed - 3:22 - SQL syntax error: "CHAR" is a reserved keyword and cannot be used as an unquoted identifier