joining 3 tables

[Former Member]

Hi ,

i want to join convert the following code into a select with join of 3 tables please help me

SELECT LL02_{LGNUM LL02}LOBID

LL02_{TBNUM LL02}TBPOS

LL02_{TBSTT LL02}BWLVS

LL02_{STATU LL02}EDATU

LL02_{EZEIT LTBP}TAMEN

INTO (WA-LGNUM,WA-LOBID,WA-TBNUM,

WA-TBPOS,WA-TBSTT,WA-BWLVS,

WA-STATU,WA-EDATU,WA-EZEIT,

HLP_TAMEN)

FROM LL02 JOIN LTBP

ON LTBP_{LGNUM = LL02}LGNUM AND

LTBP_{TBNUM = LL02}TBNUM AND

LTBP_{TBPOS = LL02}TBPOS

WHERE LL02~LGNUM = P_LGNUM

AND LL02~BWLVS IN S_BWLVS

AND LL02~VLTYP IN S_VLTYP

AND LL02~NLTYP IN S_NLTYP

AND LTBP~ELIKZ = ' '.

SELECT SINGLE *

FROM LTBK

WHERE LGNUM = WA-LGNUM

AND TBNUM = WA-TBNUM

AND ( STATU = ' ' OR STATU = 'T' )

AND BDATU < G_CUTOFF_DATE.

endselect

[Former Member]

Hi ,

i want to join convert the following code into a select with join of 3 tables please help me

SELECT LL02_{LGNUM LL02}LOBID

LL02_{TBNUM LL02}TBPOS

LL02_{TBSTT LL02}BWLVS

LL02_{STATU LL02}EDATU

LL02_{EZEIT LTBP}TAMEN

INTO (WA-LGNUM,WA-LOBID,WA-TBNUM,

WA-TBPOS,WA-TBSTT,WA-BWLVS,

WA-STATU,WA-EDATU,WA-EZEIT,

HLP_TAMEN)

FROM LL02 JOIN LTBP

ON LTBP_{LGNUM = LL02}LGNUM AND

LTBP_{TBNUM = LL02}TBNUM AND

LTBP_{TBPOS = LL02}TBPOS

WHERE LL02~LGNUM = P_LGNUM

AND LL02~BWLVS IN S_BWLVS

AND LL02~VLTYP IN S_VLTYP

AND LL02~NLTYP IN S_NLTYP

AND LTBP~ELIKZ = ' '.

SELECT SINGLE *

FROM LTBK

WHERE LGNUM = WA-LGNUM

AND TBNUM = WA-TBNUM

AND ( STATU = ' ' OR STATU = 'T' )

AND BDATU < G_CUTOFF_DATE.

endselect

[Rui_Dantas]

Hi Gayatri,

LTBK / LTBP are header / item tables. It should be pretty straightforward to link them by LGNUM and LBNUM (as in fact your doing in your separate 2nd query). In your first query you just have to add an extra JOIN ltbk ON those two fields.

What exactly is your difficulty?

Regards,

Rui Dantas

[Former Member]

Hi

It can be like this:

SELECT LL02~LGNUM LL02~LOBID
       LL02~TBNUM LL02~TBPOS
       LL02~TBSTT LL02~BWLVS
       LL02~STATU LL02~EDATU
       LL02~EZEIT LTBP~TAMEN
   INTO (WA-LGNUM,WA-LOBID,WA-TBNUM,
         WA-TBPOS,WA-TBSTT,WA-BWLVS,
         WA-STATU,WA-EDATU,WA-EZEIT,
                            HLP_TAMEN)
      FROM LL02 JOIN  LTBK
        ON LTBK~LGNUM = LL02~LGNUM AND
           LTBK~TBNUM = LL02~TBNUM
                 JOIN LTBP
        ON LTBP~LGNUM = LL02~LGNUM AND
           LTBP~TBNUM = LL02~TBNUM AND
           LTBP~TBPOS = LL02~TBPOS
             WHERE LL02~LGNUM = P_LGNUM
               AND LL02~BWLVS IN S_BWLVS
               AND LL02~VLTYP IN S_VLTYP
               AND LL02~NLTYP IN S_NLTYP
               AND ( LTBK~STATU = ' ' OR LTBK~STATU = 'T' )
               AND LTBK~BDATU < G_CUTOFF_DATE
               AND LTBP~ELIKZ = ' '.
ENDSELECT

But u should indicate which field of LTBK u nedd

Max

[Former Member]

TYPES: BEGIN OF ty_ltbp,
         lgnum TYPE ll02-lgnum,
         lobid TYPE ll02-lobid,
         tbnum TYPE ll02-tbnum,
         tbpos TYPE ll02-tbpos,
         tbstt TYPE ll02-tbstt,
         bwlvs TYPE ll02-bwlvs,
         statu TYPE ll02-statu,
         edatu TYPE ll02-edatu,
         ezeit TYPE ll02-ezeit,
         tamen TYPE ltbp-tamen,
       END OF ty_ltbp.

DATA t_ltbp  TYPE TABLE OF ty_ltbp.

RANGES: r_statu FOR ltbk-statu,
        r_bdatu FOR ltbk-bdatu.

REFRESH: r_statu,
         r_bdatu.

r_statu-sign   = 'I'.

r_statu-option = 'EQ'.

CLEAR r_statu-low.

APPEND r_statu.

r_statu-low    = 'T'.

APPEND r_statu.


r_bdatu-sign   = 'I'.

r_bdatu-option = 'LT'.

r_bdatu-low    = g_cutoff_date.

APPEND r_bdatu.


SELECT b~lgnum
       b~lobid
       b~tbnum
       b~tbpos
       b~tbstt
       b~bwlvs
       b~statu
       b~edatu
       b~ezeit
       c~tamen
FROM       ltbk AS a
INNER JOIN ll02 AS b
ON  a~lgnum EQ b~lgnum
AND a~tbnum EQ b~tbnum
INNER JOIN ltbp AS c
ON c~lgnum = b~lgnum AND
   c~tbnum = b~tbnum AND
   c~tbpos = b~tbpos
INTO TABLE t_ltbp
WHERE a~lgnum EQ p_lgnum
AND   a~statu IN r_statu
AND   a~bdatu IN r_bdatu
AND   b~bwlvs IN s_bwlvs
AND   b~vltyp IN s_vltyp
AND   b~nltyp IN s_nltyp
AND   c~elikz EQ space.

[Former Member]

If your question is solved, please close it and assign po(i)nts to those whose answers helped you.

Rob