topic Re: Problems using EXEC SQL in Application Development and Automation Discussions

Problems using EXEC SQL

Former Member — Tue, 30 Aug 2011 08:51:01 GMT

Hello everybody,

I have a problem using EXEC SQL. Not the command itself but a conflict with our naming conventions.

In Open SQL I can simply use a field called EKKO~/SOL/BESTNR_EX and get the result. Due to a runtime problem I tried to use Native SQL via EXEC SQL. But as soon as I try to use this field I get a dump.

Does anybody know how to use a field named like this in Native SQL?


  TYPES:
        BEGIN OF ls_test,
          lifnr TYPE elifn,
          matnr TYPE matnr,
          arktx TYPE arktx,
          werks TYPE werks_d,
          ean11 TYPE ean11,
          vgbel TYPE vgbel,
          posnr TYPE posnr_vl,
          pstyp type pstyp,
          bsart type esart,
          /sol/bestnr_ex TYPE /sol/mm_bestnr_ex,
          menge TYPE bstmg,
          elikz TYPE elikz,
          erekz TYPE erekz,
        END OF ls_test.

  DATA:
          ls_test TYPE ls_test.

    EXEC SQL.
      OPEN c1 FOR
SELECT EKKO.LIFNR, EKPO.MATNR, EKPO.TXZ01, EKPO.WERKS, EKPO.EAN11, EKKO.EBELN, EKPO.EBELP, EKPO.PSTYP, EKKO.BSART, EKKO./SOL/BESTNR_EX, EKPO.MENGE, EKPO.ELIKZ, EKPO.EREKZ
FROM EKKO
INNER JOIN EKPO ON EKKO.EBELN = EKPO.EBELN
    ENDEXEC.

    DO.
      EXEC SQL.
        FETCH NEXT c1 INTO :LS_TEST
      ENDEXEC.
      IF sy-subrc <> 0.
        EXIT.
      ELSE.
        WRITE LS_TEST-LIFNR.
      ENDIF.
    ENDDO.
    EXEC SQL.
      CLOSE c1
    ENDEXEC.

Before anyone asks the final SQL statement will be much bigger, but this sample shows the situation.

best regards

Stephan Knecht

Edited by: stknec on Aug 30, 2011 10:51 AM

Edited by: stknec on Aug 30, 2011 10:52 AM

Re: Problems using EXEC SQL

SuhaSaha — Tue, 30 Aug 2011 09:00:35 GMT

Hello Stephan,

In Open SQL I can simply use a field called EKKO~/SOL/BESTNR_EX and get the result. Due to a runtime problem I tried to use Native SQL via EXEC SQL. But as soon as I try to use this field I get a dump.

Can you elaborate what runtime error you're experiencing with Open & Native SQL?

Before anyone asks the final SQL statement will be much bigger, but this sample shows the situation.

If i were in your place, i would definitely post the entire SQL construct(both Open & Native)!

BR,

Suhas

Re: Problems using EXEC SQL

franois_henrotte — Tue, 30 Aug 2011 09:08:19 GMT

you should put back quotes around the field name as it contains special characters (in this case the slash)

Re: Problems using EXEC SQL

deepak_dhamat — Tue, 30 Aug 2011 09:44:27 GMT

Hi ,

Just for curiosity .. why i can't find this field

EKKO./SOL/BESTNR_EX

in ekko table

regards

Deepak.

Re: Problems using EXEC SQL

matt — Tue, 30 Aug 2011 09:48:32 GMT

Probably because it's part of an an add-in.

Re: Problems using EXEC SQL

Former Member — Tue, 30 Aug 2011 09:58:25 GMT

@for Deepak Dhamat:

The field is an include from us and as we told by SAP it is in our namespace /SOL/

@Suhas Saha:

I'm sorry I forgot the error message, it's included in this post. I've seen no use in posting the whole sql since a small part of it shows the problem, but as you wished here is the complete native SQL, there is no counterpart in Open SQL because this SQL wont work in Open SQL, it has been done in some sqls which are Open SQL compatible (since there is more then one join).

