topic Re: Inner join not working correctly in Application Development and Automation Discussions

Inner join not working correctly

Former Member — Wed, 19 Sep 2007 07:49:49 GMT

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

Re: Inner join not working correctly

Former Member — Wed, 19 Sep 2007 08:14:22 GMT

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

Re: Inner join not working correctly

Former Member — Wed, 19 Sep 2007 08:46:11 GMT

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

Re: Inner join not working correctly

Former Member — Wed, 19 Sep 2007 08:50:18 GMT

Hi,

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

Thanks,

Mohit

Re: Inner join not working correctly

seshatalpasai_madala — Wed, 19 Sep 2007 08:52:14 GMT

Hi,

What are these

SP$00001

SP$00003

SP$00002

SP$00004.

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

Regards,

Sesh

Re: Inner join not working correctly

Former Member — Wed, 19 Sep 2007 08:56:45 GMT

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

Re: Inner join not working correctly

Former Member — Wed, 19 Sep 2007 09:24:47 GMT

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.

Re: Inner join not working correctly

Former Member — Wed, 19 Sep 2007 09:37:11 GMT

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