This website is not affiliated with, sponsored by, or approved by SAP AG.

Issue with Native SQL.

Basis (Basis Technology Modules: Basis Component/System Administration, GUIs)

Moderators: Snowy, thx4allthefish

Issue with Native SQL.

Postby DrSidewalk » Wed Jul 30, 2014 5:52 am

I am extracting a huge amount of records into an itab, changing a few things around and then posting them into a temporary Z transparent table. This process is taking a long time for a number of reasons, the access path used against the two tables, the number of records returned, and the record length. The later two probably causing memory issues/paging.

To avoid it I would like to extract the data and insert it straight into the Z Temp transparent table. I'm using the following Native SQL statement. As an ex-Oracle developer I should not have an issue with this, but something is stopping it from working, and it's been many years since I worked with Native code. I don't have access to SQLPLUS either to test the code, but the main bulk of it works fine as an Open SQL statement, so it should work if I can get the syntax correct etc.

I receive this error when processing it: An SQL error has occurred: ORA-00911: invalid character

And here's the code. If anyone notices a possible issue then I would really appreciate any pointeres.

Code: Select all
  DATA: lv_exc_ref TYPE REF TO cx_sy_native_sql_error,
        lv_error_text TYPE string.
  TRY.

    EXEC SQL.
        INSERT INTO ZFI_FAEIBYPASSMT ( MANDT, ZKEY1, MATNR, BUDAT, MBLNR, MJAHR, ZEILE,
         VGART, BLART, CPUDT, CPUTM, USNAM, ZABLN, WAERS, BWART, WERKS, LGORT, CHARG, BWTAR,
         KZVBR, KZBEW, SOBKZ, KZZUG, BUSTM, BUSTW, MENGU, WERTU, SHKZG, MENGE, MEINS, DMBTR,
         DMBUM, XAUTO, KZBWS, SMBLN, SJAHR, SMBLP, KUNNR, KDAUF, EBELN, LE_EBELN, BUKRS,
         PRCTR, PPRCTR )
         SELECT :SY-MANDT AS MANDT, :GV_ZKEY1 AS ZKEY1,
             mseg~matnr,
             mkpf~budat,
             mkpf~mblnr,
             mkpf~mjahr,
             mseg~zeile,
             mkpf~vgart,
             mkpf~blart,
             mkpf~cpudt,
             mkpf~cputm,
             mkpf~usnam,
             mkpf~xabln,
             mseg~waers,
             mseg~bwart,
             mseg~werks,
             mseg~lgort,
             mseg~charg,
             mseg~bwtar,
             mseg~kzvbr,
             mseg~kzbew,
             mseg~sobkz,
             mseg~kzzug,
             mseg~bustm,
             mseg~bustw,
             mseg~mengu,
             mseg~wertu,
             mseg~shkzg,
             mseg~menge,
             mseg~meins,
             mseg~dmbtr,
             mseg~dmbum,
             mseg~xauto,
             mseg~kzbws,
             mseg~smbln,
             mseg~sjahr,
             mseg~smblp,
             mseg~kunnr,
             mseg~kdauf,
             mseg~ebeln,
             mkpf~le_vbeln,
             mseg~bukrs,
             mseg~prctr,
             mseg~pprctr
        FROM MSEG
        JOIN MKPF ON ( MKPF~MBLNR = MSEG~MBLNR AND MKPF~MJAHR = MSEG~MJAHR )
       WHERE MSEG~MATNR IN ( SELECT ZKEY4
                               FROM ZFI_FAEIBYPASS
                              WHERE ZKEY1 = :GV_ZKEY1
                                AND ZKEY2 = '1'
                                AND ZKEY3 = '' )
         AND MSEG~WERKS IN ( SELECT ZKEY4
                               FROM ZFI_FAEIBYPASS
                              WHERE ZKEY1 = :GV_ZKEY1
                                AND ZKEY2 = 'B'
                                AND ZKEY3 = '' )
         AND MKPF~BUDAT GE :LV_START_DATE
    ENDEXEC.
    IF sy-subrc <> 0.
      RAISE EXCEPTION TYPE cx_sy_native_sql_error.
    ENDIF.

    CATCH cx_sy_native_sql_error INTO lv_exc_ref.
      CLEAR: lv_error_text.
      lv_error_text  = lv_exc_ref->get_text( ).
      MESSAGE lv_error_text  TYPE 'I'.
  ENDTRY.