ERROR MESSAGE:


Runtime Errors         DBIF_DSQL2_SQL_ERROR
Except.                CX_SY_NATIVE_SQL_ERROR
Date and Time          30.08.2011 11:45:00
 Short text
     An SQL error occurred when executing Native SQL.
 What happened?
     The error "-104" occurred in the current database connection "DEFAULT".
How to correct the error
    Database error text........: "SQL0104N An unexpected token "/" was found
     following "P, EKKO.BSART, EKKO.". Expected tokens may include: "<space>".
     SQLSTATE=42601"
    Database error code........: "-104"
    Triggering SQL statement...: "SELECT EKKO.LIFNR, EKPO.MATNR, EKPO.TXZ01,
     EKPO.WERKS, EKPO.EAN11, EKKO.EBELN, EKPO.EBELP, EKPO.PSTYP, EKKO.BSART,
     EKKO./SOL/BESTNR_EX, EKPO.MENGE, EKPO.ELIKZ, EKPO.EREKZ FROM EKKO INNER JOIN
     EKPO ON EKKO.EBELN = EKPO.EBELN"
    Internal call code.........: "[DBDS/NEW DSQL]"
    Please check the entries in the system log (Transaction SM21).
    If the error occures in a non-modified SAP program, you may be able to
    find an interim solution in an SAP Note.
    If you have access to SAP Notes, carry out a search with the following
    keywords:
    "DBIF_DSQL2_SQL_ERROR" "CX_SY_NATIVE_SQL_ERROR"
    "/SOL/CL_MM_ASA================CP" or "/SOL/CL_MM_ASA================CM00C"
    "BUILD_DATA"
    If you cannot solve the problem yourself and want to send an error
    notification to SAP, include the following information:
    4. Details about the conditions under which the error occurred or which
    actions and input led to the error.
    The exception must either be prevented, caught within proedure
    "BUILD_DATA" "(METHOD)", or its possible occurrence must be declared in the
    RAISING clause of the procedure.
    To prevent the exception, note the following:
System environment
    SAP-Release 701
    Application server... "xxxxxxxxx"
    Network address...... "xxxxxxxxx"
    Operating system..... "AIX"
    Release.............. "6.1"
    Hardware type........ "000D014AD400"
    Character length.... 16 Bits
    Pointer length....... 64 Bits
    Work process number.. 0
    Shortdump setting.... "full"
    Database server... "xxxxxxxxxx"
    Database type..... "DB6"
    Database name..... "E01"
    Database user ID.. "SAPSR3"
    Terminal.......... "xxxxxxxxxx"
    Char.set.... "C"
    SAP kernel....... 701
    created (date)... "Oct 31 2010 21:35:22"
    create on........ "AIX 2 5 00CB5A5B4C00"
    Database version. "DB6_81 "
    Patch level. 117
    Patch text.. " "
    Database............. "DB6 08.02., DB6 09."
    SAP database version. 701
    Operating system..... "AIX 1 5, AIX 2 5, AIX 3 5, AIX 1 6, AIX 1 7"
    Memory consumption
    Roll.... 16192
    EM...... 12569544
    Heap.... 0
    Page.... 73728
    MM Used. 1325488
    MM Free. 2861760

best regards

Stephan Knecht

Edited by: Suhas Saha on Aug 30, 2011 3:31 PM

Re: Problems using EXEC SQL

Former Member — Tue, 30 Aug 2011 10:03:02 GMT

Hm, I used but it does not work.

NATIVE SQL STATEMENT:


      SELECT EKKO.MANDT, RES2.VBELN, EKKO.EBELN, RES3.BELNR, EKPO.MATNR, RES2.POSNR, EKPO.WERKS, EKPO.EBELP, RES2.LIFEX, RES2.LIFNR, RES2.LFDAT, RES2.ARKTX, RES2.LFIMG, EKPO.EAN11, EKKO.BSART, EKKO./SOL/BESTNR_EX, EKPO.MENGE, EKPO.ELIKZ, EKPO.EREKZ,
