topic Re: Inner Join with Negative Condition in Application Development and Automation Discussions

Inner Join with Negative Condition

Former Member — Fri, 07 Oct 2011 14:07:01 GMT

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 )

Re: Inner Join with Negative Condition

Former Member — Fri, 07 Oct 2011 15:07:52 GMT

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.

Re: Inner Join with Negative Condition

Subhankar — Fri, 07 Oct 2011 23:41:06 GMT

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

Re: Inner Join with Negative Condition

Former Member — Tue, 11 Oct 2011 06:26:54 GMT

select-options s_ebeln for v_ebeln

default 3000000010 to 3000000030 option NB SIGN I.

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.

Re: Inner Join with Negative Condition

Former Member — Tue, 11 Oct 2011 08:40:42 GMT

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

Re: Inner Join with Negative Condition

Former Member — Tue, 11 Oct 2011 09:38:17 GMT

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.