Logic is needed.

[Former Member]

This is the performance issue.

Can any one change the above logic?

I don’t want the select inside the loop.

Logic is needed.

Can any one change the above logic? (mentioned in red color)

I don’t want the select inside the loop.

loop at it_vttk.

clear v_tknum.

concatenate it_vttk-tknum '%' into v_tknum.

select fknum

fkpos

exti1

netwr

from vfkp

in to table t_vfkp

where exti1 like v_tknum.

endloop.

[Former Member]

Hi,

loop at it_vttk.

clear v_tknum.

concatenate it_vttk-tknum '%' into v_tknum.

select fknum

fkpos

exti1

netwr

from vfkp

in to table t_vfkp

where exti1 like v_tknum.

endloop.

Create varible with RANGES <SELTAB> FOR VFKP-EXTI1. This will create internal table with structure as selection table.

loop t_vfkp.

clear SELTAB.

SELTAB-SIGN = 'I'.

SELTAB-OPTION = 'CP'

concatenate it_vttk-tknum '*' into SELTAB-LOW.

append SELTAB.

endloop.

Then use SELECT statement

select fknum

fkpos

exti1

netwr

from vfkp

in to table t_vfkp

where exti1 in SELTAB.

Hope this will help you to avoid select into loop.. enloop.

Please reward helpful replies.

Regards

Sunil

Message was edited by: Sunil Sawaiker

[former_member186741]

the obvious answer is to use 'for all entries' but I don't hink that will help you..... these tables are not linked very well so the select will be inefficient unless you create an index on table VFKP on exti1.

Do you really need the 'LIKE' clause? Surely the VTTk has the complete value.

If I'm right then you could do

select fknum

fkpos

exti1

netwr

from vfkp

into corresponding fields of table t_vfkp

for all entries in t_vttk

where exti1 = t_vttk-exti1.

But, as I said before this could still be very slow unless an index is created on exti1.

[Former Member]

HI,

Consider the belowsaid option and reward if helpful.

<b>data : begin of str_tknum,
       tknum(10),
       end of str_tknum.

data : it_tknum like table of str_tknum with header line.

loop at it_vttk.
concatenate it_vttk-tknum '%' into it_tknum-tknum.
append it_tknum.
clear it_tknum.
endloop.

select fknum fkpos exti1 netwr from vfkp into table t_vfkp
for all entries in it_tknum where exti1 like it_tknum-tknum.</b>

Regards

[Former Member]

I am unable to use the LIKE In for all entries.

[kanthimathikris]

Can you try this logic,

v_tknum = 'tknum%'.

select fknum

fkpos

exti1

netwr

from vfkp

into table t_vfkp

for all entries in it_vttk

where exti1 like (v_tknum).

[Former Member]

I am not able to use the LIKE Statement in the for all entries.

[Former Member]

have u tried my code??

[Former Member]

Naren/chandra/

s still it goes to shot dumb

Error in the module RSQL accessing the database interface.

help me

[Former Member]

i had changed my code at line , have u observed that?

first i had written BT , then changed to CP

it_range-option = <b>'CP'</b>.

Message was edited by: Chandrasekhar Jagarlamudi

[Former Member]

Chandra,

it_range-option = 'CP'.

i tried the above option also.

REPORT YCHATEST.

*data: V_TKNUM like standard table of

data: V_TKNUM(30).

data: it_vttk like standard table of vttk with header line.

data: it_vfkp like standard table of vfkp with header line.

DATA : BEGIN OF IT_RANGE OCCURS 0,

SIGN(1),

OPTION(2),

LOW LIKE V_TKNUM,

HIGH LIKE V_TKNUM,

END OF IT_RANGE.

CLEAR IT_RANGE.

REFRESH IT_RANGE.

select * from vttk into table it_vttk.

LOOP AT IT_VTTK.

CLEAR V_TKNUM.

CONCATENATE IT_VTTK-TKNUM '%' INTO V_TKNUM.

IT_RANGE-SIGN = 'I'.

IT_RANGE-OPTION = 'CP'.

IT_RANGE-LOW = V_TKNUM.

IT_RANGE-HIGH = ''.

APPEND IT_RANGE.

CLEAR IT_RANGE.

ENDLOOP.

SELECT FKNUM

FKPOS

EXTI1

NETWR

FROM VFKP

INTO TABLE IT_VFKP

WHERE EXTI1 IN IT_RANGE .

write: 's'.

[Former Member]

hi ,

got it,

in the 2nd select use INTO CORRESPONDING FIELDS OF

