topic Re: Native sql where clause w/ date in Application Development and Automation Discussions

Native sql where clause w/ date

Former Member — Thu, 28 Mar 2013 16:21:39 GMT

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.

Re: Native sql where clause w/ date

edgar_nagasaki — Thu, 28 Mar 2013 18:10:29 GMT

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

Re: Native sql where clause w/ date

RaymondGiuseppi — Thu, 28 Mar 2013 18:38:18 GMT

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

Re: Native sql where clause w/ date

Former Member — Thu, 28 Mar 2013 21:05:14 GMT

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.