select query doubt

[Former Member]

I have PA0001-BTRTL,

PA0000-STAT2,

P2001-ABWTG, (infotype )

ZWSOABS-AWART.

PA0001, PA0000, P2001 having common field PERNR.

In ZWSOABS contains only one field i.e., AWART.

how can i get the data in the internal table.

Please guide me with the query. your help would be appreciated.

[RichHeilman · Accepted solution]

What is the relationship between the other tables are the ZWSOABS. If there is no relationship, how do you want to join it.

You can join the first two tables like so...

data: begin of itab occurs 0,
      btrtl type pa0001-btrtl,
      stat2 type pa0000-stat2,
      abwtg type p2001-abwtg,
      end of itab.

parameters: p_pernr type pa0001-pernr.

start-of-selection.


  select pa0001~btrtl pa0000~stat2
            into corresponding fields of table itab
                     from pa0001
                         inner join pa0000
                            on pa0001~pernr = pa0000~pernr
                                     where pa0001~pernr = p_pernr.


  check sy-subrc  = 0.

Regards,

Rich Heilman

[Former Member · Accepted solution]

Can i take AWART field which is in PA2001.

Let me know the query if I take this field from the table pa2001.

i have p2001-begda, p2001-enda, pa0001-btrtl as select options in selection screen.

[Former Member · Accepted solution]

hi,

yes you can take it from pa2001 ( no need to go ztable )

the query is

select a_{pernr ........c}awart

from pa000 as a inner join

pa0001 as b on

a_{pernr = b}pernr

inner join p2001 as c

on a_{pernr = c}pernr

where b~btrtl in s_btrtl and

c~begda in s_begda and

c_{enda in s}enda.

cheers,

sasi

[Former Member · Accepted solution]

it showing an error called P2001 is not a database table

(p2001 is an infotype)

[RichHeilman · Accepted solution]

Thats because p2001 is not a database table it is a structure in the ABAP dictionary.

Regards,

Rich Heilman

[former_member723628 · Accepted solution]

Change P2001 TO PA2001 and check...It should work.

Regards,

Gajendra Bhatt

[Former Member · Accepted solution]

hi,

use PA2001

cheers,

sasi

[Former Member · Accepted solution]

ok..i am sending the code .

Can u add these fields: - 1) PA000-STAT2

2) PA2001-AWART

3) PA2001-ABWTG

to this ALV Report.

I am little bit in confusion.

<u><b>Code is as follows:-</b></u>

TABLES : PA0000,

PA0001,

T001P. "Personal Area /Sub Area

INFOTYPES : 2001.

TYPE-POOLS : SLIS.

• Data declarations *

TYPES : BEGIN OF TY_BTEXT,

BTEXT TYPE T001P-BTEXT,

END OF TY_BTEXT.

DATA : TBL_BTEXT TYPE STANDARD TABLE OF TY_BTEXT,

WA_BTEXT TYPE TY_BTEXT.

• ALV Data declartions *

DATA : FIELDCATALOG TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE,

GD_TAB_GROUP TYPE SLIS_T_SP_GROUP_ALV,

GD_LAYOUT TYPE SLIS_LAYOUT_ALV,

GD_REPID LIKE SY-REPID.

• Start of Selection screen *

SELECTION-SCREEN : BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS : S_BEGDA FOR PA2001-BEGDA OBLIGATORY.

SELECT-OPTIONS : S_ENDA FOR PA2001-ENDA OBLIGATORY.

SELECT-OPTIONS : S_BTRTL FOR PA0001-BTRTL OBLIGATORY.

SELECTION-SCREEN : END OF BLOCK B1.

• End of selection screen *

START-OF-SELECTION.

PERFORM DATA_RETRIEVAL.

PERFORM BUILD_FIELDCATALOG.

PERFORM BUILD_LAYOUT.

PERFORM DISPLAY_ALV_REPORT.

*LOOP AT TBL_BTEXT INTO WA_BTEXT.

• WRITE 😕 WA_BTEXT-BTEXT.

*ENDLOOP.

• to get the no. of workers

*DATA : NO_OF_WORKERS TYPE I.

*

SELECT COUNT() INTO NO_OF_WORKERS FROM PA0000

*WHERE ENDDA = '99991231' AND STAT2 = '3'.

*

*WRITE 😕 NO_OF_WORKERS.

*

**to get the no. of working days lost

*DATA : WORK_DAYS_LOST TYPE P DECIMALS 2.

*

SELECT COUNT() INTO WORK_DAYS_LOST FROM PA2001

*WHERE ABWTG = P2001-ABWTG.

*

*WRITE 😕 WORK_DAYS_LOST.

&----

---

*& Form DATA_RETRIEVAL

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM DATA_RETRIEVAL.

*to get the personal sub area text

SELECT BTEXT INTO TABLE TBL_BTEXT FROM T001P WHERE BTRTL IN S_BTRTL.