SELECT FKNUM
FKPOS
EXTI1
NETWR
FROM VFKP
INTO CORRESPONDING FIELDS OF TABLE IT_VFKP
WHERE EXTI1 IN IT_RANGE .

[Former Member]

Few suggestions marked with ==> in the below code.

REPORT YCHATEST.

*data: V_TKNUM like standard table of

data: V_TKNUM(30).

data: it_vttk like standard table of vttk with header line.

data: it_vfkp like standard table of vfkp with header line.

DATA : BEGIN OF IT_RANGE OCCURS 0,

SIGN(1),

OPTION(2),

LOW LIKE V_TKNUM,

HIGH LIKE V_TKNUM,

END OF IT_RANGE.

CLEAR IT_RANGE.

REFRESH IT_RANGE.

select * from vttk into table it_vttk.

LOOP AT IT_VTTK.

CLEAR V_TKNUM.

CONCATENATE IT_VTTK-TKNUM '%' INTO V_TKNUM.

IT_RANGE-SIGN = 'I'.

IT_RANGE-OPTION = 'CP'.

IT_RANGE-LOW = V_TKNUM.

IT_RANGE-HIGH = ''.

APPEND IT_RANGE.

CLEAR IT_RANGE.

ENDLOOP.

==>since you are populating each an every entry into the range for CP it would surely be slow ...recommend to derive a logic of using between or something similar but it depends on your final requirement.

SELECT FKNUM

FKPOS

EXTI1

NETWR

FROM VFKP

INTO TABLE IT_VFKP

==> you need to use CORRESPONDING FIELDS variation.

WHERE EXTI1 IN IT_RANGE .

write: 's'.

[Former Member]

You can try this.

You can add another field to your it_vttk. ex. tknumtxt(20).

loop at it_vttk.

concatenate it_vttk-tknum '%' into it_vttk-tknumtxt.

modify it_vttk.

endloop.

select fknum fkpos exti1 netwr

from vfkp

FOR ALL ENTRIES IN IT_VTTK

into table t_vfkp

where exti1 like IT_VTTK-TknumTXT.

Hope this helps.

[Former Member]

Hi,

ranges: r_tknum for vttk-tknum.

loop at it_vttk.

concatenate it_vttk-tknum '*' into r_tknum-low.

r_tknum-sign = 'I'.

r_tknum-option = 'CP'.

append r_tknum.

endloop.

select fknum

fkpos

exti1

netwr

from vfkp

into table t_vfkp

where exti1 in r_tknum.

Please award if it helps...

Thanks,

Naren

[Former Member]

It goes to shortdumb

[Former Member]

try this...

REPORT YCHATEST.

DATA : BEGIN OF IT_RANGE OCCURS 0,
        SIGN(1),
        OPTION(2),
        LOW LIKE V_TKNUM,
        HIGH LIKE V_TKNUM,
       END OF IT_RANGE.

CLEAR IT_RANGE.
REFRESH IT_RANGE.

LOOP AT IT_VTTK.
  CLEAR V_TKNUM.
  CONCATENATE IT_VTTK-TKNUM '%' INTO V_TKNUM.
  IT_RANGE-SIGN = 'I'.
  IT_RANGE-OPTION = <b>'CP'</b>.
  IT_RANGE-LOW = V_TKNUM.
  IT_RANGE-HIGH = ''.
  APPEND IT_RANGE.
  CLEAR IT_RANGE.
ENDLOOP.

SELECT FKNUM
FKPOS
EXTI1
NETWR
FROM VFKP
INTO TABLE T_VFKP.
WHERE EXTI1 IN IT_RANGE..

Message was edited by: Chandrasekhar Jagarlamudi

[Former Member]

i want to clarify a small doubt from

i know that ranges similar like select options...

so when we have to use ranges..

explanation needed

can u give some situations we can use.

waiting for ur reply

[Former Member]

Hi,

Please check my earlier reply..

Thanks,

naren

[Former Member]

Hi,

You are not using like instead you will be using the ranges..

ranges: r_tknum for vttk-tknum.

loop at it_vttk.

concatenate it_vttk-tknum '*' into r_tknum-low.

r_tknum-sign = 'I'.

r_tknum-option = 'CP'.

append r_tknum.

endloop.

select fknum

fkpos

exti1

netwr

from vfkp

into table t_vfkp

where exti1 in r_tknum.

Please award if it helps...

Thanks,

Naren

Message was edited by: Narendran Muthukumaran

[Former Member]

