topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Wed, 06 Jun 2007 06:38:20 GMT

Hi,

if i want to have another inner join where 'c' vbrk-vbeln = 'b' vbrp-vbeln, may i know how to have this in the below select statement. i cannot continue to have inner join as 'a' is not vbrp.

thanks

SELECT a_{vbelv a}vbeln b_{aubel b}vgbel b_{charg b}fkimg

FROM vbfa AS a INNER JOIN vbrp AS b

ON a_{vbeln = b}vbeln

INTO CORRESPONDING FIELDS OF it_vbeln

FOR ALL ENTRIES IN it_report

WHERE a~vbelv = it_report-vgbel AND

a~vbtyp_n = 'M'.

COLLECT it_vbeln.

ENDSELECT.

Re: select statement

Former Member — Wed, 06 Jun 2007 06:41:13 GMT

see the code highlighted

SELECT a_{vbelv a}vbeln b_{aubel b}vgbel b_{charg b}fkimg c~vkorg

FROM vbfa AS a INNER JOIN vbrp AS b

ON a_{vbeln = b}vbeln inner join VBRK as c

ON b_{vbeln = c}vbeln

INTO CORRESPONDING FIELDS OF it_vbeln

FOR ALL ENTRIES IN it_report

WHERE a_{vbelv = it_report-vgbel AND c}vkorg = '1000' and

a~vbtyp_n = 'M'.

COLLECT it_vbeln.

ENDSELECT.

Reward points if useful

Regards

Anji

Re: select statement

Azeemquadri — Wed, 06 Jun 2007 06:41:58 GMT

SELECT a_{vbelv a}vbeln b_{aubel b}vgbel b_{charg b}fkimgc~vbeln

FROM vbfa AS a INNER JOIN vbrp AS b

vbrk as c inner join vbrp as b

ON a_{vbeln = b}vbeln

on 'c'~vbeln = 'b' ~vbrp-vbeln,

INTO CORRESPONDING FIELDS OF it_vbeln

FOR ALL ENTRIES IN it_report

WHERE a~vbelv = it_report-vgbel AND

a~vbtyp_n = 'M'.

COLLECT it_vbeln.

ENDSELECT.

Re: select statement

Former Member — Wed, 06 Jun 2007 06:42:50 GMT

Hi,

SELECT a_{vbelv a}vbeln b_{aubel b}vgbel b_{charg b}fkimg

FROM (vbfa AS a INNER JOIN vbrp AS b

ON a_{vbeln = b}vbeln) inner join vbrk AS c on c_{vbeln = b}vbeln

INTO CORRESPONDING FIELDS OF it_vbeln

FOR ALL ENTRIES IN it_report

WHERE a~vbelv = it_report-vgbel AND

a~vbtyp_n = 'M'.

COLLECT it_vbeln.

ENDSELECT.

Regards,

Naveen

Re: select statement

Former Member — Wed, 06 Jun 2007 06:44:44 GMT

Hi Eliana,

Try this out....

SELECT a_{vbelv a}vbeln b_{aubel b}vgbel b_{charg b}fkimg

FROM vbfa AS a INNER JOIN vbrp AS b

ON a_{vbeln = b}vbeln INNER JOIN c on b_{vbeln = c}vbeln

INTO CORRESPONDING FIELDS OF it_vbeln

FOR ALL ENTRIES IN it_report

WHERE a~vbelv = it_report-vgbel AND

a~vbtyp_n = 'M'.

Get back if you have any queries.

Regards,

Younus

Reward Helpful Answers:-)

Re: select statement

Former Member — Wed, 06 Jun 2007 06:55:18 GMT

Hi,

You Can have the select statement like this......

SELECT a_{vbelv a}vbeln b_{aubel b}vgbel b_{charg b}fkimg

FROM vbrp AS b INNER JOIN vbrk AS c

ON c_{vbeln = b}-vbeln

INNER JOIN

vbfa AS a

ON a_{vbeln = b}vbeln

INTO CORRESPONDING FIELDS OF it_vbeln

FOR ALL ENTRIES IN it_report

WHERE a~vbelv = it_report-vgbel AND

a~vbtyp_n = 'M'.

COLLECT it_vbeln.

ENDSELECT.

Check the below sample prog on Inner joins:

REPORT demo_select_inner_join.

DATA: BEGIN OF wa,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

fldate TYPE sflight-fldate,

bookid TYPE sbook-bookid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY carrid connid fldate bookid.

SELECT p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

LOOP AT itab INTO wa.

AT NEW fldate.

WRITE: / wa-carrid, wa-connid, wa-fldate.

ENDAT.

WRITE / wa-bookid.

ENDLOOP.

This example links the columns CARRID, CONNID, FLDATE, and BOOKID of the table SPFLI, SFLIGHT, and SBOOK, and creates a list of booking numbers for all flights from Frankfurt to New York that are not fully booked. An alias name is assigned to each table.

Reward points if it is helpful..

Regards,

Omkar.