topic Re: getting next maximum values from database table? in Application Development and Automation Discussions

getting next maximum values from database table?

Former Member — Wed, 28 Aug 2013 08:24:18 GMT

Dear Experts,

Internal table date will be like this

Material No. Date Quantity

10001010 28.08.2013 10

10001810 28.08.2012 12

based on this internal table data i need to select next maximum date based on material no from mseg

example:

if data base like this

Material No. Date Quantity

10001010 28.08.2013 10

10001010 20.07.2013 5

10001010 10.04.2013 2

10001810 28.08.2012 12

10001810 21.08.2012 1
10001810 2.07.2012 21

i want data like this

Material No. Date Quantity

10001010 20.07.2013 5

10001810 21.08.2012 1

How we can get this values using select statement

Re: getting next maximum values from database table?

FredericGirod — Wed, 28 Aug 2013 14:09:15 GMT

Hi,

first extract your data, and in second step find the good one.

The big loose of time in ABAP is the SELECT statement, so always perform simple and faster SELECT statement.

After you could make a SORT and a READ on the internal table.

MSEG is one of the biggest table of SAP, don't try to make SORT, MAX, GROUP BY ... on it

regards

Fred

Re: getting next maximum values from database table?

ronaldo_aparecido — Wed, 28 Aug 2013 14:33:08 GMT

Hello

Select everything in the correct filters, then use the loop on the internal table, but first sort the data by date and material.

Inside the loop you can use logic to store the date and the material into variables and compare with the next record in loop to check the maximum date.

Please read about Sort:

http://help.sap.com/saphelp_470/helpdata/pt/fc/eb3800358411d1829f0000e829fbfe/content.htm

And read about at new in loop.

http://help.sap.com/saphelp_470/helpdata//EN/fc/eb381a358411d1829f0000e829fbfe/content.htm

LOOP AT <itab>.
AT FIRST. ... ENDAT.
    AT NEW <f1>. ...... ENDAT.
      AT NEW <f₂ >. ...... ENDAT.
        .......
          <single line processing>
.......
      AT END OF <f2>. ... ENDAT.
    AT END OF <f1>. ... ENDAT.
AT LAST. .... ENDAT.
ENDLOOP.

The innerm

Re: getting next maximum values from database table?

former_member182379 — Wed, 28 Aug 2013 14:34:09 GMT

Hi,

Try with below method

   REPORT ZGET_SECOND_VALUE.

TYPES : BEGIN OF TP_MSEG,
             MATNR   TYPE MATNR,
             DATE    TYPE SY-DATUM,
             QTY     TYPE MENGE_D,
          END OF TP_MSEG,

          BEGIN OF TP_MAKT,
            MATNR   TYPE MATNR,
          END OF TP_MAKT.

DATA : GT_MSEG        TYPE STANDARD TABLE OF TP_MSEG,
          GS_MSEG        TYPE TP_MSEG,
          GT_MSEG1       TYPE STANDARD TABLE OF TP_MSEG,
          GS_MSEG1       TYPE TP_MSEG,
          GT_MAKT        TYPE STANDARD TABLE OF TP_MAKT,
          GS_MAKT        TYPE TP_MAKT.

DATA : GV_CNT   TYPE I.

" select data from mseg into gt_mseg .

" select matnr from makt for all entries in gt_mseg

LOOP AT GT_MAKT INTO GS_MAKT.

       CLEAR : GV_CNT.

LOOP AT GT_MSEG INTO GS_MSEG WHERE MATNR = GS_MSEG-MATNR.
       GV_CNT = GV_CNT + 1.

       IF GV_CNT = 3.
          EXIT.
        ELSE.
          APPEND GS_MSEG TO GT_MSEG1.
       ENDIF.

       CLEAR : GS_MSEG, GS_MSEG1.
ENDLOOP.

      CLEAR : GS_MAKT.
ENDLOOP.

SORT GT_MSEG1 BY MATNR ASCENDING DATE DESCENDING.
DELETE ADJACENT DUPLICATES FROM GT_MSEG1 COMPARING MATNR.

Regards,

Zafar

Re: getting next maximum values from database table?

GirieshM — Thu, 29 Aug 2013 02:53:14 GMT

Hi Ganesh,

I am gonna use 3 internal tables for your program with same structure.

select statement and values into it_table1.

it_table1 have the value (sorted by material Number ascending and date descending):

Material No. Date Quantity

10001010 28.08.2013 10

10001010 20.07.2013 5

10001010 10.04.2013 2

10001810 28.08.2012 12

10001810 21.08.2012 1
10001810 2.07.2012 21

it_table2[] = it_table1[].

In this case it_table2 has same as it_table1.

delete adjacent duplicates from it_table2 comparing material number.

IT_TABLE2:

Material No. Date Quantity

10001010 28.08.2013 10
10001810 28.08.2012 12

Loop at it_table2 into wa_table2.

read it_table1 into wa_table1 with key material number eq material number date ne wa_table2-date.

if sy-subrc eq 0.

append wa_table1 to it_table3.

endif.

ENdloop.

Free: it_table2.

IT_TABLE3:

Material No. Date Quantity

10001010 20.07.2013 5
10001810 21.08.2012 1

So you have it_table1 with all values and it_table3 with second values. if you need you can use it_table2 for 1st values in your program for future validations.

Note: if there is no second record for a material number then it will be skipped.

With Regards,

Giriesh M

Re: getting next maximum values from database table?

Former Member — Thu, 29 Aug 2013 04:03:46 GMT

Hi Giriesh,

My requirement not to get next maximum number from internal table.

I need to get next maximum number form database table from mseg...

Re: getting next maximum values from database table?

SwadhinGhatuary — Thu, 29 Aug 2013 04:45:33 GMT

Hello Ganesh .