Dr Sidewalk (about to jump out of the 10th floor window...)
DrSidewalk
 
Posts: 180
Joined: Thu May 03, 2012 9:35 am

Re: Issue with Native SQL.

Postby Gothmog » Wed Jul 30, 2014 6:26 am

Code: Select all
AND MKPF~BUDAT GE :LV_START_DATE
Is GE for >= valid in Oracle?
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67
Gothmog
 
Posts: 1941
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Issue with Native SQL.

Postby DrSidewalk » Wed Jul 30, 2014 6:53 am

okay, changed 'GE' to '>=' although I'm sure 'GE' would be accepted in Oracle.
Still getting a 911 error though. So it still believes there's a special character somewhere. In SQLPLUS it would have informed me of exactly which character in the SQL statement caused grief, but I just get a single message.

Code now looks like this. Notice I have remove the quotes and now used all variants instead.
Years ago I would have resolved this kind of issue in minutes. Alas no more.

Code: Select all
  DATA: lv_exc_ref TYPE REF TO cx_sy_native_sql_error,
        lv_error_text TYPE string,
        lv_zkey1 TYPE CHAR3,
        lv_xmatnr TYPE CHAR1,
        lv_xwerks TYPE CHAR1,
        lv_null  TYPE CHAR1.

        lv_zkey1 = gv_zkey1.
        lv_xmatnr = '1'.
        lv_xwerks = 'B'.

  TRY.

    EXEC SQL.
        INSERT INTO ZFI_FAEIBYPASSMT ( MANDT, ZKEY1, MATNR, BUDAT, MBLNR, MJAHR, ZEILE,
         VGART, BLART, CPUDT, CPUTM, USNAM, ZABLN, WAERS, BWART, WERKS, LGORT, CHARG, BWTAR,
         KZVBR, KZBEW, SOBKZ, KZZUG, BUSTM, BUSTW, MENGU, WERTU, SHKZG, MENGE, MEINS, DMBTR,
         DMBUM, XAUTO, KZBWS, SMBLN, SJAHR, SMBLP, KUNNR, KDAUF, EBELN, LE_EBELN, BUKRS,
         PRCTR, PPRCTR )
         SELECT :SY-MANDT AS MANDT, :GV_ZKEY1 AS ZKEY1,
             mseg~matnr,
             mkpf~budat,
             mkpf~mblnr,
             mkpf~mjahr,
             mseg~zeile,
             mkpf~vgart,
             mkpf~blart,
             mkpf~cpudt,
             mkpf~cputm,
             mkpf~usnam,
             mkpf~xabln,
             mseg~waers,
             mseg~bwart,
             mseg~werks,
             mseg~lgort,
             mseg~charg,
             mseg~bwtar,
             mseg~kzvbr,
             mseg~kzbew,
             mseg~sobkz,
             mseg~kzzug,
             mseg~bustm,
             mseg~bustw,
             mseg~mengu,
             mseg~wertu,
             mseg~shkzg,
             mseg~menge,
             mseg~meins,
             mseg~dmbtr,
             mseg~dmbum,
             mseg~xauto,
             mseg~kzbws,
             mseg~smbln,
             mseg~sjahr,
             mseg~smblp,
             mseg~kunnr,
             mseg~kdauf,
             mseg~ebeln,
             mkpf~le_vbeln,
             mseg~bukrs,
             mseg~prctr,
             mseg~pprctr
        FROM MSEG
        JOIN MKPF ON ( MKPF~MBLNR = MSEG~MBLNR AND MKPF~MJAHR = MSEG~MJAHR )
       WHERE MSEG~MATNR IN ( SELECT ZKEY4
                               FROM ZFI_FAEIBYPASS
                              WHERE ZKEY1 = :GV_ZKEY1
                                AND ZKEY2 = :LV_XMATNR
                                AND ZKEY3 = :LV_NULL )
         AND MSEG~WERKS IN ( SELECT ZKEY4
                               FROM ZFI_FAEIBYPASS
                              WHERE ZKEY1 = :GV_ZKEY1
                                AND ZKEY2 = :LV_XWERKS
                                AND ZKEY3 = :LV_NULL )
         AND MKPF~BUDAT >= :LV_START_DATE
    ENDEXEC.
    IF sy-subrc <> 0.
      RAISE EXCEPTION TYPE cx_sy_native_sql_error.
    ENDIF.

    CATCH cx_sy_native_sql_error INTO lv_exc_ref.
      CLEAR: lv_error_text.
      lv_error_text  = lv_exc_ref->get_text( ).
      MESSAGE lv_error_text  TYPE 'I'.
  ENDTRY.


