Selecting table entries which do not exist in another table

[Former Member]

Hi Gurus,

I've a requrirement to get The lot no.s for which no usage decision is taken. The lot header is available in QALS and Decision is available in QAVE. QAVE doesnt store the lots for which no usage decision is taken. so i need to get the Lots in QALS table for which there is no entry exist in QAVE table. Pl guide me how to code. I used inner join but no use.

The following logic i've used. pl correct me if i'm wrong.

SELECT a~prueflos

a~werk

a~herkunft

a~enstehdat

a~pastrterm

a~paendterm

a~matnr

a~ebeln

a~losmenge

b~prueflos

FROM qals AS a INNER JOIN qave AS b

ON a_{prueflos EQ b}prueflos

INTO TABLE lt_qals WHERE a~werk EQ s_werks AND

a~herkunft EQ s_orig AND

a~matnr EQ s_matnr AND

a~enstehdat EQ s_lotdt and

b~prueflos eq space.

-Rajiv

[Former Member]

Hi Gurus,

I've a requrirement to get The lot no.s for which no usage decision is taken. The lot header is available in QALS and Decision is available in QAVE. QAVE doesnt store the lots for which no usage decision is taken. so i need to get the Lots in QALS table for which there is no entry exist in QAVE table. Pl guide me how to code. I used inner join but no use.

The following logic i've used. pl correct me if i'm wrong.

SELECT a~prueflos

a~werk

a~herkunft

a~enstehdat

a~pastrterm

a~paendterm

a~matnr

a~ebeln

a~losmenge

b~prueflos

FROM qals AS a INNER JOIN qave AS b

ON a_{prueflos EQ b}prueflos

INTO TABLE lt_qals WHERE a~werk EQ s_werks AND

a~herkunft EQ s_orig AND

a~matnr EQ s_matnr AND

a~enstehdat EQ s_lotdt and

b~prueflos eq space.

-Rajiv

[matt]

Don't use an inner join, use WHERE NOT EXISTS( SELECT * FROM qave ...). Read the F1 help for more details.

matt

[Former Member]

Hi Mr. Matt,

I've given the following condition, but it was not working. pl correct me if i'm wrong.

SELECT prueflos

werk

herkunft

enstehdat

pastrterm

paendterm

matnr

ebeln

losmenge

prueflos

FROM qals INTO TABLE ls_qals

WHERE prueflos not in ( select prueflos FROM qave ) and

werk EQ s_werks AND

herkunft EQ s_orig AND

matnr EQ s_matnr AND

enstehdat EQ s_lotdt .

ENDSELECT.

-Rajiv.

[Former Member]

SELECT prueflos

werk

herkunft

enstehdat

pastrterm

paendterm

matnr

ebeln

losmenge

prueflos

FROM qals INTO CORRESPONDING FIELDS OF TABLE ls_qals where werk = 'SP01'.

select * from qave into table l_qave for ALL ENTRIES IN ls_qals

where prueflos <> ls_qals-prueflos and VWERKS = ls_qals-werk and remaining where conditions.

loop at lq_qals into wa.

read table l_qave with key prueflos = wa-prueflos vwerks = wa-werk.

if sy-subrc <> 0.

append wa to itab. " append only if entry is not in QAVE

endif.

endloop.

[Former Member]

Hi Matt,

Sory, i've given wrong condition there, and i corrected that and got the records which i wanted. and the code is

SELECT prueflos

werk

herkunft

enstehdat

pastrterm

paendterm

matnr

ebeln

losmenge

prueflos

FROM qals INTO TABLE lt_qals

WHERE prueflos not IN ( SELECT prueflos FROM qave ) AND

werk in s_werks AND

herkunft in s_orig AND

matnr in s_matnr AND

enstehdat in s_lotdt .

Thank u for ur Suggestion.

-Rajiv

[Former Member]

Hi,

First select all required fields from QALS and then use FOR ALL ENTRIES statement to select data from QAVE with the common key Inspection Lot Number. This field is key in both the tables

Let me know if this clear.

Thanks

Sourav

[Former Member]

hi

i think u r using select options.

try this

SELECT a~prueflos
a~werk
a~herkunft
a~enstehdat
a~pastrterm
a~paendterm
a~matnr
a~ebeln
a~losmenge
b~prueflos
FROM qals AS a INNER JOIN qave AS b
ON a~prueflos EQ b~prueflos
INTO TABLE lt_qals WHERE a~werk in s_werks AND
a~herkunft in s_orig AND
a~matnr in s_matnr AND
a~enstehdat in s_lotdt and
b~prueflos is initial.

or

SELECT a~prueflos
a~werk
a~herkunft
a~enstehdat
a~pastrterm
a~paendterm
a~matnr
a~ebeln
a~losmenge
b~prueflos
FROM qals AS a INNER JOIN qave AS b
ON a~prueflos EQ b~prueflos
INTO TABLE lt_qals WHERE a~werk in s_werks AND
a~herkunft in s_orig AND
a~matnr in s_matnr AND
a~enstehdat in s_lotdt and
b~prueflos is nulll.

Regards

Sajid

[Former Member]

Hi,

You need to use OUTER Join instead of Outer Join, as you need a data from two table, where in one table you can have some value, which does not exists in other table.

So just have a try.

You can check it in T.Code : SQVI . Just create a JOIN of two table and selecting the join in layout there , right click and change it into OUTER JOIN.

Hope it may help you.

Regds,

Anil