RES3.XBLNR, RES3.RMENGE, '' AS STATUS, CURRENT_DATE AS DBDAT, EKPO.PSTYP
      FROM EKKO
      INNER JOIN EKPO ON EKKO.EBELN = EKPO.EBELN
      INNER JOIN /SOL/MM_ASA_E ON /SOL/MM_ASA_E.EBELN = EKKO.EBELN
      LEFT OUTER JOIN (
        SELECT DISTINCT LIKP.LIFNR, LIKP.VBELN, LIKP.LFDAT, LIKP.LIFEX, LIPS.ARKTX, LIPS.POSNR, LIPS.LFIMG, LIPS.WERKS, LIPS.VGBEL, LIPS.VGPOS, LIPS.EAN11
        FROM LIKP
        INNER JOIN LIPS ON LIKP.VBELN=LIPS.VBELN
        INNER JOIN /SOL/MM_ASA_E ON /SOL/MM_ASA_E.EBELN = LIPS.VGBEL WHERE LIPS.MATNR IS NOT NULL) AS RES2 ON RES1.EBELN = RES2.VGBEL
      LEFT OUTER JOIN (
        SELECT RSEG.EBELN, RSEG.EBELP, RBKP.BELNR, RBKP.XBLNR, SUM(RSEG.MENGE) AS RMENGE
        FROM RBKP
        INNER JOIN RSEG ON RBKP.BELNR=RSEG.BELNR AND RBKP.GJAHR=RSEG.GJAHR
        INNER JOIN /SOL/MM_ASA_E ON /SOL/MM_ASA_E.EBELN=RSEG.EBELN
        GROUP BY RSEG.EBELN, RSEG.EBELP, RBKP.BELNR, RBKP.XBLNR) AS RES3 ON RES1.EBELN=RES3.EBELN AND RES1.EBELP=RES3.EBELP
      LEFT OUTER JOIN (
        SELECT EBKE.EBELN, EKBE.EBELP, SUM(CASE WHEN VGABE = 1 THEN MENGE ELSE 0 END) AS V1MENGE, SUM(CASE WHEN VGABE = 2 THEN MENGE ELSE 0 END) AS V2MENGE, SUM(CASE WHEN VGABE = 6 THEN MENGE ELSE 0 END) AS V6MENGE
        FROM EKBE
        INNER JOIN /SOL/MM_ASA_E ON /SOL/MM_ASA_E.EBELN = EKBE.EBELN
        GROUP BY EKBE.EBELN, EKBE.EBELP) AS RES4 ON RES1.EBELN=RES4.EBELN AND RES1.EBELP=RES4.EBELP

Moderator Message: I've done that for you

Edited by: Suhas Saha on Aug 30, 2011 3:37 PM

Re: Problems using EXEC SQL

Former Member — Tue, 30 Aug 2011 10:09:26 GMT

ERROR MESSAGE:

Runtime Errors         DBIF_DSQL2_SQL_ERROR
Except.                CX_SY_NATIVE_SQL_ERROR
Date and Time          30.08.2011 11:45:00

 Short text
     An SQL error occurred when executing Native SQL.

 What happened?
     The error "-104" occurred in the current database connection "DEFAULT".

 What can you do?
     Note down which actions and inputs caused the error.

     To process the problem further, contact you SAP system
     administrator.

     Using Transaction ST22 for ABAP Dump Analysis, you can look
     at and manage termination messages, and you can also
     keep them for a long time.

