Information Link and Oracle Function SYSDATE Unsucessful

The below code is sucessful running from Toad but is unsuccessful when running as an Information Link Custom SQL.  The Information Link does not seem to recognize the SYSDATE function.  Can anyone help with a workaround or explain how to use Oracle functions like SYSDATE in an Information Link?

TOAD (Successful)

Select * from whdw02cd.agt_certification_v a

where A.AGT_CERT_CD='A2O' 

and sysdate between A.AGT_CERT_EFDT and A.AGT_CERT_XPDT

 

Using SYSDATE

SELECT

      a1.`mk_id_no` AS `MKIDNO`,

      a1.`agt_cert_cd` AS `AGTCERTCD`,

      a1.`agt_cert_efdt` AS `AGTCERTEFDT`,

      a1.`agt_cert_xpdt` AS `AGTCERTXPDT`

   FROM

      `source_orap10_whdw02cd`.`agt_certification_v` a1

   WHERE

    a1.`agt_cert_cd`='A2O' and 

    (SYSDATE BETWEEN

   a1.`agt_cert_efdt`  AND  a1.`agt_cert_xpdt`)

Error message:  Failed to execute query: Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 10:5 Invalid table alias or column reference 'SYSDATE'

 

Using SYSDATE()

 SELECT

      a1.`mk_id_no` AS `MKIDNO`,

      a1.`agt_cert_cd` AS `AGTCERTCD`,

      a1.`agt_cert_efdt` AS `AGTCERTEFDT`,

      a1.`agt_cert_xpdt` AS `AGTCERTXPDT`

   FROM

      `source_orap10_whdw02cd`.`agt_certification_v` a1

   WHERE

    a1.`agt_cert_cd`='A2O' and 

    (SYSDATE()  BETWEEN

   a1.`agt_cert_efdt`  AND  a1.`agt_cert_xpdt`)

Error message:  Failed to execute query: Error while compiling statement: FAILED: SemanticException [Error 10011]: Line 10:5 Invalid function 'SYSDATE' (HRESULT: 80131500)

(1) Answer

Login