topic Re: select-options: in inner join in Application Development and Automation Discussions

select-options: in inner join

Former Member — Sat, 04 Aug 2007 10:12:16 GMT

in this peace of code i got error message like:

The column name "VBELN" has two meanings..

Please let me know how to resolve this select-options: problem

tables:vbap,lips,likp.

data: begin of first occurs 0,

vbeln1 like vbap-vbeln,

matnr2 like vbap-matnr,

werks3 like lips-werks,

route4 like likp-route,

end of first.

select-options: enter for vbap-vbeln.

select

a~vbeln

a~matnr

b~werks

c~route

into table first

from vbap as a

inner join lips as b

on a_{vbeln = b}vbeln

inner join likp as c

on b_{vbeln = c}vbeln

where vbeln in enter.

loop at first.

write:/2 sy-vline, first-vbeln1,

sy-vline,first-matnr2,

sy-vline,first-werks3,

sy-vline,first-route4.

endloop.

Thanks In advance

~~_SANDY~~~

Re: select-options: in inner join

Former Member — Sat, 04 Aug 2007 10:15:25 GMT

Hi,

Give like this,

tables:vbap,lips,likp.

data: begin of first occurs 0,

vbeln1 like vbap-vbeln,

matnr2 like vbap-matnr,

werks3 like lips-werks,

route4 like likp-route,

end of first.

select-options: enter for vbap-vbeln.

select

a~vbeln

a~matnr

b~werks

c~route

into table first

from vbap as a

inner join lips as b

on a_{vbeln = b}vbeln

inner join likp as c

on b_{vbeln = c}vbeln

where a~vbeln in enter.

loop at first.

write:/2 sy-vline, first-vbeln1,

sy-vline,first-matnr2,

sy-vline,first-werks3,

sy-vline,first-route4.

endloop.

Regards,

Padmam.

Re: select-options: in inner join

Former Member — Sat, 04 Aug 2007 10:16:20 GMT

Hi Sandy,

The error has occurred due to ambiguity of vbeln from which table it needs to be consider. Speicy vbeln as a~vbeln to resolve the issue.

Change code as in BOLD.

select-options: enter for vbap-vbeln.

select

a~vbeln

a~matnr

b~werks

c~route

into table first

from vbap as a

inner join lips as b

on a_{vbeln = b}vbeln

inner join likp as c

on b_{vbeln = c}vbeln

where a~vbeln in enter.

Don't use INNER JOINS for more than 2 levels as it will have great impact on performance and use FOR ALL ENTRIES instead of INNER JOINS.

Thanks,

Vinay

Message was edited by:

Vinaykumar G

Re: select-options: in inner join

Former Member — Sat, 04 Aug 2007 10:17:59 GMT

Hiii

Change the condition in where clause

Where a~VBELN IN ENTER

Regards

hitesh

Re: select-options: in inner join

Former Member — Sat, 04 Aug 2007 10:23:30 GMT

Padmam

but also it not showing out put Please hepl me in this regard.

Re: select-options: in inner join

Former Member — Sat, 04 Aug 2007 10:25:37 GMT

hi,

check the tables you are using for the vbeln,because it is having two different meanings in the data base tables because vbeln is used for the sales order as well as delivery number depending upon the database table.

please check this once.

please donot forget to reward points if it helps you.

all the best

Re: select-options: in inner join

Former Member — Sat, 04 Aug 2007 10:43:34 GMT

hi,

after using a~ also out put is not coming

When i debugged it it is not fetching any data

what to do?????????

Thanks in advance

~~~_sandy~~~

Re: select-options: in inner join

Former Member — Sat, 04 Aug 2007 10:48:20 GMT

Hi Sandy,

As you didn't have any relationship statements among the LIPS and VBAP.

Try as follows.

select-options: enter for vbap-vbeln.

select

a~vbeln

a~matnr

b~werks

c~route

into table first

from vbap as a

inner join lips as b

on a_{vbeln = b}vgbel and a_{posnr = b}vgpos

where a~vbeln in enter

inner join likp as c

on b_{vbeln = c}vbeln.

Thanks,

Vinay

Re: select-options: in inner join

Former Member — Sat, 04 Aug 2007 12:24:42 GMT

Hi,

Try this one,it is working fine,

tables:vbap,lips,likp.

data: begin of first occurs 0,

vbeln like vbap-vbeln,

matnr like vbap-matnr,

werks like lips-werks,

route like likp-route,

end of first.

select-options: enter for vbap-vbeln.

select a_{vbeln a}matnr b_{werks c}route into corresponding fields of table first

from vbap as a inner join lips as b on a_{vbeln = b}vgbel

inner join likp as c on b_{vbeln = c}vbeln

where a~vbeln in enter.

loop at first.

write:/2 sy-vline, first-vbeln,

sy-vline,first-matnr,

sy-vline,first-werks,

sy-vline,first-route.

endloop.

Regards,

Padmam.