Dynamic where clause for LOOP AT internal table

[Former Member]

Hello!

I'm fairly new to ABAP and there is a lot to learn. I'm trying to use a dynamic where-clause in an LOOP AT statement. I've found a way to do this for a SELECT dbtab statement and thought this might be the same for the LOOP AT:

loop at it_matchcluster into wa_matchcluster
  where (it_where_cluster).
  ...

the internal table

it_where_cluster

contains several lines with correct logical expressions that should be and'ed.

However this gives me an compile error.

My questions:

1. Generally why doesn't a dynamic where for the loop at statement on internal tables work the same as a dynamic where clause for a select on dbtabs? A logical expression is still a logical expression and in both cases it's evaluated at runtime not at compile time.

2. How would one implement a dynamic where clause for loop at itab?

Thanks,

Reinhard

[Former Member]

Hello!

I'm fairly new to ABAP and there is a lot to learn. I'm trying to use a dynamic where-clause in an LOOP AT statement. I've found a way to do this for a SELECT dbtab statement and thought this might be the same for the LOOP AT:

loop at it_matchcluster into wa_matchcluster
  where (it_where_cluster).
  ...

the internal table

it_where_cluster

contains several lines with correct logical expressions that should be and'ed.

However this gives me an compile error.

My questions:

1. Generally why doesn't a dynamic where for the loop at statement on internal tables work the same as a dynamic where clause for a select on dbtabs? A logical expression is still a logical expression and in both cases it's evaluated at runtime not at compile time.

2. How would one implement a dynamic where clause for loop at itab?

Thanks,

Reinhard

[Former Member]

Why would you assume that the WHERE condition works the same way for SQL and LOOP AT....

As you said you are new to ABAP, so you cannot be making these assumptions.

[Former Member]

That's the logic of how formal languages work

Can you explain why it doesn't work the same?

[Former Member]

The simple answer is because it doesn't. It has not been designed that way.

You cannot compare ABAP to other languages that may be able to handle it.

[Former Member]

I know that ABAP is different, powerful in some means but also dirty...

btw. the documentation for SELECT states:

WHERE <log_exp>

and the documentation for LOOP AT also states:

WHERE <log_exp>

Though the definition of <log_exp> is different in both cases. A bad way to formally define a programming language in my opinion (And every programming language is a formal language)

Well, only a remark off-topic. It doesn't solve my problem.

But how can it be solved then? Worked around? Done right?

[Former Member]

> A bad way to formally define a programming language in my opinion  (And every programming language is a formal language)

Have a go at creating your own then. Call it ZABAP or something.

I have been an ABAP programmer for 10 years and never need to use a dynamic where condition for a Loop. Maybe you need to look at changing how the Internal table is populated in the first place.

[christine_evans]

> I know that ABAP is different, powerful in some means but also dirty...
> 
> btw. the documentation for SELECT states:
> WHERE <log_exp>
> 
> and the documentation for LOOP AT also states:
> WHERE <log_exp> 
> 
> Though the definition of <log_exp> is different in both cases. A bad way to formally define a programming language in my opinion  (And every programming language is a formal language)
> 
> Well, only a remark off-topic. It doesn't solve my problem.
> 
> But how can it be solved then? Worked around? Done right?

Why do I get the impression that you have not been impressed by your experience of ABAP so far? Anyway, I've never tried creating a dynamic where clause for a loop at an itab and don't think I've ever found a need to do so. If you are a newcomer to ABAP, I'd try keeping it simple for the time being - in fact, keeping code as simple and as easily understood as possible and not doing unnecessarily complicated things just for the sake of it is good practice in any programming language (in my opinion).

I guess you would need to use an IF to generate your dynamic where clause. Instead, try running a different LOOP command for each of your IF conditions.

> Though the definition of <log_exp> is different in both cases. A bad way to formally define a programming language in my opinion  (And every programming language is a formal language)
> 

Why should WHERE have to mean exactly the same thing for the two different examples given? It just means that here you should insert a logical expression. It doesn't mean that you can use the same logical expression for two different programming constructs

[Former Member]

hello ,

Check if this works

