topic Re: Query on select-options in Application Development and Automation Discussions

Query on select-options

Former Member — Thu, 21 Feb 2008 13:01:46 GMT

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

Re: Query on select-options

Former Member — Thu, 21 Feb 2008 13:06:30 GMT

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.

Re: Query on select-options

Former Member — Thu, 21 Feb 2008 13:06:57 GMT

select partner partner_guid

from but000

into table it_bp

where partner not in s_bp.

use not in where condition

Re: Query on select-options

Former Member — Thu, 21 Feb 2008 13:07:54 GMT

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

Re: Query on select-options

Former Member — Thu, 21 Feb 2008 13:12:19 GMT

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

Re: Query on select-options

Former Member — Thu, 21 Feb 2008 13:12:20 GMT

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.

Re: Query on select-options

Former Member — Thu, 21 Feb 2008 13:15:22 GMT

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

Re: Query on select-options

Former Member — Thu, 21 Feb 2008 13:19:07 GMT

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

Re: Query on select-options

Former Member — Thu, 21 Feb 2008 13:21:58 GMT

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

Re: Query on select-options

Former Member — Thu, 21 Feb 2008 13:24:26 GMT

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

Re: Query on select-options

Former Member — Thu, 21 Feb 2008 13:25:06 GMT

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

Re: Query on select-options

Former Member — Thu, 21 Feb 2008 13:28:08 GMT

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.

Re: Query on select-options

Former Member — Thu, 21 Feb 2008 13:30:34 GMT

Thanks Yellappa !!!!

Re: Query on select-options

Former Member — Fri, 22 Feb 2008 06:58:30 GMT

just a small correction,

Instead of

lt_bp-partner = it_bp-partner

it should be

lt_bp-partner = l_times.

Regards,

Ashish