plaese mention what is the condition you want to find the data in internal table.

Material No. Date Quantity

10001010 28.08.2013 10

10001010 20.07.2013 5

10001010 10.04.2013 2

10001810 28.08.2012 12

10001810 21.08.2012 1
10001810 2.07.2012 21

i want data like this

Material No. Date Quantity

10001010 20.07.2013 5

10001810 21.08.2012 1

from 10001810 material you want

10001810 21.08.2012 1
from 10001010 material you want
10001010 20.07.2013 5

even though other post but unable get the condition where as
10001010 material qty 2 is their why you want 5?

Re: getting next maximum values from database table?

ThomasZloch — Thu, 29 Aug 2013 07:05:58 GMT

I have a feeling that this can be done using subquery and aggregate functions.

What "Date" field in MSEG are you referring to? Most relevant date fields are in header table MKPF.

Thomas

Re: getting next maximum values from database table?

Former Member — Thu, 29 Aug 2013 07:38:54 GMT

HI,

i would select only the entries up to a specific date. I think for you it should be sy-datum.

Then you can easily sort by date matnr.

Robert

Re: getting next maximum values from database table?

RaymondGiuseppi — Thu, 29 Aug 2013 08:14:16 GMT

You are right sub-queries can do the job (But Beware on performance)

Sample:

REPORT z_nextrecord.
 
 TYPES :
 BEGIN OF l_mseg,
   matnr TYPE mseg-matnr,
   cpudt TYPE mseg-cpudt_mkpf,
   menge TYPE mseg-menge,
 END OF l_mseg.
 
 DATA: gt_keys TYPE STANDARD TABLE OF l_mseg,
       gt_mseg TYPE STANDARD TABLE OF l_mseg.
 
 
 SELECT matnr cpudt_mkpf menge
   INTO TABLE gt_keys
   FROM mseg UP TO 10 ROWS.
 SORT gt_keys BY matnr.
 DELETE ADJACENT DUPLICATES FROM gt_keys COMPARING matnr.
 
 CHECK gt_keys[] IS NOT INITIAL.
 
 SELECT matnr cpudt_mkpf menge
   INTO TABLE gt_mseg
   FROM mseg
   FOR ALL ENTRIES IN gt_keys
   WHERE matnr = gt_keys-matnr
     AND cpudt_mkpf = ( SELECT MIN( cpudt_mkpf ) FROM mseg
                        WHERE matnr = gt_keys-matnr AND cpudt_mkpf GT gt_keys-cpudt ) .
 
 SORT gt_mseg BY matnr.
 
 BREAK-POINT.

NB: I used fields from MKPF copied into MSEG as of Note 1516684 - MKPF fields added to MSEG - Performance optimization, else JOIN between MSEG and MKPF would be required... (Fields CPUDT and BUDAT were copied into MSEG)

Regards,

Raymond

Re: getting next maximum values from database table?

former_member209120 — Mon, 30 Sep 2013 02:59:22 GMT

Hi Ganesh,

You try like this

types : begin of ty_mkpf_mseg,                " MKPF - Header: Material Document & MSEG Document Segment: Material
         mblnr type mkpf-mblnr,               " Material doc. no
         mjahr type mkpf-mjahr,               " Material Document Year
         budat type mkpf-budat,               " Posting Date in the Document
         cpudt type mkpf-cpudt,               " Day On Which Accounting Document Was Entered
         zeile type mseg-zeile,               " Mat. Doc. Item no.
         bwart type mseg-bwart,               " Movement type
         matnr type mseg-matnr,               " Material No.
         werks type mseg-werks,               " Plant
         shkzg type mseg-shkzg,               " Amount Indicator
         dmbtr type mseg-dmbtr,               " Amount
         menge type mseg-menge,               " Quantity
         meins type mseg-meins,               " Unit of measure
         aufnr type mseg-aufnr,               " Order No.
         kdauf type mseg-kdauf,               " Sales Order Number
         end of ty_mkpf_mseg,

Data : it_mkpf type table of ty_mkpf_mseg,
it_mkpf_pp type table of ty_mkpf_mseg,

selection-screen : begin of block b1 with frame title text-001.
parameters : p_date type sy-datum obligatory.
selection-screen : end of block b1.

select a~mblnr
          a~mjahr
          a~budat
          a~cpudt
          b~zeile
          b~bwart
          b~matnr
          b~werks
          b~shkzg
          b~dmbtr
          b~menge
          b~meins
          b~aufnr
          b~kdauf
          from mkpf as a inner join mseg as b
              on a~mblnr eq b~mblnr and a~mjahr eq b~mjahr
              into table it_mkpf
              where a~cpudt = p_date.

select a1~mblnr
              a1~mjahr
              a1~budat
              a1~cpudt
              b1~zeile
              b1~bwart
              b1~matnr
              b1~werks
              b1~shkzg
              b1~dmbtr
              b1~menge
              b1~meins
              b1~aufnr
              b1~kdauf
              from mkpf as a1 inner join mseg as b1
              on a1~mblnr eq b1~mblnr and a1~mjahr eq b1~mjahr
              into table it_mkpf_pp
              for all entries in it_mkpf
              where matnr = it_mkpf-matnr
              and   a1~cpudt = ( select max( c~cpudt )
                                      from mkpf as c inner join mseg as d
                                      on c~mblnr eq d~mblnr and c~mjahr eq d~mjahr
                                      where d~matnr = it_mkpf-matnr
                                      and c~cpudt lt it_mkpf-cpudt).

Re: getting next maximum values from database table?

custodio_deoliveira — Mon, 30 Sep 2013 06:15:22 GMT

I thought I was the only one thinking subquery to solve it. Raymond's solution looks good while Ramesh's seems quite odd with join, FAE and subquery in the same statement.