Date field not working in Query definition

[Former Member]

Hello Experts ,

I am trying to prepare one report which will pull some fields ( extended fields ) from Rfx header page and display in the report . For this I have made one query definition and written SQL query . Now all fields except the date fields are successfully fetched and displayed by the query . Only when I am adding the date field inside "result field" tab , the query is throwing error like -

" ORA-00904: "EFFECTIVE_START_DATE": invalid identifier "

but this field name is correct .

Any formatting needs for this field ?

Regards

Sudipta

[Former Member]

Resolved

[Former Member]

Hi Sudipta,

Please let me know how you resolved this issue.

I am facing similar issue when I am trying to fetch an Extension Field in Result Fields.

Thanks,

Saloni

[Former Member]

Hi Saloni,

Date fields are appended with "_DATE "  while saving in DB. For an example-

EFFECTIVE_START_DATE is saved in DB  as   EFFECTIVE_START_DATE_DATE

Regards

Sudipta

[Former Member]

Hi Sudipta,

Thanks for your reply.

Actually I have created a diferent extension field and want to add that field in Results fields of particular Query Definition. But when I use it Tablename.Extension_Field_Name, its giving below exception

ORA-00904: "t"."fieldname": invalid identifier

Any idea what needs to be taken care of while using extension fields in Queries.

Thanks,

Saloni