​host variable

[1190_5939_439]

Hi Experts

I read host variable's definition in the sap help. but I don't understand it . Following is Definition.

ABAP data object (usually a variable) specified in an operand position of an Open SQL statement or of a statically embedded Native SQL statement. In Open SQL, a host variable must be prefixed with the escape character @; in Native SQL, with the escape character :. In ADBC, the placeholder ? (to which ABAP data objects are bound) is used instead of host variables.

why do I use host variable in the following code ?

  SELECT
    eqbs~b_werk AS werks ,
    eqbs~b_lager AS lager,
    equi~matnr,
    equi~sernr
    INTO CORRESPONDING FIELDS OF TABLE @item
    FROM eqbs
    INNER JOIN equi ON eqbs~equnr = equi~equnr
    LEFT JOIN zrpp014 ON zrpp014~sernr = equi~sernr
    WHERE eqbs~b_werk = @werks
    AND eqbs~b_lager = @lager
    AND equi~sernr IN @s_sernr
    AND zrpp014~sernr IS NULL.

[mateuszadamus]

Hello 1190_5939_439

Host variables are used to transfer data from report to SQL query or from SQL query to report.

In your example you have both:

1. @item - transfers data read in SQL query to report and allows report to access and process it

2. @werks, @lager, @s_sernr - these transfer data from report to a SQL query this making its conditions dynamic. Without these you'd have to hardcode the WHERE condition's values and the SQL query would always return the same results.

Kind regards,
Mateusz

[1190_5939_439]

Hi Mateusz Expert

I have other one issue that how to have a fit name. For example. the host variables is '@item'. and '@werks' . Why don't we use '@item1' and '@werks1' ?

[matt]

You can call them anything you like, so long as they're defined somewhere.

DATA item TYPE STANDARD TABLE OF...  
DATA werks TYPE marc-werks.
...
SELECT
    eqbs~b_werk AS werks ,
    eqbs~b_lager AS lager,
    equi~matnr,
    equi~sernr
    INTO CORRESPONDING FIELDS OF TABLE @item
    FROM eqbs
    INNER JOIN equi ON eqbs~equnr = equi~equnr
    LEFT JOIN zrpp014 ON zrpp014~sernr = equi~sernr
    WHERE eqbs~b_werk = @werks
    AND eqbs~b_lager = @lager
    AND equi~sernr IN @s_sernr
    AND zrpp014~sernr IS NULL.

or

DATA item1 TYPE STANDARD TABLE OF...  
DATA werks1 TYPE marc-werks. 
...
 SELECT
    eqbs~b_werk AS werks ,
    eqbs~b_lager AS lager,
    equi~matnr,
    equi~sernr
    INTO CORRESPONDING FIELDS OF TABLE @item1
    FROM eqbs
    INNER JOIN equi ON eqbs~equnr = equi~equnr
    LEFT JOIN zrpp014 ON zrpp014~sernr = equi~sernr
    WHERE eqbs~b_werk = @werks1
    AND eqbs~b_lager = @lager
    AND equi~sernr IN @s_sernr
    AND zrpp014~sernr IS NULL.

or

DATA guardians_of_the TYPE STANDARD TABLE OF...  
DATA galaxy_part_two TYPE marc-werks. 
...
 SELECT
    eqbs~b_werk AS werks ,
    eqbs~b_lager AS lager,
    equi~matnr,
    equi~sernr
    INTO CORRESPONDING FIELDS OF TABLE @guardians_of_the
    FROM eqbs
    INNER JOIN equi ON eqbs~equnr = equi~equnr
    LEFT JOIN zrpp014 ON zrpp014~sernr = equi~sernr
    WHERE eqbs~b_werk = @galaxy_part_two
    AND eqbs~b_lager = @lager
    AND equi~sernr IN @s_sernr
    AND zrpp014~sernr IS NULL.

[1190_5939_439]

Why don't we define '@werks and @lager and @s_sernr in my programme?

TYPE-POOLS: slis.
TABLES: equi,eqbs,zrpp007,aufk.
DATA: gd_repid LIKE sy-repid.
DATA: i_grid_settings TYPE lvc_s_glay .
DATA: afield TYPE slis_fieldcat_alv.
DATA: fieldcat TYPE TABLE OF slis_fieldcat_alv WITH HEADER LINE.
DATA: layout TYPE slis_layout_alv.
DATA: l_pos TYPE i VALUE 1.
DATA:lcl_grid TYPE REF TO cl_gui_alv_grid.
DATA: i_values TYPE vrm_values WITH HEADER LINE.


TYPES: BEGIN OF ty_item,
         mark  TYPE char1,
         sel,
         werks LIKE eqbs-b_werk,
         lager LIKE eqbs-b_lager,
         matnr LIKE equi-matnr,
         sernr LIKE equi-sernr,
         lgobe LIKE t001l-lgobe,
         maktx LIKE makt-maktx,
         aufnr LIKE afko-aufnr,
         lgort TYPE lgort_d,
         lgobb LIKE t001l-lgobe,
         djhms LIKE zrpp014-djhms,
       END OF ty_item.
