Query on select-options

[Former Member]

Hi All,

I have a select-option so_bp.

I fired the following select query:-

select partner partner_guid

from but000

into table it_bp

where partner in s_bp.

Now, it_bp contains all BPs that exist in table BUT000 for the range so_bp-low to so_bp-high.

But, I also want to fill another table containing entries from so_bp that dont exist in table BUT000.

e.g:-

let so_bp-low = 850 and so_bp-high = 875.

Records 860, 863 and 868 DONT EXIST in table.

So, it_bp contains all records from 850 to 875 execpt the above three records.

I want thse three records into another internal table.

how do I achieve this ?

Regards,

Ashish

[Former Member]

Hi Ashish,

Declare another internal table same as it_bp say its name is it_bp_other.

select partner partner_guid

from but000

into table it_bp_other.

delete it_bp_other where not partner in s_bp.

Now the internal tbale it_bp_other holds the records what you expected( those doesn't exist in it_bp) .

regards,

Yellappa.

[Former Member]

Thanks Yellappa and Madhuri for replying,

Yellappa... that logic would work. But, but000 contains lakhs of records. Wouldnt it hamper the performance ?

Can you please suggest me of some better way to do it

Madhuri.. I think your select query would return all BPs that are not in the range... thats not my requirement !!!!!

Regards,

Ashish

[Former Member]

select partner partner_guid

from but000

into table it_bp

where partner not in s_bp.

use not in where condition

[Former Member]

Hi Madhavi,

Negative statement like NOT is not recommeded when using select statement as it causes performance issue. Instead select all the records from the table and delete the records from internal table. I have mentioned in this same forum above.

Regards,

Yellappa.

[Former Member]

Hi Yellappa,

I think even your logic would return all records in BUT000 that are not in the range I specified (Same as the select query that Madhuri has written).

But according to my requirement, only records 860... 863 and 868 should have gone in the other internal table (NOT ALL records that are outside the range)

Regards,

Ashish

[Former Member]

Hi Ashish,

Sorry for my misunderstading. I have added additional code here to the already suggested code where s_bp may have multiple ranges and changes are in bold letters.

sort s_bp by low.

loop at s_bp.

l_times = s_bp-low.

do.

read table it_bp with key partner = l_times.

if sy-subrc ne 0.

lt_bp-partner = it_bp-partner.

append lt_bp.

endif.

l_times = l_times + 1.

if l_times gt s_bp-high.

exit.

endif.

enddo.

endloop.

regards,

Yellappa.

[Former Member]

Thanks Yellappa !!!!

[Former Member]

just a small correction,

Instead of

lt_bp-partner = it_bp-partner

it should be

lt_bp-partner = l_times.

Regards,

Ashish

[Former Member]

Hi

First get all the records into one table and loop at the itab put condition if value = 860 or value = 863 or value = 868 then append wa into itab1

loop at itab into wa where value = '860' or value or value = '863' or value = '868'

append wa into itab1

endloop.

Regards

Shiva

[Former Member]

Hi Siva,

360... 363 and 368 are just example records I have given.

I dont know which records are present and which are not present in the table... so how do I write IF .. ?

Regards,

Ashish

[Former Member]

Hi

Then where will you get the other values? if you get more values then pass the to a range and user where r_value

regards

Shiva

[Former Member]

Try the below code

I have assumes only ranges would be inputted and NO EXTENSION has been added in the declaration of S-BP.

l_times = s_bp-low.
do.
  read table it_bp with key partner = l_times.
  if sy-subrc ne 0.
    lt_bp-partner = it_bp-partner.
    append lt_bp.
  endif.
  l_times = l_times + 1.
  if l_times gt s_bp-high.
    exit.
  endif.
enddo.

<REMOVED BY MODERATOR>

Thanks

Balaji

Edited by: Alvaro Tejada Galindo on Feb 21, 2008 11:06 AM

[Former Member]

Hi Balaji,

thats exactly what I was looking for...

(truly speaking) I thought of that logic ... but thought it was not a good idea

anyways ... I'll go with that logic !!!!

Regards,

Ashish