topic ABAP: Native SQL in Application Development and Automation Discussions

ABAP: Native SQL

Former Member — Fri, 07 Oct 2011 11:06:03 GMT

Hi,

I have a requirement that is to fetch data from an oracle database.

My problem is I have a lot of tables, so I'm developping the code that suits all of them.

For example:

- I need a select statement like bellow (surrounded by EXEC... ENDEXEC):

SELECT column1, column2

FROM synonym

where column2 = :var

The problem is in variables column1 and column2. This is supposed to be dynamic,

but I cant do what i do with var (:var).

So, what's the syntax i should use here, if this is possible?

Regards,

Fábio.

Edited by: Fabio Ramalho on Oct 7, 2011 1:30 PM

Re: ABAP: Native SQL

Former Member — Fri, 07 Oct 2011 11:43:11 GMT

Hi,

Follow below syntax for dynamic where clause...

data: where_tab(30) occurs 1 with header line,

where_clause(30) type c.

Build the where clause. Will look like this when finished
WHERE ZAFSTMD02 = 'X' AND rbusa = '5145'

With a constant, result: ZAFSTMD01 = 'X'

concatenate 'ZAFSTMD' zcostcheck-zmaaned ' = ''X''' into where_clause.

Append to internal table where_tab

append where_clause to where_tab.

With a variable, result: AND rbusa = '5145'

concatenate 'AND rbusa = ' '''' i_tab-zgsber ''''

append where_clause to where_tab.

Select

select * from zcostfreq

where (where_tab).

endselect.

Note that you can combine static and dynamic where clauses:

select * from zcostfreq

where bukrs = '2021' AND

(where_tab).

endselect.

Ram.

Re: ABAP: Native SQL

Former Member — Fri, 07 Oct 2011 13:08:18 GMT

Hi Ram,

I tested it, it's not working.

I believe that code is valid for Open SQL, but what I'm talking about here is Native SQL (i.e. EXEC SQL statement).

I already tried to use execute immediate but it's not recognized in SAP.

Any other suggestions?

Thanks,

Fábio.

Re: ABAP: Native SQL

Pawan_Kesari — Fri, 07 Oct 2011 15:24:02 GMT

Any reason why you are going for OPEN SQL?

In ABAP you can define data and internal table dynamically.

Fields, tablename and where clause in SELECT statement can be dynamic.

Below code should solve your problem.

REPORT zpwtest6 .

START-OF-SELECTION .

* Select MATNR ERNAM from table MARA where ERNAM = 'KESARIP'
  PERFORM select_dynamic USING 'MATNR' 'ERNAM' 'MARA' 'KESARIP' .

* Select MATNR WERKS from table MARC where WERKS = '1020'
  PERFORM select_dynamic USING 'MATNR' 'WERKS' 'MARC' '1020' .

*&---------------------------------------------------------------------*
*&      Form  SELECT_DYNAMIC
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->FIELD1     text
*      -->FIELD2     text
*      -->TABLENAME  text
*      -->CONDITION  text
*----------------------------------------------------------------------*
FORM select_dynamic USING field1    TYPE char20
                          field2    TYPE char20
                          tablename TYPE char20
                          condition TYPE char20 .

  DATA : lv_columns TYPE char40  ,
         lv_where TYPE char40    .

  DATA : ref_tab TYPE REF TO data ,
         ref_stc TYPE REF TO data .

  FIELD-SYMBOLS : <fs_itab> TYPE ANY TABLE ,
                  <fs_stc>  TYPE ANY       ,
                  <fs_fld>  TYPE ANY       .

* Create table and structure of type specified in TABLENAME variable
  CREATE DATA ref_tab TYPE TABLE OF (tablename) .
  CREATE DATA ref_stc TYPE (tablename) .

  ASSIGN ref_tab->* TO <fs_itab> .
  ASSIGN ref_stc->* TO <fs_stc> .

* Build field names to select from table
  CONCATENATE field1 field2 INTO lv_columns SEPARATED BY space .
* Build where clause
  CONCATENATE field2 ' = '  '''' condition '''' INTO lv_where  .

* Select with dynamic fields, table name, and where clause
  SELECT  (lv_columns)
    INTO CORRESPONDING FIELDS OF TABLE <fs_itab>
    FROM (tablename)
    WHERE (lv_where) .

* Display data
  LOOP AT <fs_itab> ASSIGNING <fs_stc> .
    ASSIGN COMPONENT field1 OF STRUCTURE <fs_stc> TO <fs_fld> .
    IF sy-subrc = 0 .
      WRITE : <fs_fld> .
    ENDIF.

    ASSIGN COMPONENT field2 OF STRUCTURE <fs_stc> TO <fs_fld> .
    IF sy-subrc = 0 .
      WRITE : <fs_fld> .
    ENDIF.

    NEW-LINE.

  ENDLOOP.

ENDFORM.                    "SELECT_DYNAMIC

Re: ABAP: Native SQL

Former Member — Fri, 07 Oct 2011 15:44:15 GMT

Hi,

I'm not going for Open SQL, I believe that you meant to say Native SQL.

I'm familiar with the code that you posted,

but i'm using Native SQL because it's a connection to an oracle database.

Regards,

Fábio.

Re: ABAP: Native SQL

Pawan_Kesari — Fri, 07 Oct 2011 15:55:14 GMT

Yes (sorry), I meant Native SQL.

I suppose this is an external database.

I can see from SELECT help that open sql can be used with external database connection using

addiition CONNECTION {con|(con_syntax)} in SELECT statement

Well, I have never used it so .. I think I'll leave this to experts to answer.

Regards,

Pawan.

Re: ABAP: Native SQL

Former Member — Fri, 07 Oct 2011 15:59:05 GMT

Note we use DB2 dbms on AIx....

but here's how we do it....

EXEC SQL.
    CONNECT TO 'PAD'  "our alias for the other DB2 system
  ENDEXEC.

. . . . 

      EXEC SQL.
        SELECT DESCR.TEXT_DESC                                 "DESCR refers to db2 defined to SAP as DESCR
          FROM DESCRIPTION DESCR
          INTO :EX_TEXT_DESC                                          "target variable 
           WHERE DESCR.STATUS_CD       = 'ACT'                "literal 
             AND DESCR.USE_CD                 = :IM_USE_CD    "input parameter to this Function module
             AND DESCR.POLICY_ID             = :IM_POLICY_ID
             AND DESCR.POLICY_EFF_DT   = :LV_EFF_DT    "local variable created in code (char 8 mm-dd-ccyy)
             AND DESCR.USE_LOC_NBR     = :LV_ONE
             AND DESCR.DESC_SEQ_NBR   = :LV_ZERO
      ENDEXEC.
. . .
EXEC SQL.
      DISCONNECT 'PAD'
    ENDEXEC.

Edited by: DaveL on Oct 7, 2011 5:59 PM

Re: ABAP: Native SQL

Sandra_Rossi — Sat, 08 Oct 2011 06:01:15 GMT

Hi,

Using CONNECTION keyword on Open SQL is said "internal to SAP", so it shouldn't be used.

As you want to use full dynamic Native SQL (the column names), you'll have to use ADBC classes (it allows secondary database connections too) -> [ABAP documentation: ADBC - ABAP Database Connectivity|http://help.sap.com/abapdocu_702/en/index.htm?url=abenadbc.htm]

Sandra