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

inner join -select options

Former Member — Thu, 10 Jul 2008 10:48:52 GMT

Hi all,

I have a query, but it doesn't work when i give select-options range table in WHERE clause.

SELECT-OPTIONS :

l_bukrs FOR WA_INPUT-bukrs NO INTERVALS ,

SELECT

VBAK~BUKRS_VF AS BUKRS "COMPANY CODE

PA0001~ENAME "SALES EMPLOYEE NAME

KNA1~NAME1 "CLIENT

VBKD~BSTKD "PO NO

VBAK~VBELN "SO NO

VBAK~AUDAT "SO DATE

VBAP~POSNR "LINE

VBAP~KWMENG "QTY

VBAP~VRKME "UNIT

VBAP~NETPR "RATE

VBAP~NETWR "ORDER VALUE

VBAP~WAERK "DOCUEMNT CURRENCY

VBAP~MATNR "MATERIAL NO

VBAP~ARKTX "MATERIAL NAME

VBKD~KURSK "EXCHANGE RATE

INTO CORRESPONDING FIELDS OF TABLE IT_OPSALES

FROM VBAP

LEFT OUTER JOIN VBPA ON VBPA_{VBELN = VBAP}VBELN AND VBPA~PARVW = 'AF'

INNER JOIN PA0001 ON PA0001_{PERNR = VBPA}PERNR

INNER JOIN VBAK ON VBAK_{VBELN = VBAP}VBELN

INNER JOIN KNA1 ON KNA1_{KUNNR = VBAK}KUNNR

INNER JOIN VBKD ON VBKD_{VBELN = VBAK}VBELN AND VBKD_{POSNR = VBAP}POSNR

WHERE BUKRS IN L_BUKRS

it wont support when i give L_bukrs in inner join clause..!!

any idea to change the query??

thaks and regards

Jose

Re: inner join -select options

JozsefSzikszai — Thu, 10 Jul 2008 10:52:38 GMT

hi,

pls. do the following correction:

WHERE VBAK~BUKRS IN L_BUKRS

hope this helps

Re: inner join -select options

Former Member — Thu, 10 Jul 2008 10:53:10 GMT

Try using

VBAK~BUKRS in L_BUKRS.

instead of BUKRS in L_BUKRS

Re: inner join -select options

Former Member — Thu, 10 Jul 2008 10:54:56 GMT

try to change the where condition to ..

WHERE PA0001~BUKRS IN L_BUKRS

Re: inner join -select options

Former Member — Thu, 10 Jul 2008 10:55:05 GMT

Hi,

Declare the select option as s_bukrs .

SELECT-OPTIONS :

S_bukrs FOR WA_INPUT-bukrs NO INTERVALS ,

SELECT

VBAK~BUKRS_VF AS BUKRS "COMPANY CODE

PA0001~ENAME "SALES EMPLOYEE NAME

KNA1~NAME1 "CLIENT

VBKD~BSTKD "PO NO

VBAK~VBELN "SO NO

VBAK~AUDAT "SO DATE

VBAP~POSNR "LINE

VBAP~KWMENG "QTY

VBAP~VRKME "UNIT

VBAP~NETPR "RATE

VBAP~NETWR "ORDER VALUE

VBAP~WAERK "DOCUEMNT CURRENCY

VBAP~MATNR "MATERIAL NO

VBAP~ARKTX "MATERIAL NAME

VBKD~KURSK "EXCHANGE RATE

INTO CORRESPONDING FIELDS OF TABLE IT_OPSALES

FROM VBAP

LEFT OUTER JOIN VBPA ON VBPA_{VBELN = VBAP}VBELN AND VBPA~PARVW = 'AF'

INNER JOIN PA0001 ON PA0001_{PERNR = VBPA}PERNR

INNER JOIN VBAK ON VBAK_{VBELN = VBAP}VBELN

INNER JOIN KNA1 ON KNA1_{KUNNR = VBAK}KUNNR

INNER JOIN VBKD ON VBKD_{VBELN = VBAK}VBELN AND VBKD_{POSNR = VBAP}POSNR

WHERE BUKRS IN S_BUKRS

Hope this helps you.

Thanks & Regards,

Y.R.Prem Kumar

Re: inner join -select options

naveen_inuganti2 — Thu, 10 Jul 2008 10:55:54 GMT

Hi.....

Change it as

> ........VBAK-BUKRS_VF IN L_BUKRS

Thanks,

Naveen.I

Re: inner join -select options

Former Member — Thu, 10 Jul 2008 10:58:13 GMT

hi,

check bellow coding..

SELECT

VBAK~BUKRS_VF AS BUKRS "COMPANY CODE

PA0001~ENAME "SALES EMPLOYEE NAME

KNA1~NAME1 "CLIENT

VBKD~BSTKD "PO NO

VBAK~VBELN "SO NO

VBAK~AUDAT "SO DATE

VBAP~POSNR "LINE

VBAP~KWMENG "QTY

VBAP~VRKME "UNIT

VBAP~NETPR "RATE

VBAP~NETWR "ORDER VALUE

VBAP~WAERK "DOCUEMNT CURRENCY

VBAP~MATNR "MATERIAL NO

VBAP~ARKTX "MATERIAL NAME

VBKD~KURSK "EXCHANGE RATE

INTO CORRESPONDING FIELDS OF TABLE IT_OPSALES

FROM VBAP

LEFT OUTER JOIN VBPA ON VBPA_{VBELN = VBAP}VBELN AND VBPA~PARVW = 'AF'

INNER JOIN PA0001 ON PA0001_{PERNR = VBPA}PERNR

INNER JOIN VBAK ON VBAK_{VBELN = VBAP}VBELN

INNER JOIN KNA1 ON KNA1_{KUNNR = VBAK}KUNNR

INNER JOIN VBKD ON VBKD_{VBELN = VBAK}VBELN AND VBKD_{POSNR = VBAP}POSNR

WHERE VBAK~BUKRS IN L_BUKRS