Inner join failure after using right operation

gupta_123 — Fri, 22 Jul 2022 03:38:20 GMT

  SELECT MSG~EBELN,          " MSEG-EBELN,  " new line insrted for po for at end of work

VRP~WERKS, " VBRP-WERKS,
TW~NAME1, " T001W-NAME1,
VRP~REGIO_ANA, " VBRP-REGIO_ANA,
T5U~BEZEI, " T005U-BEZEI,
* KN~REGIO, " KNA1-REGIO,
LF~LIFNR, " LFA1-LIFNR,
LF~NAME1, " LFA1-NAME1,
* KN~STCD3, " KNA1-STCD3,
LF~REGIO, " LFA1-REGIO,
LF~UF, " LFA1-UF,
MSG~MBLNR, " MSEG-MBLNR,
MSG~ZEILE, " MSEG-ZEILE,
MSG~CHARG, " MSEG-CHARG,
MSG~MATNR, " MSEG-MATNR,
MKT~MAKTX, " MAKT-MAKTX,
MSG~BWART, " MSEG-BWART,
VBR~VBELN, " VBRk-VBELN,
VBR~FKDAT, " VBRK-FKDAT,
VRP~FKIMG, " VBRP-FKIMG,
MSG~MENGE, " MSEG-MENGE,
* MSG~EBELN, " MSEG-EBELN,
VRP~NETWR, " vbrp-netwr,
VRP~MEINS, " VBRP-MEINS,
VBR~KNUMV, " condition
VRP~ERDAT, " entry date
VRP~ERNAM, " created by
MC~STEUC " HSN

INTO TABLE @LT_TBL
FROM VBRK AS VBR
* FROM VBRP AS VRP
LEFT JOIN VBRP AS VRP ON VRP~VBELN = VBR~VBELN
LEFT JOIN MSEG AS MSG ON MSG~MBLNR = VRP~VGBEL AND MSG~ZEILE = RIGHT(VRP~VGPOS ,4) AND MSG~WERKS = VRP~WERKS AND MSG~MATNR = VRP~MATNR

LEFT JOIN LFA1 AS LF ON LF~LIFNR = MSG~LIFNR
LEFT JOIN T005U AS T5U ON T5U~BLAND = VRP~REGIO_ANA
LEFT JOIN T001W AS TW ON TW~WERKS = VRP~WERKS
LEFT JOIN MAKT AS MKT ON MKT~MATNR = MSG~MATNR
LEFT JOIN MARC AS MC ON MC~MATNR = VRP~MATNR AND MC~WERKS = VRP~WERKS
* LEFT JOIN KNA1 AS KN ON ( KN~ = VRP~WERKS )
** LEFT JOIN AS KN ON ( KN~EWGIO = VRP~REGIO_ANA )

WHERE VRP~WERKS IN @SO_PLANT
AND VRP~VBELN IN @SO_CHLN
AND VBR~FKDAT IN @SO_DT
AND VBR~FKART EQ 'ZSN'
AND LF~LIFNR IN @SO_VEND
AND MSG~SHKZG = 'S'
AND T5U~SPRAS = 'EN'
AND T5U~LAND1 = 'IN'.

getting issue while using MSG~ZEILE = RIGHT(VRP~VGPOS ,4) inside the code, because both mseg and vbrp field are numeric but field leanth is differnt how to write that particular code.

Re: Inner join failure after using right operation

Sandra_Rossi — Fri, 22 Jul 2022 06:00:16 GMT

Just a legibility remark: what is the interest of using alias VBR for table VBRK, VRP for table VBRP, MSG on MSEG, etc.? Just to confuse the reader? Why don't you just NOT define aliases, and just keep the full names VBRK, VBRP and MSEG everywhere?

Re: Inner join failure after using right operation

Sandra_Rossi — Fri, 22 Jul 2022 06:21:24 GMT

Define the SQL functions at the left-hand side of the conditions, not at the right-hand side.

This is correct:

WHERE ... RIGHT( VRP~VGPOS,4 ) = MSG~ZEILE ...

This is wrong:

WHERE ... MSG~ZEILE = RIGHT( VRP~VGPOS,4 ) ...

NB: SQL functions start working from ABAP 7.50 (e.g. RIGHT) Some functions are added in later ABAP versions.

topic Inner join failure after using right operation in Application Development and Automation Discussions

Inner join failure after using right operation

Re: Inner join failure after using right operation

Re: Inner join failure after using right operation