topic Re: Select Statement in Application Development and Automation Discussions

Select Statement

Former Member — Tue, 12 Apr 2011 09:43:04 GMT

Heloo Everybody,

I have a Z_table1 , in this table the key field is aennr,

Now I want to hit this table for all the entries in an another Z_table2, But the WHERE condition is some what as below.

AENNR+8(4) EQ i_tab2-filed.

SELECT *

FROM z_table1 INTO TABLE i_ztab

FOR ALL ENTRIES IN i_ztab2

WHERE aennr EQ i_ztab2-proj

In the above code AENNR+8(4) EQ i_tab2-filed i.e last four characters of AENNR should be equal to i_ztab2-proj .

I tried appending % to the field PROJ and tried usoing LIKE i.e

WHERE aennr LIKE i_ztab2-proj

Thanks

Suraj

Re: Select Statement

Former Member — Tue, 12 Apr 2011 10:05:50 GMT

In declaration of i_ztab1, instead of field AENNR, declare 2 fields(AENNR of length 8 and AENNR_1 of length 4), rest declaration of this is same.

Now, do the other way around. Select from Z_TABLE1 first and then Z_TABLE2 that is:

Select * from Z_table1 into i_ztab1.

and then

SELECT * FROM z_table2 INTO TABLE i_ztab2

FOR ALL ENTRIES IN i_ztab1

WHERE proj EQ i_ztab1-AENNR_1.

Defnitely it'll work.

Regards

Munish Garg

Re: Select Statement

Former Member — Tue, 12 Apr 2011 10:10:16 GMT

try appending % in both sides of PROJ and then use LIKE.

Re: Select Statement

christian_wohlfahrt — Tue, 12 Apr 2011 10:12:36 GMT

Hi Suraj,

in case you can't change the database design of your Z-tables, performance seems not to play a big role. The LIKE-syntax would have been '%nnnn" - your dynamic part is in the beginning. But this is a full table scan -> you can select all fields of table 1 yourself and check the match in your coding. That's slow, it would be better to think about a change of the Z-table design (or filling: take proj in the beginning of aennr).

Best regards, Christian

Re: Select Statement

Former Member — Tue, 12 Apr 2011 10:39:27 GMT

Please see the below given logic...this dynamic select using wild char serach is very slow in terms of performance...

It will not give correct results for the scenariors like BEIGIN WITH ,ENDS WITH ...

..
SELECT *
FROM z_table1 INTO TABLE i_ztab
FOR ALL ENTRIES IN i_ztab2
WHERE aennr like '%XXXX'.
.

Edited by: Nagarjuna Sreerambhatla on Apr 12, 2011 4:10 PM