Question on Select query

[Former Member]

Hi,

I need to prepare a select query which can fetch the below mentioned scenario:

Ztable has fields zident, date, time, status. (zident, date, time are key fields)

zident date time status

10001 02/09/09 111111 s

10001 02/08/09 222222 p

10001 02/07/09 333333 s from this set only one ident should come which

10001 02/01/09 333333 s has latest status

10001 02/02/09 333333 s

10001 02/03/09 333333 s

10002 02/09/09 111111 s

10002 02/08/09 222222 p

10002 02/07/09 333333 s from this set only one ident should come which

10002 02/01/09 333333 s has latest status

10002 02/02/09 333333 s

10002 02/03/09 333333 s

and so on.............

from one set of ident one record should come in this way so many set of idents will be there.

My question is how to form the select query

[Former Member]

Hello Chandra,

Your solution lies in the concept of Control-Break Statements.

AT NEW..

AT END OF

AT LAST, etc.

DATA:
  BEGIN OF fs_table,
    zident TYPE char20,
    date   TYPE sy-datum,
    time  TYPE sy-uzeit,
    status TYPE C,
  END OF fs_table.

DATA:

t_table LIKE

STANDARD TABLE

of fs_table.

" Populate the data in Internal Table.

" While displaying data.

LOOP AT t_table INTO fs_table.
  AT NEW status.
     WRITE:/ fs_table-status.
  ENDAT.
     WRITE:/ fs_table-zident,
                  fs_table-date,
                  fs_table-time.
  ENDLOOP.

You can also use ON CHANGE OF fs_table-status, instead of AT NEW, if you get ***//* for date and time.

With this, status will be displayed once and remaining will following under it.

If you want everything to be displayed on change of each new field, apply that to every field,

eg:

AT NEW zident.

WRITE:/ fs_table-zident.

ENDAT.

AT NEW date.

WRITE: fs_table-date.

ENDAT.

.. so on and so forth.

This is just a test-case, try using the control break statements.

Hope it helps you.

Thanks: Zahack

[Former Member]

Hi,

After retrieving data from db tab to int tab say it_tab,

SORT it_tab DESCENDING BY zident date. "Now for each zident latest date will be the first entry
  DELETE ADJACENT DUPLICATES FROM it_tab COMPARING zident. "Now only status with latest 
                                          "date remains and others deleted

Hope this helps you.

Regards,

Manoj Kumar P

Edited by: Manoj Kumar on Feb 11, 2009 3:55 PM

[Former Member]

SQL is your friend. Let the database do the work.

select 
zident date time status
from ztable as
where z~date = (select max(zz~date)
                from ztable as zz
                where zz~zident = z~zident)
and z~time = ( select max(zz~time)
                from ztable as zz
                where zz~zident = z~zident
                and zz~date = z~date)

[Former Member]

Hi Jerry,

I think nested SQL queries may be costly to use.

Does it not effect the performance.

Please guide.

Thanks: Zahack

[Former Member]

First of all, this is not a nested query, it is a "correlated subquery". Secondly, according to the OP zident, date, time are key fields, and the subquery will use the index. Thirdly, reducing IO and network traffic is almost always a good thing.

[Former Member]

Thanks Jerry,

I didn't know about it.

Thanks for guidance.

Zahack