Select Subquery

[Sougata]

Hello everyone,

I have the following problem, can anyone help?

Data in HRP1000

Objid             Begda                 Endda
1234              01.01.1900          31.12.2006
1234              01.01.2007          31.12.2007
1234              01.01.2008          31.12.2010
5678              01.01.2007          30.06.2007
5678              01.07.2007          31.07.2007

Required in my internal table in 1 Select statement i.e. 
without manipulating data after Select statement

Objid             Begda                 Endda
1234              01.01.1900          31.12.2010
5678              01.01.2007          31.07.2007

In other words, in a single select statement, I want to select the DISTINCT entries from HRP1000 with MIN begda and MAX endda where multiple records exist without manipulating the data in a loop after the select statement.

<removed_by_moderator>

Cheers,

Sougata.

Edited by: Julius Bussche on Oct 29, 2008 10:44 AM

[GauthamV]

hi,

after selecting data into internal table, sort your internal table descending using pernr,begda,endda and then use this statement.

DELETE ADJACENT DUPLICATES FROM it_pa0001 COMPARING pernr.

[Former Member]

Hi,

First Select Distinct object id into one internal table.

then using For all entries select min and max separately into another internal table.

I dont think this can be done with a singe select statement as you are taking min and max values from different records.

Regards

Karthik D

[Former Member]

Hi

try this code....

sort itab1 by objid.
       loop at <itab1>.
         at new objid.
              <append into new itab2>.
         endat.
       endloop.

Arunima

[Former Member]

Hi Chatterji,

i think the data what you are getting is wrong, the enddate for the 3rd and 5th records will be 31.12.9999.

then you can write

In select statement write ENDDA =< SY-DATUM. and

BEGDA => SY-DATUM.

i.e the enddate should be equal or greter than present date.

if your data is correct then , it is difficult to get the required data in one single select statement.

Regards

kumar M.

[JozsefSzikszai]

I would do the following (pseudo code!):

Two internal tables will be necessary (both look the same)

SELECT ...
FROM hrp1000
INTO TABLE itab1
WHERE ...

LOOP AT itab1.
READ TABLE itab2 WITH KEY objid = itab1-objid. "Check if entry is already in itab2
IF sy-subrc EQ 0. "If yes
IF itab1-begda LT itab2-begda. "Check for lower begda
itab2-begda = itab1-begda.
ENDIF
IF itab1-ennda GT itab2-endda. "Check for higher ennda
itab2-endda = itab1-endda.
ENDIF.
MODIFY itab2.
ELSE. "If not ==> insert
INSERT itab1 INTO itab2.
ENDIF.
ENDLOOP.

[Sougata]

Guys,

Thanks for trying to help but can I ask you not to suggest anything else

other than what I've asked in the first place i.e. the logic within one SELECT

statement. Its easy to get what I require with the help of 2 internal tables via

table manipulation after selection of data from database etc. but I don't want

to do that....because this is part of a extractor logic for a BW generic DataSource

to load a particular InfoObject - as a result run-time is the most important criteria

that one has to bear in mind. I cannot possibly use multiple internal tables then

use LOOP, READ, SORT, DELETE etc....that's not an option here. I could have

used PROVIDE statement to get what I want but that is also expensive in

regards to performance.....so even that is not an option!

I was thinking in the lines of a subquery within a SELECT statement....but

couldn't get the results I'm looking for, so I was wondering if anyone has done

anything similar before?

Data: lt_objid type standard table of zhr_objid_range.   "structure is objid, begda, endda

Select distinct objid begda endda from hrp1000
into corresponding fields of table lt_objid
where otype = 'S'
and    objid  in ra_objid     "the data range requested from BW
and    plvar   = '01'
and    istat   = '1'
and    begda = ( select min( begda ) from hrp1000  "not sure about this part...
                        where otype = 'S'
                        and    objid  in ra_objid
                        and    plvar = '01'
                        and    istat = '1'
                        and    begda = ??   "not sure about the subquery here
                        and    endda = ?? ) "not sure about the subquery here
and    endda = ( select max( endda ) from hrp1000 "not sure about this part....
                        where otype = 'S'
                        and    objid  in ra_objid
                        and    plvar = '01'
                        and    istat = '1'
                        and    begda = ??     "not sure about the subquery here
                        and    endda = ?? ).  "not sure about the subquery here

Cheers,

Sougata.

Edited by: Sougata Chatterjee on Oct 29, 2008 7:46 PM

[JozsefSzikszai]

performance wise my advice is the best (I am 99.99% sure...), if you really want to go for subquery, pls. have a look at the following sample:

DATA : lt_objid TYPE TABLE OF hrp1000.
DATA : lw_objid TYPE hrp1000.

SELECT objid MIN( begda ) FROM hrp1000
INTO CORRESPONDING FIELDS OF lw_objid
WHERE otype = 'S'
AND    objid  IN ra_objid     "the data range requested from BW
AND    plvar   = '01'
AND    istat   = '1'
GROUP BY objid.
  SELECT MAX( endda )
  INTO lw_objid-endda
  FROM hrp1000
  WHERE objid EQ lw_objid-objid.
  APPEND lw_objid TO lt_objid.
ENDSELECT.