Inner Join with Negative Condition

[Former Member]

Hi Gurus,

I have to select data from two table and I have to exclude some records based on user selection

I am slecting data fromm MARC and MVKE and I have provided two select option for MTPOS from MVKE and EKGRP from MARC

Exclude the entries where both the field values match with an AND condition

i.e MVKE_{MTPOS = XXXX AND MARC}EKGRP = YYYY

Can I write the below statement

Select . ....

JOIN ......

WHERE ( MVKE~MTPOS NOT IN S_MTPOS AND

MARC~EKGRP NOT IN S_EKGRP )

[Former Member]

A where clause on joined tables should work as a Open SQL does on a table read. In other words, if you have a select-options entry on the screen, and the user chose to OMIT a range (Exclude Between low high), I would expect to get the correct results with...

where..... IN selection-option range.

I would not, however, write a NOT IN select-option, though....the user would have already determined that with his Exclude choice, but it will be a lengthy data retrieval (full table scans).

On the other hand, if you expecting the user to say I-include between low and high as a range that is to be excluded then you can do your way, which should result in a full table scan on both tables, or you can retrieve the remainder of the where clause and the do a 2nd step to delete from your internal table where the values equal those to be omitted.

Is there any way you can convert this to include...such as looking up possible values, building a ranges table with Include where equal to a single value, then delete the entries that should be omitted from that ranges table....then it becomes

where field in my_ranges_table....a much faster retrieval.

[Subhankar]

It is not good to use not IN operator in select statement. It will take lot of time on select statement. Better select all the records into a internal table and delete records for the internal table using delete i_tab where f1 not in s_va1 and f2 not in s_var2.

Thanks

Subhankar

[Former Member]

select-options s_ebeln for v_ebeln

default 3000000010 to 3000000030 option NB SIGN I.

or

after entering the values in selection screen read the internal table and change the option manually

read s_ebeln .

s_ebeln-option = 'NB'.

modify s_ebeln .

generally the option will be BT ( between ) for any select option.

change option field to NB ( not between ) and use the select statement as usual then you will get all the values other than the values given in selection screen.

[Former Member]

you can give the following in the where condition,

MVKE_{MTPOS !=S_MTPOS AND MARC}EKGRP != S_EKGRP

Edited by: Jayakrishnn on Oct 11, 2011 10:41 AM

[Former Member]

Hi,

Actulally I am sure with the solutions and the problem as well,

But I think we can fix this by putting these values in exclude tab for the selection either manullly or programmically.

And then write a normal select query using IN operator.

Thanks,

Anmol.