How to correct the error
    Database error text........: "SQL0104N An unexpected token "/" was found
     following "P, EKKO.BSART, EKKO.". Expected tokens may include: "<space>".
     SQLSTATE=42601"
    Database error code........: "-104"
    Triggering SQL statement...: "SELECT EKKO.LIFNR, EKPO.MATNR, EKPO.TXZ01,
     EKPO.WERKS, EKPO.EAN11, EKKO.EBELN, EKPO.EBELP, EKPO.PSTYP, EKKO.BSART,
     EKKO./SOL/BESTNR_EX, EKPO.MENGE, EKPO.ELIKZ, EKPO.EREKZ FROM EKKO INNER JOIN
     EKPO ON EKKO.EBELN = EKPO.EBELN"
    Internal call code.........: "[DBDS/NEW DSQL]"
    Please check the entries in the system log (Transaction SM21).

    If the error occures in a non-modified SAP program, you may be able to
    find an interim solution in an SAP Note.
    If you have access to SAP Notes, carry out a search with the following
    keywords:

    "DBIF_DSQL2_SQL_ERROR" "CX_SY_NATIVE_SQL_ERROR"
    "/SOL/CL_MM_ASA================CP" or "/SOL/CL_MM_ASA================CM00C"
    "BUILD_DATA"

    If you cannot solve the problem yourself and want to send an error
    notification to SAP, include the following information:

    1. The description of the current problem (short dump)

       To save the description, choose "System->List->Save->Local File
    (Unconverted)".

    2. Corresponding system log

       Display the system log by calling transaction SM21.
       Restrict the time interval to 10 minutes before and five minutes
    after the short dump. Then choose "System->List->Save->Local File
    (Unconverted)".

    3. If the problem occurs in a problem of your own or a modified SAP
    program: The source code of the program
       In the editor, choose "Utilities->More
    Utilities->Upload/Download->Download".

    4. Details about the conditions under which the error occurred or which
    actions and input led to the error.

    The exception must either be prevented, caught within proedure
    "BUILD_DATA" "(METHOD)", or its possible occurrence must be declared in the
    RAISING clause of the procedure.
    To prevent the exception, note the following:

System environment
    SAP-Release 701

    Application server... "xxxxxxxxx"
    Network address...... "xxxxxxxxx"
    Operating system..... "AIX"
    Release.............. "6.1"
    Hardware type........ "000D014AD400"
    Character length.... 16 Bits
    Pointer length....... 64 Bits
    Work process number.. 0
    Shortdump setting.... "full"

    Database server... "xxxxxxxxxx"
    Database type..... "DB6"
    Database name..... "E01"
    Database user ID.. "SAPSR3"

    Terminal.......... "xxxxxxxxxx"

    Char.set.... "C"

    SAP kernel....... 701
    created (date)... "Oct 31 2010 21:35:22"
    create on........ "AIX 2 5 00CB5A5B4C00"
    Database version. "DB6_81 "

    Patch level. 117
    Patch text.. " "

    Database............. "DB6 08.02.*, DB6 09.*"
    SAP database version. 701
    Operating system..... "AIX 1 5, AIX 2 5, AIX 3 5, AIX 1 6, AIX 1 7"

    Memory consumption
    Roll.... 16192
    EM...... 12569544
    Heap.... 0
    Page.... 73728
    MM Used. 1325488
    MM Free. 2861760

Re: Problems using EXEC SQL

Former Member — Tue, 30 Aug 2011 10:09:49 GMT

It seems there can not be two segments in one post.

Thanks Suhas

Re: Problems using EXEC SQL

deepak_dhamat — Tue, 30 Aug 2011 11:01:05 GMT

Hi Matt/Suhas ,

After searching i came to this NOTE

Note 1145460 - DB6: DBA Cockpit - error due to unquoted partitioning keys

Symptom

In the DBA Cockpit, you use the BI Data Distribution Wizard to distribute data accross new database partitions.
 When you execute the selected actions at the end of the wizard, you receive an SQL error -104.

Other terms

SQL0104N, add partition,

Reason and Prerequisites

Partitioning key column names in the generated DDL are not quoted.

Solution

SAP Basis 7.00: Import Basis Support Package 16.
SAP Basis 7.10: Import Basis Support Package 6.

Is this relevant to the error ....

regards

Deepak.

Re: Problems using EXEC SQL