DATA: item TYPE STANDARD TABLE OF ty_item  WITH HEADER LINE.
DATA: lt_zrpp014 LIKE STANDARD TABLE OF zrpp014 WITH HEADER LINE.
**宏的定义
*DEFINE fill.
*  afield-col_pos = l_pos.
*  afield-fieldname = &1.
*  afield-seltext_l = &2.
*  afield-no_zero   = &3.
*  afield-key       = &4.
*  afield-edit      = &5.
*
*  append afield to fieldcat.
*  clear afield.
*  l_pos = l_pos + 1.
*END-OF-DEFINITION.

*二、声明查询屏幕
SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-002.
PARAMETERS: r1 RADIOBUTTON GROUP gr1 USER-COMMAND sele DEFAULT 'X'.
PARAMETERS: r2 RADIOBUTTON GROUP gr1.
SELECTION-SCREEN END OF BLOCK b2.

SELECTION-SCREEN BEGIN OF BLOCK blc WITH FRAME TITLE text-001.
PARAMETERS:
werks LIKE eqbs-b_werk OBLIGATORY DEFAULT '2200',
lager LIKE eqbs-b_lager OBLIGATORY DEFAULT '2201'.
SELECT-OPTIONS : s_sernr  FOR equi-sernr.
PARAMETERS  p_ywlx(16) AS LISTBOX VISIBLE LENGTH 12 OBLIGATORY   DEFAULT '1000'.   "lit 20210519
SELECTION-SCREEN END OF BLOCK blc.

INITIALIZATION.
  PERFORM f_init_cond.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_ywlx.
  PERFORM f_search_help_ywlx.

START-OF-SELECTION.
  PERFORM frm_get_data.                 "获取数据
  IF item[] IS NOT INITIAL.
    PERFORM frm_show_data.
  ELSE.
    MESSAGE s001(00) WITH '没有查询到数据'.
  ENDIF.
*&---------------------------------------------------------------------*
*&      Form  FRM_GET_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM frm_get_data .
  IF r1 = 'X'."未打印
    IF p_ywlx = '2000'.   "lit 20210519
      SELECT
    eqbs~b_werk AS werks ,
    eqbs~b_lager AS lager,
    equi~matnr,
    equi~sernr
    INTO CORRESPONDING FIELDS OF TABLE @item
    FROM eqbs
    INNER JOIN equi ON eqbs~equnr = equi~equnr
    INNER JOIN zrpp007 ON zrpp007~sernr = equi~sernr
    INNER JOIN AUFK ON aufk~aufnr = zrpp007~aufnr
    WHERE eqbs~b_werk = @werks
    AND eqbs~b_lager = @lager
    AND equi~sernr IN @s_sernr
    AND aufk~auart = 'PP03'.
    ENDIF.
    IF p_ywlx = '1000'.
      SELECT
    eqbs~b_werk AS werks ,
    eqbs~b_lager AS lager,
    equi~matnr,
    equi~sernr
    INTO CORRESPONDING FIELDS OF TABLE @item
    FROM eqbs
    INNER JOIN equi ON eqbs~equnr = equi~equnr
    LEFT JOIN zrpp014 ON zrpp014~sernr = equi~sernr
    WHERE eqbs~b_werk = @werks
    AND eqbs~b_lager = @lager
    AND equi~sernr IN @s_sernr
    AND zrpp014~sernr IS NULL.
*    AND equi~sernr NOT IN
*      ( SELECT sernr FROM zrpp014 )."多年以后需要在这里添加限制条件
    ENDIF.
  ELSEIF r2 = 'X'."已打印
*    SELECT
*    eqbs~b_werk AS werks
*    eqbs~b_lager AS lager
*    equi~matnr
*    equi~sernr
*    INTO CORRESPONDING FIELDS OF TABLE item
*    FROM eqbs
*    INNER JOIN equi ON eqbs~equnr = equi~equnr
*    WHERE eqbs~b_werk = werks
*    AND eqbs~b_lager = lager
*    AND equi~sernr IN s_sernr
*    AND equi~sernr IN
*  ( SELECT sernr FROM zrpp014 ).

    SELECT
      werks
      lgfsb AS lager
      matnr
      sernr
      aufnr
      lgort
      djhms
      INTO CORRESPONDING FIELDS OF TABLE item
      FROM zrpp014
      WHERE werks = werks
      AND lgfsb = lager
      AND zbsat = 'S'
      AND sernr IN s_sernr.

  ENDIF.<br>

[mateuszadamus]

It is defined. It's werks and lager parameters, and s_sernr select options.

Here is something for you to read about the host variables: https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abenopen_sql_host_variables.htm

Kind regards,

Mateusz

[matt]

You are forced to use @ because you're using commas(,) in the field list. If you omit those, you won't need the @.

However, using @ is good anyway - as it makes it clear what is a host variable. This becomes very important in some complex queries where it can become quite ambiguous.