Native sql where clause w/ date

[Former Member]

Hi experts,

I am writing a program that needs to read an oracle database and bring thedata into SAP for comparing. I  am having trouble with the Native Sql command when I add a where clause that compares a date field. Below is the Oracle table structure, the structure of the SAP internal table, and the sql command.

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

PRTNUM                                    NOT NULL VARCHAR2(30)

LOTNUM                                    NOT NULL VARCHAR2(20)

EXPIRE_DTE                                         DATE

UNTQTY                                    NOT NULL NUMBER(10)

CATCH_QTY                                          NUMBER(19,4)

INVSTS                                    NOT NULL VARCHAR2(4)

LST_ARECOD                                         VARCHAR2(10)

MANDTE                                             DATE

LODNUM                                    NOT NULL VARCHAR2(30)

STOLOC                                    NOT NULL VARCHAR2(20)

TYPES: BEGIN OF dlx_ty,
  PRTNUM(30) TYPE c,                                  "Material
  LOTNUM(20) TYPE c,                                  "Batch #
EXPIRE_DTE TYPE d,                                 "Expiration Date
  CATCH_QTY TYPE p DECIMALS 4,              "Quantity
  INVSTS(4) TYPE c,                                      "Status
  MANDTE TYPE d,                                        "Manufacture Date
  STOLOC(20) TYPE c,                                   "Storage Location
END OF dlx_ty.

DATA: l_dlx TYPE dlx_ty,

          dlx_t TYPE STANDARD TABLE OF dlx_ty.

It works fine like this

    DO.
    EXEC SQL.
      OPEN feed FOR
      SELECT PRTNUM, LOTNUM, EXPIRE_DTE, CATCH_QTY, INVSTS, MANDTE, STOLOC
      FROM Z_DLX_DIST_INV_VIEW
    ENDEXEC.

  TRY.
    EXEC SQL.
     FETCH NEXT feed
     INTO :l_dlx-PRTNUM, :l_dlx-LOTNUM, :l_dlx-expire_dte, :l_dlx-catch_qty, :l_dlx-invsts, :l_dlx-mandte, :l_dlx-stoloc
    ENDEXEC.
    IF sy-subrc = '0'.
      APPEND l_dlx TO dlx_t.
    ENDIF.
  CATCH CX_SY_NATIVE_SQL_ERROR INTO EX.
    ex_text = EX->get_text( ).
    MESSAGE i208(00) WITH ex_text.
    LEAVE LIST-PROCESSING.
  ENDTRY.

    EXEC SQL.
      CLOSE feed
    ENDEXEC.
  ENDDO.

But when I add the where clause it raises an exception with the message:

An SQL error has occurred: ORA-01861: literal does not match format string#

DATA: i_date TYPE d.

EXEC SQL.

      OPEN feed FOR

      SELECT PRTNUM, LOTNUM, EXPIRE_DTE, CATCH_QTY, INVSTS, MANDTE, STOLOC

      FROM Z_DLX_DIST_INV_VIEW

      WHERE EXPIRE_DTE >= :I_DATE

ENDEXEC.

Any help would be greatly appreciated.

[edgar_nagasaki]

Hi Zachary,

What do you have in i_date variable?

Maybe you missed it when you shared the code but don't see this variable being filled anywhere.

A suggestion to try to isolate where issue lies would be to put a fixed value in your where clause, feel that's something wrong with your i_date content (it must be in format 'DD-MON-YY', eg. ''28-MAR-13').

Edgar

[RaymondGiuseppi]

For Oracle date format, youshiulduse the Gregorian calendar. You can specify an ANSI literal, as shown in this example:

DATE '1998-12-25'  

(ref Datetime Literals, DATE Datatype, 2 Basic Elements of Oracle SQL in Oracle Database SQL Reference)

Regards,

Raymond

[Former Member]

Thanks, I had switched the table I was retrieving from and the format of the date was different. We usually have our dates set up to match SAP date formats so they are comparable. The first table had the SAP format and the new one has the format '27-MAR-13'. The only issue I am having now is doing a greater than comparison with that format because of the text month, and without this way of limiting the search the amount of data available to retrieve times out the program and is continually growing.