Dr Sidewalk
DrSidewalk
 
Posts: 180
Joined: Thu May 03, 2012 9:35 am

Re: Issue with Native SQL.

Postby hoinz » Thu Jul 31, 2014 6:35 am

The invalid character is the ~ I suppose.
Try . instead.
hoinz
 
Posts: 151
Joined: Tue Jan 15, 2008 5:56 am

Re: Issue with Native SQL.

Postby blueteeth » Sat Aug 02, 2014 1:05 pm

Columns with ~ (tilda) character need be enclosed in double quotes.

For example:

"mseg~matnr"

Best,
BT
blueteeth
 
Posts: 499
Joined: Sat Apr 05, 2008 12:22 pm

Re: Issue with Native SQL.

Postby hoinz » Mon Aug 04, 2014 6:21 am

Well, I agree that generally names with ~ (tilda) character need be enclosed in double quotes.
But here no tilda is needed in the first place. :wink:

In ABAP the separator between table name and column name is tilda,
Whereas in native SQL it is dot.

regards
hoinz
 
Posts: 151
Joined: Tue Jan 15, 2008 5:56 am

Re: Issue with Native SQL.

Postby Snowy » Tue Aug 05, 2014 8:20 am

you must use Native SQL. the entries with tilda is NOT native SQL !

see a few ABAP native SQL coding on the wen to get an idea such as http://scn.sap.com/thread/233601
SapFans Moderator

Search: http://www.sapfans.com/forums/search.php
Notes: http://service.sap.com/notes
Help: http://help.sap.com
Rules: http://www.sapfans.com/forums/viewtopic.php?t=344127
Snowy
 
Posts: 28767
Joined: Mon Oct 21, 2002 2:33 pm
Location: 3.1415926535

Re: Issue with Native SQL.

Postby blueteeth » Tue Aug 05, 2014 3:33 pm

How many times we have encountered tilda as part of Basis work? Many times during reorgs and index rebuilds, upgrades, Oracle related notes etc. SAP or ABAP or whatever has this habit of introducing tilda in data dictionary objects. Oracle does not take this kindly (tilda is a special character in Oracle SQL as NOT) and SAP tools like SAPDBA nicely enclose them in double quotes (or at times, we need to enclose them in double quotes).

What was the argument again?

Cheers
blueteeth
 
Posts: 499
Joined: Sat Apr 05, 2008 12:22 pm

Re: Issue with Native SQL.

Postby Snowy » Wed Aug 06, 2014 10:55 am

his SQL:

EXEC SQL.
INSERT INTO ZFI_FAEIBYPASSMT ( MANDT, ZKEY1, MATNR, BUDAT, MBLNR, MJAHR, ZEILE,
VGART, BLART, CPUDT, CPUTM, USNAM, ZABLN, WAERS, BWART, WERKS, LGORT, CHARG, BWTAR,
KZVBR, KZBEW, SOBKZ, KZZUG, BUSTM, BUSTW, MENGU, WERTU, SHKZG, MENGE, MEINS, DMBTR,
DMBUM, XAUTO, KZBWS, SMBLN, SJAHR, SMBLP, KUNNR, KDAUF, EBELN, LE_EBELN, BUKRS,
PRCTR, PPRCTR )
SELECT :SY-MANDT AS MANDT, :GV_ZKEY1 AS ZKEY1,
mseg~matnr,
mkpf~budat,
mkpf~mblnr,
mkpf~mjahr,
mseg~zeile,
mkpf~vgart,
mkpf~blart,
mkpf~cpudt,
mkpf~cputm,
mkpf~usnam,
mkpf~xabln,
mseg~waers,
mseg~bwart,

... blah blah blah ....

these tilda are not Native SQL syntax
SapFans Moderator

Search: http://www.sapfans.com/forums/search.php
Notes: http://service.sap.com/notes
Help: http://help.sap.com
Rules: http://www.sapfans.com/forums/viewtopic.php?t=344127
Snowy
 
Posts: 28767
Joined: Mon Oct 21, 2002 2:33 pm
Location: 3.1415926535


Return to Basis

Who is online

Users browsing this forum: Google [Bot] and 5 guests





loading...


This website is not affiliated with, sponsored by, or approved by SAP AG.