topic Re: select quer with inner join in Application Development and Automation Discussions

select quer with inner join

Former Member — Sat, 12 May 2007 14:39:20 GMT

table

can any please modify the query

thanking in advance

BEGIN OF t_initord OCCURS 0,

vbeln LIKE vbak-vbeln, " Sales Order

posnr LIKE vbap-posnr, " Sales Order Line Item

auart LIKE vbak-auart, " Sales Order Type

bezei LIKE tvakt-bezei, " Sales Order Type Text

billdo Like vbrk-vbeln, "billing document number

matnr LIKE vbfa-matnr, " Material

maktx LIKE makt-maktx, " Material Description

bstnk LIKE vbak-bstnk, " Customer PO number

bukrs LIKE vbak-bukrs_vf, " Company Code..

vkorg LIKE vbak-vkorg, " Sales Org

vtweg LIKE vbak-vtweg, " Distribution Channel

spart LIKE vbak-spart, " Division

vkgrp LIKE vbak-vkgrp, " Sales Group

augru LIKE vbak-augru, " Order Reason

abgru LIKE vbap-abgru, " Reson for rejection

kwmeng LIKE vbap-kwmeng, " Order Line quantit

deliv LIKE likp-vbeln, " Delivery Number

delit LIKE lips-posnr, " Delivery Number

rfmng LIKE vbfa-rfmng, " Delivery Qty..

vbtyp_n LIKE vbfa-vbtyp_n, " R12 Defect 8938.

mrnkz LIKE vbkd-mrnkz, " Subsequent Invoicing

docurr LIKE vbak-waerk, " Document Currency

netwr LIKE vbap-netwr, " Line Item Amt in Doc curr

ocdate LIKE vbak-aedat, " Order change date

objnr LIKE vbap-objnr, " Object number

rad LIKE sy-datum, " Requested Arrival Date

submi LIKE ekko-submi, " Collective number

plant1 LIKE vbap-werks, " Supplying Plant

ernam LIKE vbak-ernam, " Order Created by

erdat LIKE vbak-erdat, " Order Created on

lifnr LIKE ekko-lifnr, " Vendor

soldto LIKE vbak-kunnr, " Sold-to Party

shipto LIKE vbak-kunnr, " Ship-to Party

etenr LIKE vbep-etenr, " Sales Order schedule line

bmeng LIKE vbep-bmeng, " Order Sch Line quantity

vrkme LIKE vbap-zieme, " Sales Order UOM

cad LIKE sy-datum, " Confirmed Arrival Date

ktokk LIKE lfa1-ktokk, " Vendor account group

docurramt LIKE vbap-netwr, " Document Currency Amount

locurr LIKE vbak-waerk, " Local Currency

locurramt LIKE vbap-netwr, " Local Currency Amount

werks1 LIKE vbap-werks, " Supplying Plant

werks_name1 LIKE t001w-name1, " Plant description

lifnr_name1 LIKE lfa1-name1, " Name of the Vendor

stat TYPE g_stat_typ, " Status Code

lstatus TYPE g_stext_typ, " Last Status

soappr TYPE tj30t-txt30, " Sales Order Approval

bdnum LIKE vbfa-vbeln, " Billing doc number

bdlin LIKE vbrp-posnr, " Billing Item

uecha_b LIKE vbrp-uecha, " Item of Batch split

uecha_d LIKE lips-uecha, " Item of Batch split

END OF t_initord.

This is the internal table

to get the details wrote a select query

can any one please correct the select query

SELECT t1_{vbeln t2}posnr t1_{auart t4}bezei

t2_{matnr t5}maktx

t1_{bstnk t1}bukrs_vf t1_{vkorg t1}vtweg t1_{spart t1}vkgrp

t1_{augru t2}abgru t2_{kwmeng t8}vbeln

t8_{posnn t8}rfmng t8_{vbtyp_n t7}mrnkz t2_{waerk t2}netwr

t1_{aedat t1}objnr t1_{vdatu t1}submi t2_{werks t1}ernam t1~erdat

t6_{lifnr t1}kunnr t9~kunnr

INTO TABLE t_initord

FROM vbak AS t1

INNER JOIN vbap AS t2

ON t1_{vbeln EQ t2}vbeln

LEFT OUTER JOIN tvakt AS t4

ON t1_{auart EQ t4}auart

AND t4~spras EQ c_en

LEFT OUTER JOIN makt AS t5

ON t2_{matnr EQ t5}matnr

AND t5~spras EQ c_en

LEFT OUTER JOIN vbpa AS t9

ON t1_{vbeln EQ t9}vbeln

AND t9~parvw EQ c_we

LEFT OUTER JOIN ekko AS t6

ON t1_{submi EQ t6}ebeln

LEFT OUTER JOIN vbkd AS t7

ON t1_{vbeln EQ t7}vbeln AND

t7~posnr EQ c_initposnr