ENDFORM. " DATA_RETRIEVAL

&----

---

*& Form BUILD_FIELDCATALOG

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM BUILD_FIELDCATALOG.

FIELDCATALOG-FIELDNAME = 'BTEXT'.

FIELDCATALOG-SELTEXT_M = 'Location'.

FIELDCATALOG-COL_POS = 1.

APPEND FIELDCATALOG TO FIELDCATALOG.

CLEAR FIELDCATALOG.

ENDFORM. " BUILD_FIELDCATALOG

&----

---

*& Form BUILD_LAYOUT

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM BUILD_LAYOUT.

GD_LAYOUT-NO_INPUT = 'X'.

GD_LAYOUT-COLWIDTH_OPTIMIZE = 'X'.

ENDFORM. " BUILD_LAYOUT

&----

---

*& Form DISPLAY_ALV_REPORT

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM DISPLAY_ALV_REPORT.

GD_REPID = SY-REPID.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

• I_INTERFACE_CHECK = ' '

• I_BYPASSING_BUFFER =

• I_BUFFER_ACTIVE = ' '

I_CALLBACK_PROGRAM = GD_REPID

• I_CALLBACK_PF_STATUS_SET = ' '

• I_CALLBACK_USER_COMMAND = ' '

• I_CALLBACK_TOP_OF_PAGE = ' '

• I_CALLBACK_HTML_TOP_OF_PAGE = ' '

• I_CALLBACK_HTML_END_OF_LIST = ' '

• I_STRUCTURE_NAME =

• I_BACKGROUND_ID = ' '

• I_GRID_TITLE =

• I_GRID_SETTINGS =

IS_LAYOUT = GD_LAYOUT

IT_FIELDCAT = FIELDCATALOG[]

• IT_EXCLUDING =

• IT_SPECIAL_GROUPS =

• IT_SORT =

• IT_FILTER =

• IS_SEL_HIDE =

• I_DEFAULT = 'X'

• I_SAVE = ' '

• IS_VARIANT =

• IT_EVENTS =

• IT_EVENT_EXIT =

• IS_PRINT =

• IS_REPREP_ID =

• I_SCREEN_START_COLUMN = 0

• I_SCREEN_START_LINE = 0

• I_SCREEN_END_COLUMN = 0

• I_SCREEN_END_LINE = 0

• IT_ALV_GRAPHICS =

• IT_ADD_FIELDCAT =

• IT_HYPERLINK =

• I_HTML_HEIGHT_TOP =

• I_HTML_HEIGHT_END =

• IT_EXCEPT_QINFO =

• IMPORTING

• E_EXIT_CAUSED_BY_CALLER =

• ES_EXIT_CAUSED_BY_USER =

TABLES

T_OUTTAB = TBL_BTEXT

EXCEPTIONS

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

ENDFORM. " DISPLAY_ALV_REPORT

[RichHeilman · Accepted solution]

This may not be 100% correct, because I don't really no your business requiement.

report zrich_0006 .
tables : pa0000,
pa0001, <b>pa2001,</b>
t001p. 

infotypes : 2001.

type-pools : slis.


* Data declarations *
types : begin of ty_btext,
btext type t001p-btext,
end of ty_btext.

data : tbl_btext type standard table of ty_btext,
wa_btext type ty_btext.

* ALV Data declartions *
data : fieldcatalog type slis_t_fieldcat_alv with header line,
gd_tab_group type slis_t_sp_group_alv,
gd_layout type slis_layout_alv,
gd_repid like sy-repid.

<b>data: begin of itab occurs 0,
      btrtl type pa0001-btrtl,
      btext type t001p-btext,
      stat2 type pa0000-stat2,
      abwtg type p2001-abwtg,
      end of itab.</b>


* Start of Selection screen *
selection-screen : begin of block b1 with frame title text-001.
select-options : s_begda for pa2001-begda obligatory.
<b>select-options : s_endda for pa2001-endda obligatory.</b>
select-options : s_btrtl for pa0001-btrtl obligatory.
selection-screen : end of block b1.
* End of selection screen *

start-of-selection.
  perform data_retrieval.
  perform build_fieldcatalog.
  perform build_layout.
  perform display_alv_report.

*LOOP AT TBL_BTEXT INTO WA_BTEXT.
* WRITE 😕 WA_BTEXT-BTEXT.
*ENDLOOP.

** to get the no. of workers
*DATA : NO_OF_WORKERS TYPE I.
*
*SELECT COUNT(*) INTO NO_OF_WORKERS FROM PA0000
*WHERE ENDDA = '99991231' AND STAT2 = '3'.
*
*WRITE 😕 NO_OF_WORKERS.
*
**to get the no. of working days lost
*DATA : WORK_DAYS_LOST TYPE P DECIMALS 2.
*
*SELECT COUNT(*) INTO WORK_DAYS_LOST FROM PA2001
*WHERE ABWTG = P2001-ABWTG.
*
*WRITE 😕 WORK_DAYS_LOST.