LOOP AT t_matchcluster ASSIGNING <l_line>.

ASSIGN COMPONENT <dynamic_field> OF STRUCTURE <l_line> TO <l_field>.

IF <l_field> EQ (your condition)

[ThomasZloch]

> 1. Generally why doesn't a dynamic where for the loop at statement on internal tables work the same as a dynamic where clause for a select on dbtabs? A logical expression is still a logical expression and in both cases it's evaluated at runtime not at compile time.

Guessing here: an open SQL statement must anyway be translated into a native SQL statement for the respective DBMS, so resolving a dynamic WHERE-condition along the way is probably not so much additional effort. As opposed to a LOOP AT statement, which is interpreted and executed on the application server. I assume this could be introduced theoretically, but ->

> 2. How would one implement a dynamic where clause for loop at itab?

Like Christine and Martin I never missed this possibility in my practical work, so I wonder what your motivation is other than pure principle. Do you have an actual specification requiring dynamic WHERE conditions?

The ultimate weapon is dynamic program generation, but this is surely not for new ABAPers and is also condidered unnecessarily complex for most purposes. It's a potential security hazard as well that auditors love to complain about.

Thomas

[Former Member]

The motivation for an dynamic where-clause is a quite complex search for duplicates in debitors or kreditors using different criterias and arbitrary combinations of these criterias (e.g. find all debitors that match in name AND tax number). This algorithm builds up two internal tables, one containing every debitor and the match-groups (a group is a set of debitors that match a specific criteria), the second table is a table containing all so called match-clusters (a match cluster is defined as a group of at least two identical match groups with at least two members).

Obviously if you want arbitrary combinations of criterias (user defined) you need to dynamically select from those two internal tables if you don't want to do a calculation of those everytime you change the criteria-combinations.

Nevermind, I found a solution to the problem which isn't as elegant as if ABAP supported dynamic where clauses in the LOOP AT statement. This solution uses two more nested loops for the criteria table which isn't that big performance issue as long as there aren't too many criterias (which isn't the case for debitor matches).

Thanks,

Reinhard

[ThomasZloch]

Interesting task, glad you found a workaround. Make sure you use internal tables defined as SORTED and use the defined key fields in the WHERE-conditions of the loops.

If you have done that already, you're not a "new ABAPer", at least not the usual one

Thomas

[Former Member]

As there will be lots of reads from those tables by the matching algorithms once they've been built up, I decided to make those tables hashed ones. This should give an slight speedup if playing around with odd combinations of match criterias.

Thanks,

Reinhard

[Former Member]

Hi,

Kindly go through this link :

http://www.susanto.id.au/papers/DynOpenSQL.asp#_Toc23566383

Hope it helps

Regards

Mansi

[OttoGold]

Hi,

this is an old thread but I found it when working on the same problem.

Ok, so we don`t have dynamic WHERE clause for LOOP statement.

I am not sure how exactly the guest, who asked the question, solved his problem, but I wanted to share my solution and ask if you, dear gurus and "gurettes" think it is effective or not.

Ok, so problem is: looking for duplicates, in my case it is looking for more and more specific subsets of the input set.

What I am doing:

1) understand which attrbutes have the best selectivity, so I remove as many records every round as possible

2) have two internal tables of type "RANGE" for attributes I test

3) every round I remove as many records as possible and for those which are not removed I put their name into the select option

in next round i use my conditions which are not dynamic and add IN statement for the RANGE(S)

side-note) the numbers drop down very quickly so the performance is not bad at all

as a part of the solution one can write the LOOP AT again, this time with xxx IN LT_RNG_2 and NOT IN LT_RNG2

to learn which records were removed in this round.

I was researching how could I solve my problem in the most elegant way and didn`t find such way.

I am a) missing something very important despite the years of experience or b) there is no such way.

Have a nice day,

Otto

[Former Member]

In each round are you just LOOPing, or are you going to the database as well?

Rob

[OttoGold]

Before I start the LOOP I beg the database for the first data set.

Then, with every LOOP, I reduce the records I really test (explicitely). Every line I touch I use for building the range that I use in the LOOP through "xx IN lt_rng". Basically I use range to test less and less records with every round and LOOP at the same data all the time, because the key of the table consists of multiple fields I work with (copying would be very ineffective and working with one column only is not possible because of the nature of the data).