Priya,

Consider the following code, reward if helpful.

DATA: tknum(18) TYPE C,

ITAB LIKE TABLE OF tknum.

PARAMETERS: p_tknum(18) TYPE C.

CONCATENATE p_tknum '%' INTO tknum.

APPEND tknum TO ITAB.

SELECT fknum fkpos exti1 netwr INTO WA FROM vfkp

WHERE (ITAB).

WRITE / WA.

ENDSELECT.

Regards

[Former Member]

Zapper,

i tried ur optios also.

again it goes for dumb.

[Former Member]

hi priya,

pls reward and close thread if solved

[Former Member]

the data is not retrieved in the table 2 internal table it_vfkp

still i am facing the same problem.

SELECT FKNUM

FKPOS

EXTI1

NETWR

FROM VFKP

INTO CORRESPONDING FIELDS OF TABLE IT_VFKP

WHERE EXTI1 IN IT_RANGE .

[Former Member]

Sorry Priya..but the problem now is not able to retrive data or its performance ?

<b>If it is data can you cut-paste couple of sample data from VTTK and VFKP.</b>

LOOP AT IT_VTTK.

CLEAR V_TKNUM.

<b>CONCATENATE '' IT_VTTK-TKNUM '' INTO V_TKNUM.</b>

IT_RANGE-SIGN = 'I'.

IT_RANGE-OPTION = 'CP'.

IT_RANGE-LOW = V_TKNUM.

IT_RANGE-HIGH = ''.

APPEND IT_RANGE.

CLEAR IT_RANGE.

ENDLOOP.

Regards

Anurag

Message was edited by: Anurag Bankley

Message was edited by: Anurag Bankley

[Former Member]

i am testing into Developement server. the data is very low.

but i checked 2 tables they are nearly morethan 20 records are mamking.

as per above coding the data is not populated inti the internal table it_vfkp.

in the runtime also i have changed the records also but it not able to populate.

thanks,

[Former Member]

Hi Priya

  Just want to add few comments. The Logic provided 
earlier to you by our friends, with defining ranges and 
option 'CP' will work provided there are no leading 
<b>ZEROES</b> in the storage of table VFKP. Please check 
in the table if itz stored with <b>ZEROES PREFIXED</b>.
In simple can you check if the formatting of both values in both tables are in the same way.

REPORT YCHATEST.

*data: V_TKNUM like standard table of
data: V_TKNUM(30).
data: it_vttk like standard table of vttk with header line.
data: it_vfkp like standard table of vfkp with header line.

*DATA : BEGIN OF IT_RANGE OCCURS 0,
*SIGN(1),
*OPTION(2),
*LOW LIKE V_TKNUM,
*HIGH LIKE V_TKNUM,
*END OF IT_RANGE.

RANGES: R_TKNUM FOR VFKP-EXTI1.

*CLEAR IT_RANGE.
*REFRESH IT_RANGE.
*

select * from vttk into table it_vttk.

LOOP AT IT_VTTK.
     CLEAR V_TKNUM.
     CONCATENATE IT_VTTK-TKNUM '*' INTO V_TKNUM.
**** If you have leading ZEROES stored in the value here
***     add respectively. Suppose 4 zeroes you have to 
****    do it like CONCATENATE '0000' IT_VTTK-TKNUM '*' 
****    INTO V_TKNUM.
     R_TKNUM-SGIN = 'I'.
     R_TKNUM-OPTION = 'EQ'.
     R_TKNUM-LOW = V_TKNUM.
     APPEND R_TKNUM.
*IT_RANGE-SIGN = 'I'.
*IT_RANGE-OPTION = 'CP'.
*IT_RANGE-LOW = V_TKNUM.
*IT_RANGE-HIGH = ''.
*APPEND IT_RANGE.
*CLEAR IT_RANGE.
ENDLOOP.

SELECT FKNUM
       FKPOS
       EXTI1
       NETWR
       FROM VFKP
       INTO TABLE IT_VFKP
*WHERE EXTI1 IN IT_RANGE .
       WHERE EXTI1 IN R_TKNUM .


Hope this helps you.

Kind Regards
Eswar

[Former Member]

Hi,

Why would u want to concatenate '%' into the variable?

Rgds

[Former Member]

For example in the first internal table

there are 10 records.

Eg: 123

456

425

.

.

.

542

Here I want to match the first value (123) of the second internal table.

It must retrieve the only below records,

eg:

123456456656

12334534

42554564565

The above records are correct results.

In don’t want to populate the below records,

