topic Re: Abap SQL question in Application Development and Automation Discussions

Abap SQL question

Former Member — Wed, 26 Jan 2011 08:47:38 GMT

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.

Re: Abap SQL question

Wil_Wilstroth — Wed, 26 Jan 2011 09:03:47 GMT

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

Re: Abap SQL question

Former Member — Wed, 26 Jan 2011 12:32:48 GMT

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.

Re: Abap SQL question

Former Member — Wed, 26 Jan 2011 13:23:08 GMT

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.

Re: Abap SQL question

Former Member — Wed, 26 Jan 2011 13:26:01 GMT

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

Re: Abap SQL question

Former Member — Wed, 26 Jan 2011 14:18:39 GMT

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

Re: Abap SQL question

Former Member — Wed, 26 Jan 2011 14:49:08 GMT

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.

Re: Abap SQL question

Clemenss — Wed, 26 Jan 2011 15:20:41 GMT

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