topic Re: Problem in select query in Application Development and Automation Discussions

Problem in select query

Former Member — Sat, 12 May 2007 09:32:57 GMT

Hi, Any one can correct this select query

it is urgent

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

t11_{vbeln 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 t11_{waerk t11}netwr

t10_{fkimg t2}werks t1_{ernam t1}erdat

t6_{lifnr t1}kunnr t9_{kunnr t13}land1 t14~land1

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

inner join vbrp AS t10

ON t8_{vbeln EQ t10}vbeln

inner JOIN vbrk AS t11

ON t10_{vbeln EQ t11}vbeln

inner JOIN kna1 AS t13

ON t1_{kunnr EQ t13}kunnr

inner JOIN kna1 AS t14

ON t9_{kunnr EQ t14}kunnr

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.

AND t8~vbtyp_n IN (c_j,c_t).

Thanking you in advance

raju

Re: Problem in select query

Former Member — Sat, 12 May 2007 09:41:18 GMT

You are fetching so many VBELN fields from the tables?

it will mess up data in the internal table.

give different name in the internal table.

WHAT'S YOUR REQUIREMENT? SOMETHING WRONG I FEEL, NO NEED TO JOIN SO MANY TABLES TOGETHER, SPLIT THEM .

Don't join so many tables together

If a single table doesn't have data or not joined properly the data will not be fetched correctly

so join the tables VBAK, VBAP, VBKD, VBPA, VBRK TOGETHER

get the descriptions like AUART, MATNR, KUNNR with a select single from the respective table.

mention clearly what fields you need inthe output.

Reward points if useful

Regards

Anji

Re: Problem in select query

Former Member — Sat, 12 May 2007 09:53:19 GMT

Hi,

See when we use joins in this type of query will try to minimize the readablity of your program you try with multiple query stmts..

I cant judge what field u r retrieving and comparing..

Re: Problem in select query

Former Member — Sat, 12 May 2007 11:39:54 GMT

Hi,

Using Inner and Outer joins may not fetch u the desired results if not coded properly. And in ur case , u are joining too many tables. One of the best alternative is to use the For All Entries. U may have to use multiple queries to fetch the data, but atleast u can be assured of a more perfect data fetching.

Re: Problem in select query

Former Member — Sat, 12 May 2007 13:49:48 GMT

table

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

can any help me in this regard

thanking in advance