LEFT OUTER JOIN vbfa AS t8

ON t2_{vbeln EQ t8}vbelv AND

t2_{posnr EQ t8}posnv

WHERE t1~vbeln IN s_slord

AND t1~vkbur IN s_sloff

AND t1~vkgrp IN s_slgrp

AND t1~kunnr IN s_custag

AND t1~auart IN s_ortyp

AND t1~vkorg IN s_slorg

AND t1~vtweg IN s_disch

AND t1~spart IN s_divi

AND t1~erdat IN s_date

AND t1~bukrs_vf IN s_ccode

AND t2~ernam IN s_slcre.

i need to modify the above query to get the additional information into the above table

billing doument number

document currency

document currency amount

billed quantity

sold to country

ship to country

Re: select quer with inner join

former_member5350 — Tue, 15 May 2007 09:34:07 GMT

Hi Singhireddy,

this - had a first look - looks very complicated and should also bring some performance issues as you are joining 7 tables and as of your several WHERE conditions. could you please elaborate on your requirement ? if these are clear, we could try to find an easier - more efficient way perhaps.

Re: select quer with inner join

Former Member — Tue, 15 May 2007 09:44:13 GMT

Hi Raju,

You are suggested not to join more that 3 tables at a time.

Suggestion is that ... you can eclare the diff. strutes for all the tables(internal table) that you are taking ... also define a final internal table ... which will be containing all the fields from diff. internal table ..

populate the internal table for the table first using for all entries .. acoording to your condition...

and then modify the final internal table after all the internal table are populated independently...

Regards,

Jayant

Re: select quer with inner join

Former Member — Tue, 15 May 2007 09:47:24 GMT

You can also refer to this code... for making your code better..

SELECT MATNR

WERKS

DISMM

DISPO

BESKZ INTO TABLE I_MARC FROM MARC

WHERE WERKS IN S_WERKS AND BESKZ = 'F' AND DISMM <> 'ND'.

IF NOT I_MARC IS INITIAL.

SELECT MATNR

MAKTX INTO TABLE I_MAKT FROM MAKT

FOR ALL ENTRIES IN I_MARC

WHERE MATNR = I_MARC-MATNR.

ENDIF.

REFRESH I_MDPS.

REFRESH I_MDEZ.

REFRESH I_MT61D.

REFRESH I_MDPS2.

REFRESH I_MDEZ2.

LOOP AT I_MARC INTO WA_MARC.

CALL FUNCTION 'MD_STOCK_REQUIREMENTS_LIST_API'

EXPORTING

MATNR = WA_MARC-MATNR

WERKS = WA_MARC-WERKS

TABLES

MDEZX = I_MDEZ

EXCEPTIONS

MATERIAL_PLANT_NOT_FOUND = 1

PLANT_NOT_FOUND = 2

OTHERS = 3

LOOP AT I_MDEZ.

I_MDEZ2-MATNR = WA_MARC-MATNR.

I_MDEZ2-WERKS = WA_MARC-WERKS.

MOVE-CORRESPONDING I_MDEZ TO I_MDEZ2.

APPEND I_MDEZ2.

ENDLOOP.

CLEAR I_MDEZ.

CLEAR I_MDEZ2.

ENDLOOP.

Deleting teh records from the internal table which are
nither purchase order nor purchase requisition.

if pur_ord = 'X' and Pur_req = 'X'.

delete i_mdez2 where delkz <> 'BA' and delkz <> 'BE'.

endif.

if pur_ord = 'X' and Pur_req <> 'X'.

delete i_mdez2 where delkz <> 'BE'.

endif.

if pur_req = 'X' and pur_ord <> 'X'.

delete i_mdez2 where delkz <> 'BA'.

endif.

Populating the final internal table to be displayed.

Sort i_mdez2 by matnr werks.

SORT I_MAKT BY MATNR.

LOOP AT I_MDEZ2 WHERE LIFNR IN S_LIFNR.

wa_final-werks = i_mdez2-werks.

WA_FINAL-LIFNR = I_MDEZ2-LIFNR.

WA_FINAL-EXTRA = I_MDEZ2-EXTRA.

WA_FINAL-MNG01 = I_MDEZ2-MNG01.

WA_FINAL-AUSKT = I_MDEZ2-AUSKT.

WA_FINAL-UMDAT = I_MDEZ2-UMDAT.

WA_FINAL-WERKS = I_MDEZ2-WERKS.

WA_FINAL-MATNR = I_MDEZ2-MATNR.

wa_final-delkz = i_mdez2-delkz.

For Message to be displayed with the Purchase Order and Purchase Requisition.

PERFORM TEXT_EXCEPTION.

READ TABLE I_MAKT INTO WA_MAKT WITH KEY MATNR = WA_FINAL-MATNR BINARY SEARCH.

IF SY-SUBRC = 0.

WA_FINAL-MAKTX = WA_MAKT-MAKTX.

ENDIF.

