Inner join not working correctly

[Former Member]

Hi all,

Below shown is the select query generated by ABAP Query.At the time of running the abap query we are putting the ERDAT date of the contract group and Group type eq 'G' and VBAK-vkorg.

select VBSK_{ERDAT VBSK}ERNAM VBSK_{ERNUM VBSK}SAMMG VBSK_{SMART VBSK}VBNUM VBFS_{SAMMG VBFS}VBELN VBAK_{VBELN VBAK}VKORG

into (VBSK-ERDAT , VBSK-ERNAM , VBSK-ERNUM , VBSK-SAMMG , VBSK-SMART , VBSK-VBNUM , VBFS-SAMMG , VBFS-VBELN , VBAK-VBELN

, VBAK-VKORG )

from ( VBSK

inner join VBFS

on VBFS_{SAMMG = VBSK}SAMMG

inner join VBAK

on VBAK_{VBELN = VBFS}VBELN )

where VBSK~ERDAT in SP$00001

and VBSK~SAMMG in SP$00003

and VBSK~SMART in SP$00002

and VBAK~VKORG in SP$00004.

When it is executed it is retrieving all the records.

Can anybody plase suggest me what is wrong in the above mentioned query.

thanks,

Mohit

[Former Member]

select VBSK~ERDAT VBSK~ERNAM VBSK~ERNUM VBSK~SAMMG VBSK~SMART VBSK~VBNUM VBFS~SAMMG VBFS~VBELN VBAK~VBELN VBAK~VKORG
into (VBSK-ERDAT , VBSK-ERNAM , VBSK-ERNUM , VBSK-SAMMG , VBSK-SMART , VBSK-VBNUM , VBFS-SAMMG , VBFS-VBELN , VBAK-VBELN
, VBAK-VKORG )
from  VBSK
inner join VBFS
on VBFS~SAMMG = VBSK~SAMMG
inner join VBAK
on VBAK~VBELN = VBFS~VBELN 
where VBSK~ERDAT in SPselect VBSK~ERDAT VBSK~ERNAM VBSK~ERNUM VBSK~SAMMG VBSK~SMART VBSK~VBNUM VBFS~SAMMG VBFS~VBELN VBAK~VBELN VBAK~VKORG
into (VBSK-ERDAT , VBSK-ERNAM , VBSK-ERNUM , VBSK-SAMMG , VBSK-SMART , VBSK-VBNUM , VBFS-SAMMG , VBFS-VBELN , VBAK-VBELN
, VBAK-VKORG )
from  VBSK
inner join VBFS
on VBFS~SAMMG = VBSK~SAMMG
inner join VBAK
on VBAK~VBELN = VBFS~VBELN 
where VBSK~ERDAT in SP$00001
and VBSK~SAMMG in SP$00003
and VBSK~SMART in SP$00002
and VBAK~VKORG in SP$00004.

and VBSK~SAMMG in SP[code]select VBSK~ERDAT VBSK~ERNAM VBSK~ERNUM VBSK~SAMMG VBSK~SMART VBSK~VBNUM VBFS~SAMMG VBFS~VBELN VBAK~VBELN VBAK~VKORG
into (VBSK-ERDAT , VBSK-ERNAM , VBSK-ERNUM , VBSK-SAMMG , VBSK-SMART , VBSK-VBNUM , VBFS-SAMMG , VBFS-VBELN , VBAK-VBELN
, VBAK-VKORG )
from  VBSK
inner join VBFS
on VBFS~SAMMG = VBSK~SAMMG
inner join VBAK
on VBAK~VBELN = VBFS~VBELN 
where VBSK~ERDAT in SP$00001
and VBSK~SAMMG in SP$00003
and VBSK~SMART in SP$00002
and VBAK~VKORG in SP$00004.
[/code]3
and VBSK~SMART in SP[code]select VBSK~ERDAT VBSK~ERNAM VBSK~ERNUM VBSK~SAMMG VBSK~SMART VBSK~VBNUM VBFS~SAMMG VBFS~VBELN VBAK~VBELN VBAK~VKORG
into (VBSK-ERDAT , VBSK-ERNAM , VBSK-ERNUM , VBSK-SAMMG , VBSK-SMART , VBSK-VBNUM , VBFS-SAMMG , VBFS-VBELN , VBAK-VBELN
, VBAK-VKORG )
from  VBSK
inner join VBFS
on VBFS~SAMMG = VBSK~SAMMG
inner join VBAK
on VBAK~VBELN = VBFS~VBELN 
where VBSK~ERDAT in SP$00001
and VBSK~SAMMG in SP$00003
and VBSK~SMART in SP$00002
and VBAK~VKORG in SP$00004.
[/code]2
and VBAK~VKORG in SP[code]select VBSK~ERDAT VBSK~ERNAM VBSK~ERNUM VBSK~SAMMG VBSK~SMART VBSK~VBNUM VBFS~SAMMG VBFS~VBELN VBAK~VBELN VBAK~VKORG
into (VBSK-ERDAT , VBSK-ERNAM , VBSK-ERNUM , VBSK-SAMMG , VBSK-SMART , VBSK-VBNUM , VBFS-SAMMG , VBFS-VBELN , VBAK-VBELN
, VBAK-VKORG )
from  VBSK
inner join VBFS
on VBFS~SAMMG = VBSK~SAMMG
inner join VBAK
on VBAK~VBELN = VBFS~VBELN 
where VBSK~ERDAT in SP$00001
and VBSK~SAMMG in SP$00003
and VBSK~SMART in SP$00002
and VBAK~VKORG in SP$00004.
[/code]4.

