1 Reply Latest reply: Jun 29, 2012 12:14 PM by Pnauduri-Oracle RSS

    small query not compiling

    691406
      I did convert this from sql server query. it is getting stuck on "AND (OEN.DATEONLY(N.CREATED_ON) BETWEEN OEN.DATEONLY(DATEFROM) AND OEN.DATEONLY(DATETHRU))"
      this error: PL/SQL: ORA-00904 "GEN"."DATEONLY" invalid identifier.




      CREATE OR REPLACE PROCEDURE OEN.DBD_NOT_GET_NOTES_DETAIL (
      FACILITYKEY varchar2
      , DATEFROM DATE
      , DATETHRU DATE
      , UNITSTR varchar2
      , NOTETYPE NUMERIC



      , OCURSOR OUT SYS_REFCURSOR
      ) as
      BEGIN
      OPEN OCURSOR FOR
      SELECT P.FACILITY_KEY,
      P.PAT_NUMBER,
      P.PATIENT_ID,
      OEN.DATEONLY(N.CREATED_ON) CREATED_ON, N.NOTE_HEADER,
      N.CREATED_BY, P.LAST_NAME, P.FIRST_NAME, P.MIDDLE_NAME, P.UNIT_CODE

      FROM OEN.GEN_M_PATIENT_MAST P
      INNER JOIN OTC.NOT_M_MAST N ON (P.PAT_NUMBER = N.PAT_NUMBER AND N.FACILITY_KEY = FACILITYKEY)


      WHERE N.NOTE_STATUS = 0

      AND (OEN.DATEONLY(N.CREATED_ON) BETWEEN OEN.DATEONLY(DATEFROM) AND OEN.DATEONLY(DATETHRU))


      AND CREATED_ON BETWEEN DATEFROM AND DATETHRU

      AND (UNITSTR IS NULL OR P.UNIT_CODE = UNITSTR);


      END;
        • 1. Re: small query not compiling
          Pnauduri-Oracle
          Hello

          There is no DATEONLY function in Oracle. It seems that it is a user defined function which needs to be ported to Oracle. Alternatively you can simply use the TRUNC() function on the date column to just return the DATE portion of the DATE+TIME column.

          So your query may look like TRUN(N.CREATED_ON) BETWEEN TRUNC(DATEFROM) AND TRUNC(DATETHRU)) assuming that CREATED_ON , DATEFROM, DATETHRU are of DATE datatypes in Oracle.

          Regards

          Prakash