topic Re: query in Application Development and Automation Discussions

query

Former Member — Wed, 05 Sep 2007 09:14:30 GMT

Hi experts

i have the following query, it contains faulty data, it returns all kdaufs which is not match with itab-kdauf, can u tell me whats the error in this query. can we check bwart field as follows.

select a~mblnr

a~kdauf

a~kdpos

a~matnr

a~werks

a~erfmg

a~erfme

a~BWART

b~budat

from mseg as a

inner join mkpf as b on

a_{mblnr = b}mblnr

into corresponding fields of table itab1

for all entries in itab

where a~kdauf = itab-kdauf

and a~kdpos = itab-kdpos and

a_{bwart = '601' or a}bwart = '602' and

a~WERKS = itab-werks and

b~budat le itab-budat.

itab as follows**

SELECT mseg~mblnr

mkpf~budat

mseg~kdauf

mseg~kdpos

mseg~matnr

mseg~werks

mseg~erfmg

mseg~erfme

mseg~BWART

from mseg

inner join

mkpf on mkpf_{mblnr EQ mseg}mblnr

into table itab

where mkpf~budat in zdate and

mseg~werks in zpl and

( mseg~BWART = '601' or

mseg~bwart = '602').

Thanks in advance.

Regards

Rajaram

Re: query

Former Member — Wed, 05 Sep 2007 09:16:52 GMT

first chk db tables matches data found.

after chk length of two fields example numeric or number type.

Re: query

gopi_narendra — Wed, 05 Sep 2007 09:17:01 GMT

Few things to be taken care

1. The order of fields in your query are not according to the standard table fields order

2. Avoid using into corresponding fields.

3. Sort itab by mblnr.

4. Use if not itab[] is initial. before starting the query.

5. First use MKPF and then join on MSEG ( you did the reverse of it) since MKPF is header and MSEG is the item table.

And why are you querying 2 times on the same tables instead you can do it in one shot.

Regards

Gopi

Re: query

Former Member — Wed, 05 Sep 2007 09:18:25 GMT

HI,

First write

SELECT mseg~mblnr

mseg~matnr

mseg~werks

mseg~kdauf

mseg~kdpos

mseg~erfmg

mseg~erfme

mseg~BWART

mkpf~budat

from mseg

inner join

mkpf on mseg_{mblnr EQ mkpf}mblnr

into table itab

where mkpf~budat in zdate and

mseg~werks in zpl and

( mseg~BWART = '601' or

mseg~bwart = '602').

If itab[] is not intial.

select a~mblnr

a~BWART

a~matnr

a~werks

a~kdauf

a~kdpos

a~erfmg

a~erfme

b~budat

from mseg as a

inner join mkpf as b on

a_{mblnr = b}mblnr

into table itab1 "define ITAb1 with the fields selected

"above in the same order

for all entries in itab

where a~kdauf = itab-kdauf

and a~kdpos = itab-kdpos and

( a_{bwart = '601' or a}bwart = '602')

and a~WERKS = itab-werks

and b~budat le itab-budat.

endif.

All the fields are selected in order..

You need to check if ITAB is not initial.. If ITAb is initial you will get all the values from the table..

rewards if useful,

regards,

nazeer

Re: query

JozsefSzikszai — Wed, 05 Sep 2007 09:18:48 GMT

hi Raja,

you have to change the WHERE part like this:

where a~kdauf = itab-kdauf

and a~kdpos = itab-kdpos and

( a_{bwart = '601' or a}bwart = '602' ) and

a~WERKS = itab-werks and

b~budat le itab-budat.