*&---------------------------------------------------------------------*
*& Form DATA_RETRIEVAL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form data_retrieval.



<b>  select pa0001~btrtl pa0000~stat2  pa2001~abwtg t001p~btext
       into corresponding fields of table itab
            from pa0001
                inner join pa0000
                     on pa0001~pernr = pa0000~pernr
                inner join pa2001
                        on pa0001~pernr = pa2001~pernr
                inner join t001p
                        on pa0001~btrtl = t001p~btrtl
                       where pa0001~btrtl in s_btrtl
                         and pa0000~begda in s_begda
                         and pa0000~endda in s_endda.</b>


  .

endform. 


*&---------------------------------------------------------------------*
*& Form BUILD_FIELDCATALOG
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form build_fieldcatalog.

<b>  fieldcatalog-fieldname = 'BTRTL'.
  fieldcatalog-seltext_m = 'Location Code'.
  fieldcatalog-col_pos = 1.
  append fieldcatalog to fieldcatalog.
  clear fieldcatalog.


  fieldcatalog-fieldname = 'BTEXT'.
  fieldcatalog-seltext_m = 'Location Text'.
  fieldcatalog-col_pos = 2.
  append fieldcatalog to fieldcatalog.
  clear fieldcatalog.

  fieldcatalog-fieldname = 'STAT2'.
  fieldcatalog-seltext_m = 'Status 2'.
  fieldcatalog-col_pos = 3.
  append fieldcatalog to fieldcatalog.
  clear fieldcatalog.

  fieldcatalog-fieldname = 'ABWTG'.
  fieldcatalog-seltext_m = 'ABWTG Text'.
  fieldcatalog-col_pos = 4.
  append fieldcatalog to fieldcatalog.
  clear fieldcatalog.</b>

endform. 



*&---------------------------------------------------------------------*
*& Form BUILD_LAYOUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form build_layout.

  gd_layout-no_input = 'X'.
  gd_layout-colwidth_optimize = 'X'.

endform.


*&---------------------------------------------------------------------*
*& Form DISPLAY_ALV_REPORT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
form display_alv_report.

  gd_repid = sy-repid.

  call function 'REUSE_ALV_GRID_DISPLAY'
  exporting
* I_INTERFACE_CHECK = ' '
* I_BYPASSING_BUFFER =
* I_BUFFER_ACTIVE = ' '
  i_callback_program = gd_repid
* I_CALLBACK_PF_STATUS_SET = ' '
* I_CALLBACK_USER_COMMAND = ' '
* I_CALLBACK_TOP_OF_PAGE = ' '
* I_CALLBACK_HTML_TOP_OF_PAGE = ' '
* I_CALLBACK_HTML_END_OF_LIST = ' '
* I_STRUCTURE_NAME =
* I_BACKGROUND_ID = ' '
* I_GRID_TITLE =
* I_GRID_SETTINGS =
  is_layout = gd_layout
  it_fieldcat = fieldcatalog[]
* IT_EXCLUDING =
* IT_SPECIAL_GROUPS =
* IT_SORT =
* IT_FILTER =
* IS_SEL_HIDE =
* I_DEFAULT = 'X'
* I_SAVE = ' '
* IS_VARIANT =
* IT_EVENTS =
* IT_EVENT_EXIT =
* IS_PRINT =
* IS_REPREP_ID =
* I_SCREEN_START_COLUMN = 0
* I_SCREEN_START_LINE = 0
* I_SCREEN_END_COLUMN = 0
* I_SCREEN_END_LINE = 0
* IT_ALV_GRAPHICS =
* IT_ADD_FIELDCAT =
* IT_HYPERLINK =
* I_HTML_HEIGHT_TOP =
* I_HTML_HEIGHT_END =
* IT_EXCEPT_QINFO =
* IMPORTING
* E_EXIT_CAUSED_BY_CALLER =
* ES_EXIT_CAUSED_BY_USER =
  tables
<b>  t_outtab = itab</b>
  exceptions
  program_error = 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.


endform. 

I have highlighted the changes in "Bold".

Please remember to award points for helpful answers. Thanks.

Regards,

Rich Heilman

[Former Member · Accepted solution]

can anyone hav a look on the code ..and help me

your effort will be appreciated with points

[RichHeilman · Accepted solution]

Please see the above. Thanks

Regards,

Rich Heilman

[Former Member · Accepted solution]

hai Rich..thank u for your valuable time

but i am unable to see the report output.

it's not showing any data

[RichHeilman · Accepted solution]

I guess it all depends on the data in your system. In my system, if I run from 01/01/2005 in the beginning data and 12/31/9999 in the ending date and then choose a value for the other field, I get data in my ALV grid.

Regards,

Rich Heilman

[Former Member · Accepted solution]

ok..thank u rich. I definitely reward the points.