topic Re: Select Statement in Application Development and Automation Discussions

Select Statement

Former Member — Tue, 13 Jun 2006 10:27:40 GMT

Dear Friends,

From the MKPF table, I have taken the uniquie document number into Itab-1. In Itab-2 I am selecting all the entries from MSEG.

The code used is as follows:

SELECT MBLNR BUDAT

FROM MKPF

INTO CORRESPONDING FIELDS OF TABLE IT_MKPF

WHERE BUDAT LE P_LDATE.

SORT IT_MKPF BY MBLNR BUDAT DESCENDING.

DELETE ADJACENT DUPLICATES FROM IT_MKPF COMPARING

MBLNR.

SELECT B_{MBLNR B}MATNR B_{BWART B}BUKRS B_{WERKS B}LGORT

B_{SHKZG B}MENGE B_{XAUTO B}SOBKZ

FROM MSEG AS B

INNER JOIN MARA AS C

ON B_{MATNR = C}MATNR

INTO TABLE IT_STOCK1 FOR ALL ENTRIES IN IT_MKPF

WHERE B~MBLNR EQ IT_MKPF-MBLNR AND

C~SPART IN ('CT', 'AP', 'AU', 'AC' , 'PJ')

AND B~MATNR IN S_MATNR.

The no. range used for last year and this year is same, and hence I am deleting the duplicates from the MKPF itab.

The problem I have observed is that, there are two genuine records available in MSEG for a single MKPF document no. But, in the Itab-2, it is selecting only one record from MSEG, istead of two records.

Pls guide me the solution.

Thanks in advace,

MARK K

Re: Select Statement

Former Member — Tue, 13 Jun 2006 10:30:47 GMT

as u r using FOR ALL ENTRIES it will take only one doc number

Re: Select Statement

Former Member — Tue, 13 Jun 2006 10:31:41 GMT

How to get all the records ?

Regards,

Re: Select Statement

Former Member — Tue, 13 Jun 2006 10:34:57 GMT

All the fields that u r selecting from MSEG and MARA should be same for those two genuine records. That's the reason why only one set of fields are extracted.

Adding primary fields of MSEG and MARA in the selecting part should solve ur problem

Re: Select Statement

Former Member — Tue, 13 Jun 2006 10:35:04 GMT

oh sorry!

u r using DELETING ADJACENT DUPLICATES COMPARING MBLNR.

this will remove duplicate documents , either specify some more fields in the above statement like

DELETING ADJACENT DUPLICATES COMPARING MBLNR BUDAT .. or remove this statement

Re: Select Statement

Former Member — Tue, 13 Jun 2006 10:36:07 GMT

Hi,

are you sure those two records qualify the where condition, i feel you got one record because of where condition (only one record satisfied the where condition). can you check it once again

Regards

vijay

Re: Select Statement

Former Member — Tue, 13 Jun 2006 10:36:43 GMT

after deleting duplicates i think there is only one value for MBLNR.

when u used for all entries in another select it compare only one time with MBLNR. so it takes only one value.

avoid to used delete adjucent comparing MBLNR.

for number range problem just use MJAHR field for current year u take it 2006 or sy-datum+0(4)

SELECT MBLNR BUDAT

FROM MKPF

INTO CORRESPONDING FIELDS OF TABLE IT_MKPF

WHERE BUDAT LE P_LDATE

and MJAHR = sy-datum+0(4) .

Re: Select Statement

Former Member — Tue, 13 Jun 2006 10:46:06 GMT

Though i use DELETING ADJACENT DUPLICATES COMPARING MBLNR, there is only record in MKPF table (even without deleting) but for the same document no., there are two records in MSEG.

Regards,

Re: Select Statement

Former Member — Tue, 13 Jun 2006 10:49:48 GMT

All the fields that u r selecting from MSEG and MARA should be same for those two genuine records. That's the reason why only one set of fields are extracted.

Adding primary fields of MSEG and MARA in the selection(not extraction) part should solve ur problem

Re: Select Statement

Former Member — Tue, 13 Jun 2006 10:51:09 GMT

Hello mark,

You are getting only one entry as you are using 'FOR ALL ENTRIES' statement.

The FOR ALL ENTRIES checks for similar records and considers only one. Refer to Help on 'WHERE' condition - 'for all entries'

You will need to loop through the table to be able to select all records.

Re: Select Statement

Former Member — Tue, 13 Jun 2006 10:51:59 GMT

in the select from MSEG you added AND B~MATNR IN S_MATNR , check this S_MATNR have 2 material numbers or not.

i believe, your S_MATNR is missing out 1 ,so your result also getting based on that.

regards

srikanth

Re: Select Statement

Former Member — Tue, 13 Jun 2006 11:20:58 GMT

HI Mark,

When U use FOR ALL ENTRIES at that time you should select all the key fields from the table. In your select query you are not selecting fields MJAHR and ZEILE. Try to get these fields in the internal table.

I think this must solve your problem.

Regards,

Atish