leaf out the ( ) perhaps ?

and I don't see the group type EQ G in you're select statement ?

kind regards

arthur

[Former Member]

select VBSK_{ERDAT VBSK}ERNAM VBSK_{ERNUM VBSK}SAMMG VBSK_{SMART VBSK}VBNUM VBFS_{SAMMG VBFS}VBELN VBAK_{VBELN VBAK}VKORG

into (VBSK-ERDAT , VBSK-ERNAM , VBSK-ERNUM , VBSK-SAMMG , VBSK-SMART , VBSK-VBNUM , VBFS-SAMMG , VBFS-VBELN , VBAK-VBELN

, VBAK-VKORG )

from VBSK

inner join VBFS

on VBSK_{SAMMG = VBFS}SAMMG

inner join VBAK

on VBAK_{VBELN = VBSK}VBELN

where VBSK~ERDAT in SP$00001

and VBSK~SAMMG in SP$00003

and VBSK~SMART in SP$00002

and VBAK~VBTYP eq 'G'

and VBAK~VKORG in SP$00004.

reward if useful.

Amit Singla

[Former Member]

Hi,

the field group type is SMART in the table VBSK and also i cannot pass VBAK-vbtyp in the query.

Thanks,

Mohit

[seshatalpasai_madala]

Hi,

What are these

SP$00001

SP$00003

SP$00002

SP$00004.

Do they have any data, check if they are empty.

Regards,

Sesh

[Former Member]

Hi,

In the select statement

select VBSK_{ERDAT VBSK}ERNAM VBSK_{ERNUM VBSK}SAMMG VBSK_{SMART VBSK}VBNUM VBFS_{SAMMG VBFS}VBELN VBAK_{VBELN VBAK}VKORG

into (VBSK-ERDAT , VBSK-ERNAM , VBSK-ERNUM , VBSK-SAMMG , VBSK-SMART , VBSK-VBNUM , VBFS-SAMMG , VBFS-VBELN , VBAK-VBELN

, VBAK-VKORG )

from ( VBSK

inner join VBFS

on VBFS_{SAMMG = VBSK}SAMMG

inner join VBAK

on VBAK_{VBELN = VBFS}VBELN )

where VBSK~ERDAT in SP$00001

and VBSK~SAMMG in SP$00003

and VBSK~SMART in SP$00002

and VBAK~VKORG in SP$00004.

Below mentioned is the select options name in which the values entered on the selection screen screen will be passed.

Please let me know what will be the effect if in am not passing the SAMMG value in the selection screen.

SP$00001

SP$00003

SP$00002

SP$00004

[Former Member]

data : begin of it1 occurs 0,

define all fields here of all three tables.

end of it1.

data : begin of it2 occurs 0,

define vbak field here.

endif.

select VBSK_{ERDAT VBSK}ERNAM VBSK_{ERNUM VBSK}SAMMG VBSK_{SMART VBSK}VBNUM VBFS_{SAMMG VBFS}VBELN from VBSK

inner join VBFS

on VBSK_{SAMMG = VBFS}SAMMG

into corresponding fields of itab1

where VBSK~ERDAT in SP$00001

and VBSK~SAMMG in SP$00003

and VBSK~SMART in SP$00002.

if not it1[] is initial.

select VBELN

VKORG

from vbak

into corresponding fields of it2 for all entries in it1

where vbtyp = 'G' and

vbeln = it1-vbeln.

endif.

loop at it1.

read table it2 with key vbeln = it1-vbeln.

if sy-subrc = 0.

it1-vbeln1 = it2-vbeln.

it1-vkorg =it2-vkorg.

modify it1.

endif.

endloop.

[Former Member]

Hi Amit,

The select statement is generated by ABAP Query itself and our problemis why this select statement is not working correctly in the production system.The output contains Contract group that sre not created in the date range specified on the selection screen and it will pick that contract group also that are not of type 'G'.

Thanks,

Mohit