topic Dynamic where clause with loop statement in Application Development and Automation Discussions

Dynamic where clause with loop statement

Former Member — Tue, 06 Feb 2007 13:54:39 GMT

Hi all,

is it possible to use a dynamic where clause with a loop statement?

Can you please advise me, how the syntax needs to be?

Thanks for your suggestions,

kind regards, Kathrin!

Re: Dynamic where clause with loop statement

Shivaji16 — Wed, 07 Feb 2007 04:32:41 GMT

Hi Kathrin,

If u are in ECC 6.0, please go through the code...

REPORT zdynamic_select.

TYPES:

BEGIN OF ty_sales,

vbeln TYPE vbak-vbeln, " Sales document

posnr TYPE vbap-posnr, " Sales document item

matnr TYPE vbap-matnr, " Material number

arktx TYPE vbap-arktx, " Short text for sales order item

kwmeng TYPE vbap-kwmeng, " Order quantity

vkorg TYPE vbak-vkorg, " Sales organization

kunnr TYPE vbak-kunnr, " Sold-to party

netwr TYPE vbak-netwr, " Net Value of the Sales Order

END OF ty_sales.

DATA :

gt_sales TYPE STANDARD TABLE OF ty_sales,

wa_sales TYPE ty_sales.

DATA: ob_select TYPE REF TO cl_rs_where.

DATA: ob_from TYPE REF TO cl_rs_where.

DATA: ob_where TYPE REF TO cl_rs_where,

gv_source TYPE abapsource.

START-OF-SELECTION.

*Step 1 : Prepare the select fields.

PERFORM zf_build_select.

*Step 2 : Build the from clause for the select

PERFORM zf_build_from.

*Step 3 : Build the where clause for the select

PERFORM zf_build_where.

*Step 4 : Execute the dynamic select

SELECT (ob_select->n_t_where)

FROM (ob_from->n_t_where)

INTO CORRESPONDING FIELDS OF TABLE gt_sales

WHERE (ob_where->n_t_where).

LOOP AT gt_sales INTO wa_sales.

WRITE : /5 wa_sales-vbeln,

15 wa_sales-vkorg,

20 wa_sales-kunnr,

40 wa_sales-netwr,

50 wa_sales-posnr,

60 wa_sales-matnr,

70 wa_sales-arktx,

90 wa_sales-kwmeng.

ENDLOOP.

&----

*& Form zf_build_select

&----

FORM zf_build_select .

CREATE OBJECT ob_select.

*********************************************************

*Build the table name/field name combination

*Add Sales order header fields

CLEAR gv_source.

CALL METHOD cl_rs_where=>build_tabname_fieldname

EXPORTING

i_tabname = 'VBAK'

i_fieldname = 'VBELN'

i_sign = '~'

IMPORTING

e_combined = gv_source.

*Add the where line

CALL METHOD ob_select->add_line

EXPORTING

i_line = gv_source.

CLEAR gv_source.

************************************************************

CALL METHOD cl_rs_where=>build_tabname_fieldname

EXPORTING

i_tabname = 'VBAK'

i_fieldname = 'VKORG'

i_sign = '~'

IMPORTING

e_combined = gv_source.

*Add the where line

CALL METHOD ob_select->add_line

EXPORTING

i_line = gv_source.

CLEAR gv_source.

************************************************************

CALL METHOD cl_rs_where=>build_tabname_fieldname

EXPORTING

i_tabname = 'VBAK'

i_fieldname = 'KUNNR'

i_sign = '~'

IMPORTING

e_combined = gv_source.

*Add the where line

CALL METHOD ob_select->add_line

EXPORTING

i_line = gv_source.

CLEAR gv_source.

************************************************************

CALL METHOD cl_rs_where=>build_tabname_fieldname

EXPORTING

i_tabname = 'VBAK'

i_fieldname = 'NETWR'

i_sign = '~'

IMPORTING

e_combined = gv_source.

*Add the where line

CALL METHOD ob_select->add_line

EXPORTING

i_line = gv_source.

************************************************************

*Add Sales order item fields

CALL METHOD cl_rs_where=>build_tabname_fieldname

EXPORTING

i_tabname = 'VBAP'

i_fieldname = 'POSNR'

i_sign = '~'

IMPORTING

e_combined = gv_source.

*Add the where line

CALL METHOD ob_select->add_line

EXPORTING

i_line = gv_source.

CLEAR gv_source.

CALL METHOD cl_rs_where=>build_tabname_fieldname

EXPORTING

i_tabname = 'VBAP'

i_fieldname = 'MATNR'

i_sign = '~'

IMPORTING

e_combined = gv_source.

*Add the where line

CALL METHOD ob_select->add_line

EXPORTING

i_line = gv_source.

CLEAR gv_source.

CALL METHOD cl_rs_where=>build_tabname_fieldname

EXPORTING

i_tabname = 'VBAP'

i_fieldname = 'ARKTX'

i_sign = '~'

IMPORTING

e_combined = gv_source.

*Add the where line

CALL METHOD ob_select->add_line

EXPORTING

i_line = gv_source.

CLEAR gv_source.

CALL METHOD cl_rs_where=>build_tabname_fieldname

EXPORTING

i_tabname = 'VBAP'

i_fieldname = 'KWMENG'

i_sign = '~'

IMPORTING

e_combined = gv_source.

*Add the where line

CALL METHOD ob_select->add_line

EXPORTING

i_line = gv_source.

ENDFORM. " zf_build_select

&----

*& Form zf_build_from

&----

FORM zf_build_from .

CREATE OBJECT ob_from.

*Add opening bracket

CALL METHOD ob_from->add_opening_bracket

CLEAR gv_source.

*Add the join condition.This can be made

