topic Re: Select query in Application Development and Automation Discussions

Select query

Former Member — Wed, 30 Jul 2008 07:10:36 GMT

Hi Friends,

I need to retrieving the record from table 'pa0041' with select query for particular employee, this table has 'date type' and 'date' fields 12 times (dar01,dat01 to dar12,dat12).

Suppose i need to find out date type = 32 this may exist in any field repeated from dar01 to dar12 how can i fetch only particular record where datetype eq 32.

please give logic.

Thanks in advance.

chandra.

Re: Select query

Former Member — Wed, 30 Jul 2008 07:12:20 GMT

hi,

Use DO Varying Statement to Check your Record.

eg.

Do w_dat varying from w_dat01 next w_dat02.

Regards

Sumit Agarwal

Re: Select query

Former Member — Wed, 30 Jul 2008 07:13:56 GMT

Hi Purna Chandra,

the Date types in info type PA0041 are only 12 . That is from DAR01 to DAR12.

TABLES :
  pa0000,
  pa0041.

*"Selection screen elements............................................
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS :
  s_pernr FOR pa0000-pernr.
SELECTION-SCREEN END OF BLOCK b1.

DATA :
  BEGIN OF fs_temp,
    pernr TYPE persno,
    years TYPE i,
    months TYPE i,
  END OF fs_temp.

DATA :
  t_temp LIKE
STANDARD TABLE
      OF fs_temp.

DATA :
  t_pa0000 TYPE TABLE OF pa0000,

  t_pa0041 LIKE
  STANDARD TABLE
        OF p0041.

DATA :
  fs_pa0000 TYPE pa0000,
  fs_pa0041 TYPE p0041,
  w_dar     TYPE datar,                " Date Type
  w_dat     TYPE dardt.                " Date for Date Type
  
 SELECT pernr
         endda
         begda
         stat2
    FROM pa0000
    INTO CORRESPONDING FIELDS OF TABLE t_pa0000
   WHERE pernr IN s_pernr
     AND endda GE sy-datum
     AND begda LE sy-datum
     AND stat2 EQ '3'.

LOOP AT t_pa0000 INTO fs_pa0000.
    CALL FUNCTION 'HR_READ_INFOTYPE'
      EXPORTING
        pernr                 = fs_pa0000-pernr
        infty                 = '0041'
       begda                 = fs_pa0000-begda
       endda                 = fs_pa0000-endda
      TABLES
        infty_tab             = t_pa0041
      EXCEPTIONS
        INFTY_NOT_FOUND       = 1
        OTHERS                = 2.
    IF sy-subrc NE 0.
      MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    ENDIF.                             " IF SY-SUBRC NE 0
  ENDLOOP.                             " LOOP AT T_PA0000...

  LOOP AT t_pa0041 INTO fs_pa0041.

    DO VARYING w_dar FROM fs_pa0041-dar01 NEXT  fs_pa0041-dar02
       VARYING w_dat  FROM fs_pa0041-dat01 NEXT  fs_pa0041-dat02.

      IF w_dar EQ '32'.
         
<your code> -- what you want to do               
         
      ENDIF.                           " IF W_DAR EQ '32'

    ENDDO.                             " DO VARYING W_DAR FROM...

  ENDLOOP.                             " LOOP AT T_PA0041 INTO...

In the above code w_dar is of TYPE datar, " Date Type

w_dat is of TYPE dardt, " Date for Date Type

Regards,

Swapna.

Edited by: NagaSwapna Thota on Jul 30, 2008 12:44 PM

Re: Select query

Former Member — Wed, 30 Jul 2008 07:15:15 GMT

Hi!

Check out this sample code. Here I've used

Do 12 TIMES VARYING....

ENDDO.


REPORT z_sdn.
PARAMETERS:
  p_pernr TYPE pa0000-pernr,
  p_dat TYPE t548t-datar.

DATA:
  BEGIN OF fs_pa0000,
    pernr TYPE pa0000-pernr,
  END OF fs_pa0000,
  w_dtype TYPE pa0041-dar01,
  w_dat TYPE pa0041-dat01.
DATA: fs_548 TYPE t548t.
DATA: t_548 LIKE
      TABLE OF
            fs_548.
DATA:
  t_pa0000 LIKE
     TABLE OF
           fs_pa0000.
DATA:
  fs_p41 TYPE p0041.

DATA:
  t_p41 TYPE
  TABLE OF
        p0041.

START-OF-SELECTION.

  PERFORM get_pernr.
  PERFORM get_data.
  PERFORM disp_data.

*&---------------------------------------------------------------------*
*&      Form  get_pernr
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM get_pernr .
  SELECT pernr
    FROM pa0000
    INTO TABLE t_pa0000
   WHERE pernr EQ p_pernr
     AND begda LT sy-datum
     AND endda GT sy-datum.
ENDFORM.                    " get_pernr
*&---------------------------------------------------------------------*
*&      Form  get_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM get_data .
  LOOP AT t_pa0000 INTO fs_pa0000.
    CALL FUNCTION 'HR_READ_INFOTYPE'
      EXPORTING
        pernr                 = fs_pa0000-pernr
        infty                 = '0041'
      TABLES
        infty_tab             = t_p41
     EXCEPTIONS
       infty_not_found       = 1
       OTHERS                = 2
              .
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.

  ENDLOOP.
