Select Statement

[Former Member]

Hi All,

I have one requirement, there i need to fetch the data for the following fields.

PA0001-BTRTL, PA2001-PERNR, PA2001-BEGDA, PA2001-ENDDA, PA2001-AWART, PA2001-BEGUZ, PA2001--ENDZU, PA2001-STDAT.

here the PERNR should be in PA0000 infotype.

here, personal subarea BTRTL is equal to DE01 then the Absence types 0200, 0201 and 0350 only be displayed.

& if the personal subare BTRTL is equal to AT22 then all Absence types should be displayed.

I need all the records for BTRTL is equal to DE01 and BTRTL is equal to AT22. I mean, three for DE01 and all for AT22.

please suggest how to write the select statement to achieve the requirement.

Regards,

Nagarjuna.

[dev_parbutteea]

Hi,

TYPES : BEGIN OF t_p,

pernr TYPE pa0001-pernr,

btrtl TYPE pa0001-btrtl,

BEGDA TYPE PA2001-BEGDA,

ENDDA TYPE PA2001-ENDDA,

AWART TYPE PA2001-AWART,

bEGUZ TYPE pA2001-BEGUZ,

ENDZU TYPE PA2001-ENDUZ,

STDAT TYPE PA2001-STDAT,

END OF t_p.

data: i_tab TYPE STANDARD TABLE OF t_p.

select pa0001_{pernr pa0001}btrtl PA2001_{BEGDA PA2001}ENDDA PA2001_{AWART PA2001}BEGUZ PA2001_{ENDUZ PA2001}STDAT

into table i_tab

from pa0001 inner join pa0000

on pa0001_{pernr = pa0000}pernr

inner join pa2001

on pa0001_{pernr = pa2001}pernr

where ( pa0001_{brtrl = 'DE01' and PA2001}AWART in (0200, 0201, 0350) )

or (pa0001~brtrl = 'AT22' ).

Else you can also use for all entries.

Regards,

Dev.

Edited by: Dev Parbutteea on Dec 26, 2008 12:50 PM

Edited by: Dev Parbutteea on Dec 26, 2008 12:55 PM

[Former Member]

You can use Inner join in the select statement like,

select PA0001~PERNR

PA0001~BRTRL

PA2001~BEGDA

PA2001~ENDDA

PA2001~AWART

PA2001~BEGUZ

PA2001~ENDUZ

PA2001~STDAT

into table i_tab

from PA0001 inner Join PA0000

on PA0001_{PERNR = PA0000}PERNR

inner join PA2001

on PA0001_{PERNR = PA2001}PERNR

where ( PA0001_{BRTRL = 'DE01' and PA2001}AWART in (0200, 0201, 0350) )

or (PA0001~BRTRL = 'AT22' ).

Regards,

Joan

[I355602]

Hi Nagarjuna,

Try this code,

TYPES : BEGIN OF t_pa0000,
          pernr TYPE pa0000-pernr,
        END OF t_pa0000,

        BEGIN OF t_final,
          pernr TYPE pa0001-pernr,
          btrtl TYPE pa0001-btrtl,
          begda TYPE pa2001-begda,
          endda TYPE pa2001-endda,
          awart TYPE pa2001-awart,
          beguz TYPE pa2001-beguz,
          endzu TYPE pa2001-enduz,
          stdat TYPE pa2001-stdaz,
        END OF t_final.

DATA : it_pa0000 TYPE STANDARD TABLE OF t_pa0000,
       wa_pa0000 TYPE t_pa0000,

        it_final TYPE STANDARD TABLE OF t_final,
        wa_final TYPE t_final.

START-OF-SELECTION.

  SELECT
    pernr
  FROM pa0000
  INTO TABLE it_pa0000.

  SELECT
    a~pernr
    a~btrtl
    b~begda
    b~endda
    b~awart
    b~beguz
    b~enduz
    b~stdaz
  FROM pa0001 AS a
  INNER JOIN pa2001 AS b
  ON a~pernr = b~pernr
  INTO TABLE it_final
  FOR ALL ENTRIES IN it_pa0000
  WHERE
    ( a~pernr = it_pa0000-pernr ) AND
    ( ( a~btrtl = 'DE01' AND b~awart IN ('0200' , '0201' , '0350') ) OR ( a~btrtl = 'AT22' ) ).

Hope this solves your problem.

Thanks & Regards.

Tarun Gambhir.

[Former Member]

hi,

the optimal method in your case would be to write a select statement to read from infotype 1001 and then use FM HR_READ_INFOTYPE to fetch details from 2001.

Regards,

Sumanjeet.