09-05-2007 10:14 AM
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
amblnr = bmblnr
into corresponding fields of table itab1
for all entries in itab
where a~kdauf = itab-kdauf
and a~kdpos = itab-kdpos and
abwart = '601' or abwart = '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 mkpfmblnr EQ msegmblnr
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
09-05-2007 10:18 AM
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 msegmblnr EQ mkpfmblnr
into table itab
where mkpf~budat in zdate and
mseg~werks in zpl and
( mseg~BWART = '601' or
mseg~bwart = '602').
<b>If itab[] is not intial.</b>
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
amblnr = bmblnr
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
<b>( abwart = '601' or abwart = '602')</b>
and a~WERKS = itab-werks
and b~budat le itab-budat.
<b>endif.</b>
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
09-05-2007 10:16 AM
first chk db tables matches data found.
after chk length of two fields example numeric or number type.
09-05-2007 10:17 AM
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
09-05-2007 10:18 AM
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 msegmblnr EQ mkpfmblnr
into table itab
where mkpf~budat in zdate and
mseg~werks in zpl and
( mseg~BWART = '601' or
mseg~bwart = '602').
<b>If itab[] is not intial.</b>
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
amblnr = bmblnr
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
<b>( abwart = '601' or abwart = '602')</b>
and a~WERKS = itab-werks
and b~budat le itab-budat.
<b>endif.</b>
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
09-05-2007 10:18 AM
hi Raja,
you have to change the WHERE part like this:
where a~kdauf = itab-kdauf
and a~kdpos = itab-kdpos and
<b>(</b> abwart = '601' or abwart = '602' <b>)</b> and
a~WERKS = itab-werks and
b~budat le itab-budat.
ec