ENDFORM.                    " get_data
*&---------------------------------------------------------------------*
*&      Form  disp_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM disp_data .
   READ TABLE t_p41 INTO fs_p41 INDEX 1.
   DO 12 TIMES VARYING w_dtype FROM fs_p41-dar01 NEXT fs_p41-dar02

      VARYING w_dat FROM fs_p41-dat01 NEXT fs_p41-dat02.

     IF p_dat EQ w_dtype.
       SELECT *
         FROM t548t
         INTO TABLE t_548
        WHERE datar = w_dtype
          AND sprsl = 'EN'.
       READ TABLE t_548 INTO fs_548 INDEX 1.
         WRITE: /2  'Personnel Number'(002),
                 15 'Begin Date'(003),
                 28 'End Date'(004),
                 40 'Date Type'(005),
                 53 'Date'(006),
                 65 'Date Text'(007).
         WRITE: / fs_p41-pernr UNDER text-002,
                  fs_p41-begda UNDER text-003,
                  fs_p41-endda UNDER text-004,
                  w_dtype      UNDER text-005,
                  w_dat        UNDER text-006,
                  fs_548-dtext UNDER text-007.
       EXIT.
     ENDIF.
   ENDDO.
ENDFORM.                    " disp_data

Regards

Abhijeet

Re: Select query

Former Member — Wed, 30 Jul 2008 07:15:25 GMT

Sumit with LDB it is possible, with out using LDB will it works

need a reply, if possible try to send code plz.

thanks,

Chandra

Re: Select query

Former Member — Wed, 30 Jul 2008 07:18:30 GMT

Yes it works even without LDB.

Try the code I send above and see.

Regards,

Swapna.

Re: Select query

Former Member — Wed, 30 Jul 2008 07:19:04 GMT

Hi,,

Check the Below code-You will find the way



tables:
 pa0041.
*"-------------------------------------------------------------------"*
*Parameters Declaration.
*"-------------------------------------------------------------------"*
parameters:
 p_pernr type pa0000-pernr,            " Presonnel Number.
 p_date  type pa0041-dar01.            " Date type.
*"-------------------------------------------------------------------"*
*Internal Tables Declaration.
*"-------------------------------------------------------------------"*
data:
  t_pa0041 like standard table
        of p0041.
data:
   fs_pa0041 like line of t_pa0041,
   w_date type pa0041-dat01,
   w_dtype type pa0041-dar01,
   t_t548 like standard table of t548t,
   fs_t548 like line of t_t548.
*"-------------------------------------------------------------------"*
*                  START-OF-SELECTION.
*"-------------------------------------------------------------------"*
start-of-selection.
perform read_data.
perform dispay_list.
*&---------------------------------------------------------------------*
*&      Form  read_data
*&---------------------------------------------------------------------*
*       This Subroutine Read data from Infotype 0041 Through FM.
*----------------------------------------------------------------------*
*       There Is No interface parameter to be passed in this subroutine
*----------------------------------------------------------------------*
form read_data .

 call function 'HR_READ_INFOTYPE'
    exporting
      pernr                 = p_pernr
      infty                 = '0041'
     begda                 = sy-datum
     endda                 = sy-datum
    tables
      infty_tab             = t_pa0041
   exceptions
     infty_not_found       = 1
     others                = 2
            .
  if sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  endif.                               " IF sy-subrc <> 0.

endform.                               " Read_data.
*&---------------------------------------------------------------------*
*&      Form  dispay_list
*&---------------------------------------------------------------------*
*       This Subroutine Display data from Internal table
*----------------------------------------------------------------------*
*       There Is No interface parameter to be passed in this subroutine
*----------------------------------------------------------------------*
form dispay_list .

read table t_pa0041 into fs_pa0041 index 1.

  do varying w_dtype from fs_pa0041-dar01 next fs_pa0041-dar02
     varying w_date from fs_pa0041-dat01 next fs_pa0041-dat02.

    if p_date = w_dtype.

      select *
        from t548t up to 1 rows
        into table t_t548
       where datar = w_dtype
         and sprsl = sy-langu.

      read table t_t548 into fs_t548 index 1.

      write:
          / fs_pa0041-pernr,
           15 fs_pa0041-begda,
           30 fs_pa0041-endda,
           45 p_date,
           52 w_date,
           65 fs_t548-dtext.
     exit.
    endif.                             " IF p_date = w_dtype.
  enddo.                               " DO 1 TIMES VARYING.

Regards,

Sujit

Re: Select query

Former Member — Wed, 30 Jul 2008 07:19:22 GMT

Hi Purna,

Try the following while displaying:

READ TABLE t_p0041 INTO fs_p0041 INDEX 1.

DO 12 TIMES VARYING w_dtype FROM fs_p0041-dar01 NEXT fs_p0041-dar02

VARYING w_dat FROM fs_p0041-dat01 NEXT fs_p0041-dat02.

Regards,

Chandra Sekhar

Re: Select query

Former Member — Wed, 30 Jul 2008 07:20:06 GMT

Do as follows

LOOP AT t_pa0041 INTO fs_pa0041.

DO VARYING w_dar FROM fs_pa0041-dar01 NEXT fs_pa0041-dar02

VARYING w_dat FROM fs_pa0041-dat01 NEXT fs_pa0041-dat02.

IF w_dar EQ '32'.

<your code> -- what you want to do

ENDIF. " IF W_DAR EQ '32'

ENDDO.

Re: Select query

Former Member — Wed, 30 Jul 2008 07:20:28 GMT

hi,

It can work.

Sumit Agarwal