dynamic where condition based on tables

[Former Member]

Friends,

I'm creating a dynamic table now need to pass the where condition also dynamically. can anybody suggest?

Many thanks,

Albert.

[eddy_declercq]

Hi,

Do you mean something like this?

sel = 'some fields'.

tab = 'a tab'.

cond = 'a condition'.

select (sel)

from (tab) into corresponding fields of a_tab

where (cond).

..

endselect.

Eddy

PS.

Put yourself on the SDN world map (http://sdn.idizaai.be/sdn_world/sdn_world.html) and earn 25 points.

Spread the wor(l)d!

[uwe_schieferstein]

Hello Albert

If you are already working an 6.40 (or higher) than you can simply concatenate the WHERE condition into a string and code:

DATA:
  gd_where_conditions  TYPE string.

SELECT * FROM ...
   WHERE ( gd_where_conditions ).

However, if you are working on 6.20 (or less) than you could use the following function module to create your dynamic WHERE condition(s): <b>RH_DYNAMIC_WHERE_BUILD</b>

The function module returns the WHERE conditions in CONDTAB. Thus, you could code:

TYPES: BEGIN OF ty_s_clause.
TYPES:   line(72)  TYPE c.
TYPES: END OF ty_s_clause.
DATA:
  gt_where_clauses  TYPE STANDARD TABLE OF ty_s_clause
                    WITH DEFAULT KEY.
DATA:
  gt_condtab     TYPE STANDARD TABLE OF hrcond.

* Fill gt_condtab with the required conditions
  CALL FUNCTION 'RH_DYNAMIC_WHERE_BUILD'
       EXPORTING
            dbtable         = space " can be empty
       TABLES
            condtab         = gt_condtab
            where_clause    = gt_where_clauses
       EXCEPTIONS
            empty_condtab   = 01
            no_db_field     = 02
            unknown_db      = 03
            wrong_condition = 04.

* Select your data
  SELECT * FROM ...
    WHERE (gt_where_clauses).

Regards

Uwe

[alexander_schuchman]

This is exactly what I was looking for, I'm trying to do dynamic wheres in 4.6C. Thanks for saving me hours of research!

-Alex

[Former Member]

Hi,

DATA: vbap_where type standard table of rsdswhere,

say SO_VKORG is selection screen parameter for sales organization,

concatenate 'VKORG' 'IN' 'SO_VKORG' into vbap_where

separated by space.

append wa_tplnr_where to vbap_where.

SELECT vbeln bstnk

FROM vbak

INTO TABLE gt_vbak

WHERE vbap_where.

this is same as

SELECT vbeln bstnk

FROM vbak

INTO TABLE gt_vbak

WHERE vkorg IN so_vkorg.

Regards,

Raghavendra

[Former Member]

hi,

A simple program which shows how to use dynamic where.

IT_VBAK contains data based on the dynamic where condition.

TABLES : VBAK.

DATA: BEGIN OF IT_VBAK OCCURS 0,
        VBELN LIKE VBAK-VBELN,
        ERDAT LIKE VBAK-ERDAT,
        VBTYP LIKE VBAK-VBTYP,
        NETWR LIKE VBAK-NETWR,
        WAERK LIKE VBAK-WAERK,
        VKORG LIKE VBAK-VKORG,
        VTWEG LIKE VBAK-VTWEG,
        SPART LIKE VBAK-SPART,
        INDIC LIKE ICON-ID,
        ERDAT_N(10) TYPE C,
        SYMBL_N(2) TYPE C,
        CHKBX_N TYPE C,
      END OF IT_VBAK.

DATA: V_WHERE TYPE STRING.

SELECT-OPTIONS : S_VBELN FOR VBAK-VBELN,
                 S_ERDAT FOR VBAK-ERDAT.


INITIALIZATION.
  S_VBELN-LOW = '10000'.
  S_VBELN-HIGH = '10025'.
  S_VBELN-SIGN = 'I'.
  S_VBELN-OPTION = 'EQ'.
  APPEND S_VBELN. CLEAR S_VBELN.

START-OF-SELECTION.

  PERFORM POPULATE_WHERE.


  SELECT VBELN
         ERDAT
         VBTYP
         NETWR
         WAERK
         VKORG
         VTWEG
         SPART
    INTO TABLE IT_VBAK
    FROM VBAK
   WHERE (V_WHERE).

  IF SY-SUBRC = 0.
  ENDIF.

*&---------------------------------------------------------------------*
*&      Form  POPULATE_WHERE
*&---------------------------------------------------------------------*
*       Populate Where
*----------------------------------------------------------------------*
FORM POPULATE_WHERE .

  IF NOT S_ERDAT[] IS INITIAL.
    CONCATENATE 'VBELN IN S_VBELN'
                'AND'
                'ERDAT IN V_ERDAT'
          INTO V_WHERE
          SEPARATED BY SPACE.
  ELSE.
    V_WHERE = 'VBELN IN S_VBELN'.
  ENDIF.

ENDFORM.                    " POPULATE_WHERE

Regards,

Sailaja.

[Former Member]

hi Albert,

REPORT YA002DYN002 .
TABLES: SCUSTOM.
  DATA: ITAB_SCUSTOM LIKE SCUSTOM OCCURS 0 WITH HEADER LINE.
  DATA: STR_WHERE TYPE TABLE OF EDPLINE.
  DATA: STR_LINE TYPE EDPLINE.
  PARAMETERS: NAMELIKE(20) TYPE C DEFAULT 'A' OBLIGATORY.
  PARAMETERS: OTHERCON(50) TYPE C DEFAULT 'ID BETWEEN 1 AND 99'.
  CONCATENATE 'NAME LIKE ''' NAMELIKE '%''' INTO STR_LINE.

  IF OTHERCON <> ''.
   CONCATENATE STR_LINE 'AND' OTHERCON '' INTO STR_LINE
   SEPARATED BY SPACE.
 ENDIF.
 APPEND STR_LINE TO STR_WHERE.
<b> SELECT * FROM SCUSTOM INTO TABLE ITAB_SCUSTOM WHERE (STR_WHERE).</b>

 WRITE: / 'CUST ID',
          11 'CUSTOMER NAME',
          40 'STREET ADDRESS',
          59 'CITY',
          75 'COUNTRY'.

 LOOP AT ITAB_SCUSTOM.
   WRITE: / ITAB_SCUSTOM-ID,
            11 ITAB_SCUSTOM-NAME,
            40 ITAB_SCUSTOM-STREET,
            59 ITAB_SCUSTOM-CITY,
            75 ITAB_SCUSTOM-COUNTRY.
 ENDLOOP.

Regards

Sudheer