topic Re: Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Thu, 31 Aug 2006 06:50:47 GMT

Hi experts,

Please let me know inner join for following two queries, i am not able to write , it's giving error. Points will be given for sure.

1) Select customer menu’s associated with the retroactive pricing condition record

Select VBAP.VBELN (Contract #)

VBAP.POSNR (Item #)

Where KONV.KNUMH = KONH.KNUMH (Condition record number)

VBAK.KNUMV = KONV.KNUMV (Document condition number)

VBAK.AUART = “ZMNU” (Document Type)

VBAP.POSNR = KONV.KPOSN (Item #)

2) Select all customer menu and item combinations that are associated with each customer pricing contact that have an associated output condition record in table – B504 (Customer Pricing Contact – PARNR)

Select B504.PARNR (Contact #)

VBPA.VBELN (Contract #),

VBPA.POSNR (Item #)

Where VBPA.PARNR = B504.PARNR (Contact #)

VBPA.PARVW = “Z8”

VBAK.AUART = “ZMNU”

Inner Join VBAK.VBELN = VBPA.VBELN

Re: Inner Join

Former Member — Thu, 31 Aug 2006 07:03:10 GMT

YOu want to have it in native SQL or in abap open SQL . Becosue what you have written here looks for native...

Re: Inner Join

Former Member — Thu, 31 Aug 2006 07:04:04 GMT

1) It should be like this:

DATA: BEGIN OF RESULT,

VBELN TYPE VBELN,

POSNR TYPE POSNR,

END OF RESULT.

SELECT A_{VBELN B}POSNR INTO RESULT

FROM VBAK AS A INNER JOIN VBAP AS B

ON A_{VBELN = B}VBELN

WHERE A~AUART = 'ZMNU' .

WRITE: / RESULT-VBELN, RESULT-POSNR.

ENDSELECT.

You can't use the table KONV because it's a cluster table.

2- The second query will be similar.

Max

Re: Inner Join

Former Member — Thu, 31 Aug 2006 07:14:02 GMT

Hi Anand,

Please find the queries.

1. SELECT <field list>

INTO <target list>

FROM

( ( VBAK JOIN VBAP ON VBAK_{VBELN = VBAP}VBELN )

( KONH JOIN KONV ON KONH_{KNUMH = KONV}KNUMH ) )

WHERE VBAK_{KNUMV = KONV}KNUMV AND

VBAK~AUART = 'ZMNU' AND

VBAP_{POSNR = KONV}KPOSN.

2. SELECT <field list>

INTO <target list>

FROM

( ( VBAK JOIN VBPA ON VBAK_{VBELN = VBAP}VBELN )

JOIN B504 ON VBPA.PARNR = B504.PARNR )

WHERE

VBPA.PARVW = 'Z8' AND

VBAK.AUART = 'ZMNU'.

Regards,

Prasanth

Re: Inner Join

Former Member — Thu, 31 Aug 2006 07:17:09 GMT

SELECT A_{VBAK fields B}VBAP fields INTO TABLE internal table

FROM VBAK AS A INNER JOIN VBAP AS B

ON A_{VBELN = B}VBELN

WHERE A~AUART = Selection screen values .

Re: Inner Join

Former Member — Thu, 31 Aug 2006 08:34:34 GMT

1) Select customer menu’s associated with the retroactive pricing condition record

Select VBAP.VBELN (Contract #)

VBAP.POSNR (Item #)

Where KONV.KNUMH = KONH.KNUMH (Condition record number)

VBAK.KNUMV = KONV.KNUMV (Document condition number)

VBAK.AUART = “ZMNU” (Document Type)

VBAP.POSNR = KONV.KPOSN (Item #)

--> I did not get your point of using konh in the above.

select A_{vbeln B}posnr into table itab

from vbak as A inner join vbap as b on A_{vbeln = b}vbeln

inner join konv as c on A_{knumv = C}knumv

where b_{posnr = c}kposn and A~auart = 'ZMNU'.

~~-XXX~~-

Select B504.PARNR (Contact #)

VBPA.VBELN (Contract #),

VBPA.POSNR (Item #)

Where VBPA.PARNR = B504.PARNR (Contact #)

VBPA.PARVW = “Z8”

VBAK.AUART = “ZMNU”

Inner Join VBAK.VBELN = VBPA.VBELN

-->

SELECT A_{VBELN A}POSNR B~PARNR into table itab

from VBPA as A inner join VBAK as K on A_{VBELN = K}VBELN

inner join B504 as B on A_{PARNR = B}PARNR

where A_{PARVW = 'Z8' and K}AUART = 'ZMNU'.

Message was edited by: Anurag Bankley

Re: Inner Join

Former Member — Thu, 31 Aug 2006 16:13:39 GMT

Thanx Max,

This has really helped alot.

I have written the code, it's final testing will be tomorrow, if some thing goes wrong i will again reply with queries, if goes right then i will close this thread.

Thanx for your help.

Anand

Re: Inner Join

Former Member — Thu, 31 Aug 2006 16:16:29 GMT

anurag u helped me lot, thanx

I have given points to every one & have given more points to you & max. As i replied to max that i have written the code & testing would be tomorrow, so unce evrything goes right i will close the thred or else i will post again with the queries.

thanx for help