Skip to Content
0
Former Member
May 12, 2007 at 02:39 PM

select quer with inner join

35 Views

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 t1vbeln t2posnr t1auart t4bezei

t2matnr t5maktx

t1bstnk t1bukrs_vf t1vkorg t1vtweg t1spart t1vkgrp

t1augru t2abgru t2kwmeng t8vbeln

t8posnn t8rfmng t8vbtyp_n t7mrnkz t2waerk t2netwr

t1aedat t1objnr t1vdatu t1submi t2werks t1ernam t1~erdat

t6lifnr t1kunnr t9~kunnr

INTO TABLE t_initord

FROM vbak AS t1

INNER JOIN vbap AS t2

ON t1vbeln EQ t2vbeln

LEFT OUTER JOIN tvakt AS t4

ON t1auart EQ t4auart

AND t4~spras EQ c_en

LEFT OUTER JOIN makt AS t5

ON t2matnr EQ t5matnr

AND t5~spras EQ c_en

LEFT OUTER JOIN vbpa AS t9

ON t1vbeln EQ t9vbeln

AND t9~parvw EQ c_we

LEFT OUTER JOIN ekko AS t6

ON t1submi EQ t6ebeln

LEFT OUTER JOIN vbkd AS t7

ON t1vbeln EQ t7vbeln AND

t7~posnr EQ c_initposnr

LEFT OUTER JOIN vbfa AS t8

ON t2vbeln EQ t8vbelv AND

t2posnr EQ t8posnv

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