topic Re: host variable in Application Development and Automation Discussions

host variable

1190_5939_439 — Tue, 18 May 2021 09:26:28 GMT

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.

Re: host variable

MateuszAdamus — Tue, 18 May 2021 09:29:53 GMT

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

Re: host variable

matt — Tue, 18 May 2021 11:41:43 GMT

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.

Re: host variable

1190_5939_439 — Wed, 19 May 2021 01:57:35 GMT

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' ?

Re: host variable

matt — Wed, 19 May 2021 06:34:33 GMT

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.

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.

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.

Re: host variable

1190_5939_439 — Thu, 20 May 2021 07:10:18 GMT

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>

Re: host variable

MateuszAdamus — Thu, 20 May 2021 07:39:19 GMT

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

topic Re: ​host variable in Application Development and Automation Discussions

​host variable

Re: ​host variable

Re: ​host variable

Re: ​host variable

Re: ​host variable

Re: ​host variable

Re: ​host variable

topic Re: host variable in Application Development and Automation Discussions

host variable

Re: host variable

Re: host variable

Re: host variable

Re: host variable

Re: host variable

Re: host variable