Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

getting next maximum values from database table?

Former Member
0 Kudos

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

1 ACCEPTED SOLUTION

former_member209120
Active Contributor
0 Kudos

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).
                                   

11 REPLIES 11

FredericGirod
Active Contributor
0 Kudos

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

ronaldo_aparecido
Contributor
0 Kudos

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
2 >. ...... ENDAT.
        .......
          <single line processing>
.......
     
AT END OF <f2>. ... ENDAT.
   
AT END OF <f1>. ... ENDAT.
  AT LAST. .... ENDAT.
ENDLOOP.

The innerm

former_member182379
Contributor
0 Kudos

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

GirieshM
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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...

former_member182915
Active Contributor
0 Kudos

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?

ThomasZloch
Active Contributor
0 Kudos

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

0 Kudos

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

Former Member
0 Kudos

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.

BR

Robert

former_member209120
Active Contributor
0 Kudos

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).
                                   

custodio_deoliveira
Active Contributor
0 Kudos

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.