00125454

6582222

That’s y I am using the ‘%’ .

[Former Member]

Hi Priya

In that case, use I for SIGN, CP for Option and add *

to the values on the right hand side in low. It should

work. Also check the same in debugging.

Kind Regards

Eswar

[Former Member]

Can u send the code

[Former Member]

Priya,

- Sorry ignore the below since the EXTI1 field has variable length it wont work---

U can try the below option

ltknum(10) type c.

select * from vttk into table it_vttk.

LOOP AT IT_VTTK.

CLEAR V_TKNUM.

IT_RANGE-SIGN = 'I'.

IT_RANGE-OPTION = 'BT'.

CONCATENATE IT_VTTK-TKNUM '0000000000' INTO ltknum.

IT_RANGE-LOW = lTKNUM.

CONCATENATE IT_VTTK-TKNUM '9999999999' INTO ltknum.

IT_RANGE-HIGH = ltknum.

APPEND IT_RANGE.

CLEAR IT_RANGE.

ENDLOOP.

SELECT FKNUM

FKPOS

EXTI1

NETWR

FROM VFKP

INTO TABLE IT_VFKP

WHERE EXTI1 IN IT_RANGE .

--Basically the variables ltknum would have the data as 1230000000 and 1239999999. So if you VFKP of 10 chars is using the complete 10 chars it would get you the correct data...in case you are using only say 8 digits of VFKP-EXTI1 field than you can use only 8 as the length.

Message was edited by: Anurag Bankley

Message was edited by: Anurag Bankley

[Former Member]

I gave the sample data.

the first internal table of may be different .

it not start with 0000000 or 9999999

the field contains 10 char it should match the first 10 char of the second internal table.

eg:

table1 table2 result

133 01254554 -

002 15451214 -

989 98900021 98900021

[Former Member]

Priya

Have you tried it using I CP 123* as the data in your range ?

Regards

Anurag

[Former Member]

i gave the example that's it.

that's not the correct sln

[Former Member]

Hi,

The issue is not with the solution..

Try the solution with less number of records for EXTI1.

THe short dump occurs since there were lot of records in the range and sap will convert the open SQL to Native SQL and he was not accomodate all the values..

I tried this and it was working fine for me..

I will check if there is another solution for that..

Thanks,

Naren

[Former Member]

Hi,

Check this..This would avoid the short dump and the number of hits will be less..

ranges: r_tknum for vttk-tknum.

ranges: r_tknum_temp for vttk-tknum.

data: v_start_index type int4,

v_end_index type int4.

loop at it_vttk.

concatenate it_vttk-tknum '*' into r_tknum-low.

r_tknum-sign = 'I'.

r_tknum-option = 'CP'.

append r_tknum.

endloop.

v_start_index = 1.

v_end_index = 500.

D0.

REFRESH : r_tknum_temp.

APPEND LINES of r_tknum from

v_start_index to v_end_index

to r_tknum_temp.

if r_tknum_temp[] is initial.

exit.

endif.

select fknum

fkpos

exti1

netwr

from vfkp

appending table t_vfkp

where exti1 in r_tknum_temp.

v_start_index = v_start_index + 500.

v_end_index = v_end_index + 500.

ENDDO.

• If it still short dumps reduce the number from 500 to 400..

THanks,

Naren

[Former Member]

Naren,

i don't want the select statement inside the loop.

**DATA : BEGIN OF IT_RANGE OCCURS 0,

• SIGN(1),

• OPTION(2),

• LOW LIKE V_TKNUM,

• HIGH LIKE V_TKNUM,

• END OF IT_RANGE.

**

**CLEAR IT_RANGE.

**REFRESH IT_RANGE.

**

• select * from vttk into table it_vttk.

**

**LOOP AT IT_VTTK.

• CLEAR V_TKNUM.

• CONCATENATE IT_VTTK-TKNUM '%' INTO V_TKNUM.

• IT_RANGE-SIGN = 'I'.

• IT_RANGE-OPTION = 'BT'.

• IT_RANGE-LOW = V_TKNUM.

• IT_RANGE-HIGH = ''.

**

• APPEND IT_RANGE.

• CLEAR IT_RANGE.

**

**

**SELECT FKNUM

**FKPOS

**EXTI1

**NETWR

**FROM VFKP

**INTO CORRESPONDING FIELDS OF TABLE IT_VFKP

**WHERE EXTI1 IN IT_RANGE .

**

**

**ENDLOOP.

the above coding is working fine but it not populated th e 2 internal table