topic Re: Select max ( budat ) problem in Application Development and Automation Discussions

Select max ( budat ) problem

Former Member — Wed, 08 Apr 2009 12:32:35 GMT

Dear all,

i need from one DB table to select matnr and max budat.

I try query like this:

select max( budat ) MATNR

into CORRESPONDING FIELDS OF TABLE IT_ZMATDATE

from /BIC/B0000086000

where BWART = BWART

group by matnr.

I receive matnr but in budat all fields are 000000.

Do someone know how to get the latest date for one matherial ???

Thanks in advance.

Re: Select max ( budat ) problem

sarbajitm — Wed, 08 Apr 2009 12:34:41 GMT

please provide the structure of IT_ZMATDATE.

Re: Select max ( budat ) problem

soumya_jose3 — Wed, 08 Apr 2009 12:36:13 GMT

Hi,

Can you tell how you declare the internal table IT_ZMATDATE.

Regards,

Soumya.

Re: Select max ( budat ) problem

Former Member — Wed, 08 Apr 2009 12:38:28 GMT

in report: DATA: IT_ZMATDATE LIKE STANDARD TABLE OF ZMAT_DATE.

where ZMAT_DATE is our z table with 3 Fields: mandt matnr budat.

Re: Select max ( budat ) problem

sarbajitm — Wed, 08 Apr 2009 12:40:11 GMT

IS BUDAT's type SY_DATUM?

Re: Select max ( budat ) problem

Former Member — Wed, 08 Apr 2009 12:43:24 GMT

This is in BW system and type is /BIC/OW_BUDAT_BA which is Data Element and domain is type DATS 8

Re: Select max ( budat ) problem

sarbajitm — Wed, 08 Apr 2009 12:45:57 GMT

You have written:

select max( budat ) MATNR

into CORRESPONDING FIELDS OF TABLE IT_ZMATDATE

from /BIC/B0000086000

where BWART = BWART

group by matnr.

just replace it with this one:

select MATNR max( budat )

into CORRESPONDING FIELDS OF TABLE IT_ZMATDATE

from /BIC/B0000086000

where BWART = BWART

group by matnr.

Edited by: Sarbajit Majumdar on Apr 8, 2009 6:16 PM

Re: Select max ( budat ) problem

Former Member — Wed, 08 Apr 2009 12:51:17 GMT

The result is the same budat fields are 000000.The material fields are ok

Re: Select max ( budat ) problem

agnihotro_sinha2 — Wed, 08 Apr 2009 12:55:03 GMT

hi,

have you checked the /BIC/B0000086000 table for values of BUDAT for the material number MATNR?

also if BUDAT is of type DATS why is the result showing 000000?

usually this kind of select statement works perfectly.

Re: Select max ( budat ) problem

Former Member — Wed, 08 Apr 2009 13:00:58 GMT

Yes i check the table for matherial 000000000010018397 for example there is 218 records and there budat 08.01.2009,08.01.2009,09.01.2009 and so on...?!?

Re: Select max ( budat ) problem

sarbajitm — Wed, 08 Apr 2009 13:01:12 GMT

What I can suggest that you first create a type like the following one:

Type:begin of ty_ZMATDATE,

mandt type ZMAT_DATE-mandt,

matnr type ZMAT_DATE-matnr,

budat type sy-datum,

end of ty_ZMATDATE.

Data: IT_ZMATDATE LIKE STANDARD TABLE OF ty_ZMATDATE.

otherwise I'm not able to find any other type solution.

Regards.

Sarbajit.

Re: Select max ( budat ) problem

Former Member — Wed, 08 Apr 2009 13:14:10 GMT

HI,

try this,

select max( budat ) MATNR

into CORRESPONDING FIELDS OF TABLE IT_ZMATDATE

from /BIC/B0000086000

where BWART = BWART

Regards,

R K.

Re: Select max ( budat ) problem

Former Member — Wed, 08 Apr 2009 13:15:58 GMT

thanks Sarbajit,

but this also do not help.

Re: Select max ( budat ) problem

agnihotro_sinha2 — Wed, 08 Apr 2009 13:46:16 GMT

hi,

Since its taking long to solve your problem by experts in forum , i have got a temp solution for your problem, which is ofcourse not performnce recommended.

but for time being you can use it untill anyone else comes up with better solnt.

Declare IT_ZMATDATE as a work area.



select max( budat ) MATNR
into IT_ZMATDATE
from /BIC/B0000086000
where BWART = BWART
ENDSELECT.

Edited by: ags on Apr 8, 2009 3:46 PM