APPEND WA_FINAL TO I_FINAL .

ENDLOOP.

IF SY-SUBRC <> 0.

MESSAGE I058.

leave program.

ENDIF.

Selecting the Purchase Organisation from the EKKO table.

if not i_final is initial.

SELECT EBELN EKORG LIFNR INTO TABLE I_EKKO FROM EKKO

FOR ALL ENTRIES IN I_FINAL

WHERE LIFNR = I_FINAL-LIFNR.

Selecting the name of the vendor from the LFA1 table.

select lifnr name1 into table i_lfa1 from lfa1

for all entries in i_final

where lifnr = i_final-lifnr.

Selecting the current date from the eban table.

select matnr lfdat into table i_eban from eban

for all entries in i_final

where matnr = i_final-matnr." and bnfpo = i_ekpo-bnfpo.

endif.

if not i_ekko is initial.

Selecting the Unit Price from EKPO for finding the Extended amount.

SELECT EBELN ebelp NETPR MATNR banfn bnfpo INTO TABLE I_EKPO FROM EKPO

FOR ALL ENTRIES IN I_EKKO

WHERE EBELN = I_EKKO-EBELN.

endif.

if not i_ekpo is initial.

Selecting the current date from the Eket table.

select ebeln ebelp eindt into table i_eket from eket

for all entries in i_ekpo

where ebeln = i_ekpo-ebeln and ebelp = i_ekpo-ebelp.

endif.

*Sorting the internal tables.

SORT I_EKKO BY LIFNR.

sort i_lfa1 by lifnr.

SORT I_EKPO BY MATNR.

sort i_eket by ebeln ebelp.

sort i_eban by matnr.

LOOP AT I_FINAL INTO WA_FINAL.

READ TABLE I_EKKO INTO WA_EKKO WITH KEY LIFNR = WA_FINAL-LIFNR.

WA_FINAL-EKORG = WA_EKKO-EKORG.

wa_final-ebeln = wa_ekko-ebeln.

read table i_lfa1 into wa_lfa1 with key lifnr = wa_final-lifnr.

wa_final-name1 = wa_lfa1-name1.

READ TABLE I_EKPO INTO WA_EKPO WITH KEY MATNR = WA_FINAL-MATNR.

wa_final-ebelp = wa_ekpo-ebelp.

WA_FINAL-NETPR = WA_EKPO-NETPR.

if wa_final-delkz = 'BA'.

read table i_eban into wa_eban with key matnr = wa_final-matnr.

wa_final-dat00 = wa_eban-lfdat.

endif.

if wa_final-delkz = 'BE'.

read table i_eket into wa_eket with key ebeln = wa_final-ebeln ebelp = wa_final-ebelp.

wa_final-dat00 = wa_eket-eindt.

endif.

WA_FINAL-EXTND_AMOUNT = WA_FINAL-MNG01 * WA_FINAL-NETPR.

MODIFY I_FINAL FROM WA_FINAL TRANSPORTING EKORG name1 dat00 EXTND_AMOUNT.

ENDLOOP.

In this I have taken many internal tables... according to the database table I am extracting from .. then I am bringing all the record in the final internal table which will be containing teh records from all the internal table that i have used..

Regards,

Jayant

Please award if helpful

Re: select quer with inner join

former_member5350 — Tue, 15 May 2007 09:52:40 GMT

hi,

you could make Jayant's great code even faster if you use:

SORT I_EKKO BY LIFNR.

READ TABLE I_EKKO INTO WA_EKKO WITH KEY LIFNR = WA_FINAL-LIFNR BINARY SEARCH.

WA_FINAL-EKORG = WA_EKKO-EKORG.

wa_final-ebeln = wa_ekko-ebeln.

read table i_lfa1 into wa_lfa1 with key lifnr = wa_final-lifnr.

wa_final-name1 = wa_lfa1-name1.

SORT I_EKPO BY MATNR.

READ TABLE I_EKPO INTO WA_EKPO WITH KEY MATNR = WA_FINAL-MATNR BINARY SEARCH.

wa_final-ebelp = wa_ekpo-ebelp.

WA_FINAL-NETPR = WA_EKPO-NETPR.

if wa_final-delkz = 'BA'.

read table i_eban into wa_eban with key matnr = wa_final-matnr BINARY SEARCH.

wa_final-dat00 = wa_eban-lfdat.

endif.

if wa_final-delkz = 'BE'.

read table i_eket into wa_eket with key ebeln = wa_final-ebeln ebelp = wa_final-ebelp BINARY SEARCH.

wa_final-dat00 = wa_eket-eindt.

endif.

For BINARY SEARCH, you need to SORT the tables you want to search before.

Reward if helpful,

Re: select quer with inner join

Former Member — Tue, 15 May 2007 10:00:39 GMT

Hey THANKS clemens,

for making my code more faster.... I forgot to mention binary search...

Thankyou very much,

Jayant