matt — Tue, 30 Aug 2011 11:19:40 GMT


It seems there can not be two  segments in one post.
> 
> Thanks Suhas

yes

there can

Put the field name in double quotes

   EXEC SQL.
      OPEN c1 FOR
          SELECT EKKO.LIFNR, EKPO.MATNR, EKPO.TXZ01, 
                 EKPO.WERKS, EKPO.EAN11, EKKO.EBELN, EKPO.EBELP, 
                 EKPO.PSTYP, EKKO.BSART, "EKKO./SOL/BESTNR_EX", 
                 EKPO.MENGE, EKPO.ELIKZ, EKPO.EREKZ
    FROM EKKO
    INNER JOIN EKPO ON EKKO.EBELN = EKPO.EBELN
  ENDEXEC.

Re: Problems using EXEC SQL

Former Member — Tue, 30 Aug 2011 11:34:09 GMT

Double quoting kinda helped, but I get a new error message. Since the only name I know for this field is

EKKO~/SOL/BESTNR_EX I don't know it's name on the database itself. Since I have no access direct to the database I cannot have a look on the physical table to see it's name.

The SQLSTATE 42703 says that the field does not exist.

Re: Problems using EXEC SQL

Former Member — Tue, 30 Aug 2011 11:37:37 GMT

New error:

Runtime Errors         DBIF_DSQL2_SQL_ERROR
Except.                CX_SY_NATIVE_SQL_ERROR
Date and Time          30.08.2011 13:28:03

What happened?
    The error "-206" occurred in the current database connection "DEFAULT".

How to correct the error
    Database error text........: "SQL0206N "EKKO./SOL/BESTNR_EX" is not valid in
     the context where it is used. SQLSTATE=42703"
    Database error code........: "-206"
    Triggering SQL statement...: "SELECT EKKO.LIFNR, EKPO.MATNR, EKPO.TXZ01,
     EKPO.WERKS, EKPO.EAN11, EKKO.EBELN, EKPO.EBELP, EKPO.PSTYP, EKKO.BSART,
     "EKKO./SOL/BESTNR_EX", EKPO.MENGE, EKPO.ELIKZ, EKPO.EREKZ FROM EKKO INNER JOIN
     EKPO ON EKKO.EBELN = EKPO.EBELN"
    Internal call code.........: "[DBDS/NEW DSQL]"
    Please check the entries in the system log (Transaction SM21).

    If the error occures in a non-modified SAP program, you may be able to
    find an interim solution in an SAP Note.
    If you have access to SAP Notes, carry out a search with the following
    keywords:

    "DBIF_DSQL2_SQL_ERROR" "CX_SY_NATIVE_SQL_ERROR"
    "/SOL/CL_MM_ASA================CP" or "/SOL/CL_MM_ASA================CM00C"
    "BUILD_DATA"

Re: Problems using EXEC SQL

Former Member — Tue, 30 Aug 2011 11:44:58 GMT

EXEC SQL.
      OPEN c1 FOR
          SELECT EKKO.LIFNR, EKPO.MATNR, EKPO.TXZ01, 
                 EKPO.WERKS, EKPO.EAN11, EKKO.EBELN, EKPO.EBELP, 
                 EKPO.PSTYP, EKKO.BSART, EKKO."/SOL/BESTNR_EX", 
                 EKPO.MENGE, EKPO.ELIKZ, EKPO.EREKZ
    FROM EKKO
    INNER JOIN EKPO ON EKKO.EBELN = EKPO.EBELN
  ENDEXEC.

The solution is, to only quote the field, and not the table qualifier.

thanks to all, especially Matt.

best regards

Stephan Knecht

Re: Problems using EXEC SQL

franois_henrotte — Wed, 31 Aug 2011 09:48:47 GMT

I told you "around the field name" )

Re: Problems using EXEC SQL

Former Member — Wed, 31 Aug 2011 10:01:30 GMT

Yes, but since the back quotes produced the same error as I had before I could not really fix my problem.