*fully dynamic as per your requirement

gv_source = 'VBAK AS VBAK INNER JOIN VBAP AS VBAP'.

*Add the where line

CALL METHOD ob_from->add_line

EXPORTING

i_line = gv_source.

CLEAR gv_source.

*Add the join condition.This can be made

*fully dynamic as per your requirement

gv_source = 'ON VBAK_{VBELN = VBAP}VBELN'.

*Add the where line

CALL METHOD ob_from->add_line

EXPORTING

i_line = gv_source.

*Add the closing bracket

CALL METHOD ob_from->add_closing_bracket

ENDFORM. " zf_build_from

&----

*& Form zf_build_where

&----

FORM zf_build_where .

DATA :

lv_field TYPE REF TO data,

lv_field_low TYPE REF TO data,

lv_field_high TYPE REF TO data.

CREATE OBJECT ob_where.

*Add the field VBELN : Sales Document

*Use this method if you want to assign a single value to a field

*Set the value for VBELN : Sales Document Number

******************************************************************

CALL METHOD ob_where->add_field
EXPORTING
i_fieldnm = 'VBAK~VBELN'
i_operator = '='
i_intlen = 10
i_datatp = 'CHAR'
IMPORTING
e_r_field = lv_field.

CALL METHOD ob_where->set_value_for_field
EXPORTING
i_fieldnm = 'VBAK~VBELN'
i_value = '0000120020'.

********************************************************************

*Use this method if you want to assign a range of values

*Set a range for the Sales Document number

********************************************************************

CALL METHOD ob_where->add_field_between_2values

EXPORTING

i_fieldnm = 'VBAK~VBELN'

i_intlen = 10

i_datatp = 'CHAR'

IMPORTING

e_r_field_low = lv_field_low

e_r_field_high = lv_field_high.

CALL METHOD ob_where->set_2values_for_field

EXPORTING

i_fieldnm = 'VBAK~VBELN'

i_value_low = '0000120020'

i_value_high = '0000120067'.

**********************************************************************

*Set the 'AND' Clause

CALL METHOD ob_where->add_and.

*Add the field MATNR : Material

CALL METHOD ob_where->add_field

EXPORTING

i_fieldnm = 'MATNR'

i_operator = '='

i_intlen = 18

i_datatp = 'CHAR'

IMPORTING

e_r_field = lv_field.

*Set the value for the Material field

CALL METHOD ob_where->set_value_for_field

EXPORTING

i_fieldnm = 'MATNR'

i_value = '000000000050111000'.

*Set the 'AND' Clause

CALL METHOD ob_where->add_and

*Add the field VKORG

CALL METHOD ob_where->add_field

EXPORTING

i_fieldnm = 'VKORG'

i_operator = '='

i_intlen = 4

i_datatp = 'CHAR'

IMPORTING

e_r_field = lv_field.

*Set the value for VKORG : Sales Organization

CALL METHOD ob_where->set_value_for_field

EXPORTING

i_fieldnm = 'VKORG'

i_value = 'GMUS'.

ENDFORM. " zf_build_where

Re: Dynamic where clause with loop statement

anversha_s — Wed, 07 Feb 2007 04:39:25 GMT

Hello

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):

RH_DYNAMIC_WHERE_BUILD

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

Anver

Re: Dynamic where clause with loop statement

uwe_schieferstein — Wed, 07 Feb 2007 04:44:24 GMT

Hello Kathrin

I assume that both answers thus far have not yet met your point. You are looking for a piece of code like this:

 LOOP AT itab INTO ls_record
                      WHERE ( <dynamic clause> ).
...
 ENDLOOP.

The short answer is: this is <b>not possible</b> since the LOOP statement wants to know the static type of the fields used in the WHERE clause. You can also search the forum (<i>dynamic AND where AND clause</i>) where you will find many threads confirming this.

Regards

Uwe

Re: Dynamic where clause with loop statement

Former Member — Wed, 07 Feb 2007 04:46:07 GMT

hi kathrin ,

As per ur posting , it is possible to have dynamic where clause in the loop statement. Following is the snippet .

tables: kna1.

data: begin of itab occurs 0,

nam like kna1-name1,

snam like kna1-name2,

kunnr like kna1-kunnr,

end of itab.

Here at first we have to make an entry mannualyinto itab

nam = 'kath'.

snam='Guth'.

kunnr='4384'.

loop at itab

select name1 name2 kunnr from kna1 into itab where <your condition on kna1>

endloop.

But , onething i wud like to add here, it ios not agood approach to use selec t statement in Looop because i thits the databse evrytime and reduces the performance of the system.

i hope this solves the problem

Regards.

Note: Reward if useful

Re: Dynamic where clause with loop statement

Former Member — Wed, 07 Feb 2007 04:53:04 GMT

Hi,

you can declare an internal table like :

data: begin of i_flds occurs 0,

str(132),

end of i_flds.

Append the where clause into this internal table, for eg:

concatenate 'matnr = ' p_matnr into i_flds-str.

"assuming p_matnr is a parameter value

append i_flds.

clear i_flds.

concatenate ' and werks = ' p_werks into i_flds-str.

append i_flds.

clear i_flds.

In the select query's Where class write:

SELECT ........

where (i_flds).

Regards

Subramanian

Re: Dynamic where clause with loop statement

Former Member — Thu, 08 Feb 2007 08:51:52 GMT

Hello all,

thank you very much for all your help, I appreciate the many helpful answers. I was roughly familiar with the dynamic where clause for select statements, but was looking for a similiar solution for a loop statement (to avoid select-loops and performance problems that go along with it...). Anyways, I learned a lot about more elegant ways to build dynamic where clauses, thanks.

cheers, Kathrin!