Not sure how clear this is, but I think it is elegant and seems to be effective.

It is like version 22 of the report, so I hope it has some depth. On the ohter hand I have no idea how to measure if it really IS effective or if some other way would go better. I heard that sub-SELECTS can be slower than operations on the internal tables, I also heard that the effectivity of every statement depends on the way it is implemented in background and of course of attributes of the statements which one can only difficultly finetune without knowing the implementation.

Howgh. Not sure if this helps anybody, but I hope so and with this hope I write these lines.

Have a nice day everybody,

Otto

[ThomasZloch]

Hi Otto,

dynamic WHERE-clauses for LOOPs have been introduced with Release 7.02 (EhP2), if you are lucky enough to work with this version already (I'm not).

Thomas

[Former Member]

Hi Thomas,

I have an ALV display with dynamic structure.

the user filters the list after the display.

Now in my program I wish to filter the internal table based on the filters placed by the user at run time on the output list.

This obiviously will require dynamic filtering from the internal table as it depends on the filters place on the output list.

i have some how implemented this.

the problem is based on the no. of filters placed, I am looping the internal table that many times to delete the irrevelent entries. This is degrading the performance.

do you have any suggestions?

I tried consulting some of my senior colleagues, but they said it is very specific case.

Unfortunately I am still working on 6.0.

[Former Member]

Hi,

May be just put the deletion indicator 'ON' for such records in the internal table.

Once you are through with your 'Loop' delete all records where deletion indicator is 'ON'.

For updating the indicator use Field-Symbols, this will make it still better.

I hope this helps,

Regards

Raju chitale

[former_member209920]

Hi Former Member,

Use IT_FILTER parameter of method SET_TABLE_FOR_FIRST_DISPLAY.

You do not need to delete internal table.

Regards,

[Former Member]

Hello,

Yes, you can use a dynamic where clause with a loop.  Here is a very, very basic example.\

DATA: lt_spfli TYPE STANDARD TABLE OF spfli,

       lv_where TYPE string,

       lv_fld1  TYPE string value 'CARRID',

       lv_fld2  TYPE string VALUE 'AIRPTO',

       lv_carrid TYPE s_carr_id VALUE 'AA',

       lv_airpt  TYPE s_toairp VALUE 'JFK',

       lv_sing_quote TYPE string VALUE '''',

       lv_stub   TYPE string,

       lv_stub2   TYPE string.

FIELD-SYMBOLS: <fs_spfli> TYPE spfli.

SELECT * INTO CORRESPONDING FIELDS OF TABLE lt_spfli FROM spfli.

"To add the single quote around value. E.g.  AA => 'AA', JFK => 'JFK'

CONCATENATE lv_sing_quote lv_carrid lv_sing_quote INTO lv_stub.

CONCATENATE lv_sing_quote lv_airpt lv_sing_quote INTO lv_stub2.

"Where clause without AND

CONCATENATE lv_fld1 '=' lv_stub INTO lv_where SEPARATED BY space.

ULINE.

LOOP AT lt_spfli ASSIGNING <fs_spfli> WHERE (lv_where).

   WRITE: / <fs_spfli>-carrid, <fs_spfli>-airpto.

ENDLOOP.

ULINE.

NEW-LINE.

NEW-LINE.

CLEAR lv_where.

"Where clause with AND

CONCATENATE lv_fld1 '=' lv_stub 'AND' lv_fld2 '=' lv_stub2 INTO lv_where SEPARATED BY SPACE.

ULINE.

LOOP AT lt_spfli ASSIGNING <fs_spfli> WHERE (lv_where).

   WRITE: / <fs_spfli>-carrid, <fs_spfli>-airpto.

ENDLOOP.

ULINE.

Someone asked why you would want to do this?

If you have a shared memory object for which you want to allow consumers to search, it is easier to build the dynamic where clause based upon search criteria.  The abap class  cl_lib_seltab makes converting selection criteria to dynamic where clauses much easier.

Regards,

Larry