Abap SQL question

[Former Member]

Hi,

I need to select the set of records only in Table A, but not in Table B. I think that the correct SQL is:

select k~vbeln

into data

from vbak as k

inner join vbfa as f on k_{vbeln = f}vbelv

where f~vbelv is null.

But the following message appears: u201CNo fields from the right-hand table of a LEFT OUTER JOIN may appear in the WHERE condition: u201CF~VBELBu201Du201D.

Thanks in advance,

Ricard.

[Wil_Wilstroth]

Hi Oscar,

I've tried your coding and I don't see anything wrong with it. For that query, I wrote it like below:

TYPES : BEGIN OF ty_data, 
          vbeln TYPE vbak-vbeln, 
        END OF ty_data.

DATA : lw_data TYPE ty_data. 

SELECT a~vbeln
       INTO lw_data
       FROM vbak AS a
       INNER JOIN vbfa AS b ON a~vbeln = b~vbelv
       WHERE b~vbelv IS NULL.
    <you can do your process here>
ENDSELECT.

Probably you need to check your variables declarations again... see if you are declaring correctly.

Cheers

William Wilstroth

Edited by: william wilstroth on Jan 26, 2011 5:04 PM

[Former Member]

sorry!!! it is not an inner join, is a left join.

data: str type string.

select k~vbeln

into str

from vbak as k

left join vbfa as f on k_{vbeln = f}vbelv

where f~vbelv is null.

endselect.

Ricard.

[Former Member]

IS NULL results in bypass of buffer and full database table scan, as I recall SAP's documentation on this usage....check out subquery, from tips and tricks feature in transaction SE30 for possible alternative (and stunningly fast) check against another table, which can be used to verify exists or not exists.

[Former Member]

Hi,

Try out this.

select a~vbeln a~ernam into corresponding fields of table it_vbak
  from vbfa as b inner join vbak as a on b~vbelv = a~vbeln
    where b~vbelv is null.

Thanks,

Anmol.

Edited by: anmol112 on Jan 26, 2011 8:27 AM

[Former Member]

Hi,

I need to select the set of records only in Table A, but not in Table B. 

is the case then i think Jovito is correct,

else you can try this

select vbelv from vbfa into corresponding fields of table it_vbfa.
if it_vbfa is not initial.
select vbeln into corresponding fields of table it_vbak
    from vbak for all entries in it_vbfa
       where vbeln ne it_vbfa-vbelv.

[Former Member]

Hi Oscar,

I think your query is wrong.

*I need to select the set of records only in Table A, but not in Table B. *

Assuming u want records in vbak that are not in vbfa

The correct SQL is:

select vbeln 
into data
from vbak 
where vbeln not in (select vbeln from vbfa)

This below query will give you a list of vbeln that are present in both vbak and vbfa and whose vbelv is not null.

select k~vbeln
into data
from vbak as k
inner join vbfa as f on k~vbeln = f~vbelv
where f~vbelv is null.

Regards,

Jovito

[Clemenss]

Hi Oscar & William,

first of all: The database meaning of NULL is that for the field in question no value is stored for an existing record.

In SAP tables we rarely have any NULL values. Only if you extend an existing table, i.e. append a new field, the database will expand the structure but not store INITIAL values for the new field in all records.

Second, just a hint: although the SAP example uses the mystifying ALIAS (AS) addition for joins, it does not help except reducing the code transparency:

SELECT vbak~vbeln
       INTO lw_data
       FROM vbak
       INNER JOIN vbfa ON vbak~vbeln = vbfa~vbelv
       WHERE vbfa~vbelv IS NULL.
    <you can do your process here>
ENDSELECT.

Is 100 % the same. Nevertheless it will never retrieve a single record because vbelv is never stored as a NULL value.

I think this can be solved using a subquery

DATA : lt_data TYPE table of vbak-vbeln.
SELECT vbeln
       INTO  lt_data   
       FROM vbak
       WHERE NOT EXISTS 
	     ( SELECT * FROM vbfa WHERE vbelv = vbak~vbeln ).
    <you can do your process here>
ENDSELECT.

To be honest: I did not try the code, never played with subqueries though I should do so.

Regards,

Clemens