Re: Select statement

Former Member · ‎2006 Dec 04

Hi all ,

In this code i have 2 fields pbpino ,pbpapino . Instead of hitting same table somany times(with single select statement) is there any other way so that i can hit the table once and get the values .

SELECT PBPINO PBPAPINO FROM ZWPBPH INTO TABLE T_ZWPBPH FOR ALL ENTRIES

IN T_MARA WHERE PBPINO = T_MARA-MATNR.

ENDIF.

IF SY-SUBRC = 0.

LOOP AT T_ZWPBPH.

SELECT SINGLE PBPINO PBPAPINO FROM ZWPBPH INTO T_MODEL WHERE

PBPINO = T_ZWPBPH-PBPAPINO.

IF SY-SUBRC = 0.

SELECT SINGLE PBPINO PBPAPINO FROM ZWPBPH INTO T_PRODUCT WHERE

PBPINO = T_MODEL-PBPAPINO.

ENDIF.

IF SY-SUBRC = 0.

SELECT SINGLE PBPINO PBPAPINO FROM ZWPBPH INTO T_FAMILY WHERE

PBPINO = T_PRODUCT-PBPAPINO.

ENDIF.

IF SY-SUBRC = 0.

SELECT SINGLE PBPINO PBPAPINO FROM ZWPBPH INTO T_LINE WHERE

PBPINO = T_FAMILY-PBPAPINO.

ENDIF.

IF SY-SUBRC = 0.

SELECT SINGLE PBPINO PBPAPINO FROM ZWPBPH INTO T_TYPE WHERE

PBPINO = T_LINE-PBPAPINO.

ENDIF.

IF SY-SUBRC = 0.

READ TABLE T_MARA WITH KEY MATNR = T_ZWPBPH-PBPINO.

IF SY-SUBRC = 0.

IF T_MARA-SPART <> T_TYPE-PBPAPINO+1(2).

MOVE-CORRESPONDING T_MARA TO T_ALV.

IF SY-SUBRC = 0.

MOVE T_ZWPBPH-PBPINO TO T_ALV-MATNR.

MOVE T_TYPE-PBPAPINO+1(2) TO T_ALV-SPART1.

APPEND T_ALV.

CLEAR T_ALV.

ENDIF.

RichHeilman · ‎2006 Dec 04

Yes, get all relevent records from ZWPBPH into an internal table of the same structure. Then simply use the READ table statement to read the line of the internal table. THis way you hit the database once for this table, not many.

Regards,

Rich Heilman

Former Member · ‎2006 Dec 04

Rich ,

Here problem is in t_zwpbph table contains two fields pbpino (1)and pbpapino(2) for ex ; values 1 and 2 . i need to submit value 2 to pbpino value to get value 3 , 4 again need to submit value 4 to pbpino to get value 5 and 6 . Finally i need to link 1 and 6 . For this scenario is there any other way instead of writing many select statement?

Message was edited by:

priya katragadda

Message was edited by:

priya katragadda

Former Member · ‎2006 Dec 04

Hi Priya,

Try this:

DATA: T_PBPINO LIKE ZWPBPH-PBPINO,

T_PBPAPINO LIKE ZWPBPH-PBPAPINO.

SELECT PBPINO PBPAPINO FROM ZWPBPH INTO TABLE T_ZWPBPH FOR ALL ENTRIES

IN T_MARA WHERE PBPINO = T_MARA-MATNR.

ENDIF.

IF SY-SUBRC = 0.

LOOP AT T_ZWPBPH.

SELECT SINGLE PBPINO PBPAPINO FROM ZWPBPH INTO (T_PBPINO, T_PBPAPINO)

WHERE PBPINO = T_ZWPBPH-PBPAPINO.

IF SY-SUBRC = 0.

MOVE: T_PBPINO TO T_MODEL-PBPINO,

T_PBPAPINO TO T_MODEL-PBPAPINO.

MOVE: T_PBPINO TO T_PRODUCT-PBPINO,

T_PBPAPINO TO T_PRODUCT-PBPAPINO.

MOVE: T_PBPINO TO T_FAMILY-PBPINO,

T_PBPAPINO TO T_FAMILY-PBPAPINO.

MOVE: T_PBPINO TO T_LINE-PBPINO,

T_PBPAPINO TO T_LINE-PBPAPINO.

MOVE: T_PBPINO TO T_TYPE-PBPINO,

T_PBPAPINO TO T_TYPE-PBPAPINO.

IF SY-SUBRC = 0.

READ TABLE T_MARA WITH KEY MATNR = T-PBPINO.

IF SY-SUBRC = 0.

IF T_MARA-SPART <> T_TYPE-PBPAPINO+1(2).

MOVE-CORRESPONDING T_MARA TO T_ALV.

IF SY-SUBRC = 0.

MOVE T_ZWPBPH-PBPINO TO T_ALV-MATNR.

MOVE T_TYPE-PBPAPINO+1(2) TO T_ALV-SPART1.

APPEND T_ALV.

CLEAR T_ALV.

ENDIF.

I hope this would suffice your need...you would get required pbpino and pbpapino into temp values T_PBPINO, T_PBPAPINO in this case and move them to required internal tables.

Regards,

Vivek

Former Member · ‎2006 Dec 04

If ZWPBPH isn't too large or you will be selecting a large portion of it, it may be quicker to read the whole thing into an internal table and then use READs with binary search to get the data you need.

Rob

Former Member · ‎2006 Dec 04

Vivek,

Here my scenario is different i need to submit T_MODEL-PBPAPINO to get T_PRODUCT-PBPINO and T_PRODUCT-PBPAPINO same for T_family ,T_line , T_type .

Thanks

Former Member · ‎2006 Dec 04

Hi Priya,

I didn't understand your requirement which you replied for rich's reply. Can you elaborate it or paste your code? Otherwise, download the data into an internal table and read data from internal table using binary search as suggested by Rich or Rob without any problem.

Regards,

Vivek

Former Member · ‎2006 Dec 04

Hi vivek ,

This is my code . Here Iam getting data from zwpbph table . This table contains two fields pbpino ,pbpapino lets assume values 1 , 2. I have to submit this value 2 (t_model) to pbpino again to get value 3 and 4 (T_product) again submit value for zwpbph-pbpino table to get values 5 and 6 (T_family ) again submit value 6 to zwpbph -pbpino to get values 7 and 8 (t_type ). Every time i have to submit pbpapino value to zwpbph-pbpino to get next values. This like

t_zwpbph

pbpino- 1

pbpapino-2

submit value 2 zwpbph - pbpino

pbpino-3

pbpapino-4

.

REPORT ZWRLOMDMM_PMG_PHWEB_PLD NO STANDARD PAGE HEADING

LINE-SIZE 120

LINE-COUNT 65

MESSAGE-ID 00.

----

TABLES *

----

TABLES: MARA , "General Material Data

ZWPBPH. " Published Product Hierarchy

----

TYPE-POOLS *

----

TYPE-POOLS:SLIS .

DATA:FIELDCATALOG_BASIC TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE,

FIELDCATALOG_SECONDARY 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.

----

INTERNAL TABLES *

----

*Internal Tables to Get Data.

*Internal Table For MARA Table.

DATA:BEGIN OF T_MARA OCCURS 0 ,

MATNR LIKE MARA-MATNR,

MTART LIKE MARA-MTART,

ERSDA LIKE MARA-ERSDA,

ERNAM LIKE MARA-ERNAM,

AENAM LIKE MARA-AENAM,

SPART LIKE MARA-SPART,

LAEDA LIKE MARA-LAEDA,

END OF T_MARA.

*Internal Table For ZWPBPH Table.

DATA:BEGIN OF T_ZWPBPH OCCURS 0 ,

PBPINO LIKE ZWPBPH-PBPINO,

PBPAPINO LIKE ZWPBPH-PBPAPINO,

END OF T_ZWPBPH.

*Internal Table For Basic List Field Catalog.

DATA:BEGIN OF T_ALV OCCURS 0 ,

MATNR LIKE MARA-MATNR,

MTART LIKE MARA-MTART,

ERSDA LIKE MARA-ERSDA,

ERNAM LIKE MARA-ERNAM,

SPART LIKE MARA-SPART,

SPART1(3),

END OF T_ALV.

*Internal Table For Secondary List Field Catalog.

DATA: BEGIN OF T_ALV_TEMP OCCURS 0 ,

MATNR LIKE MARA-MATNR,

AENAM LIKE MARA-AENAM,

LAEDA LIKE MARA-LAEDA,

END OF T_ALV_TEMP.

*Internal Table For Model

DATA:T_MODEL LIKE T_ZWPBPH OCCURS 0 WITH HEADER LINE.

*Internal Table For Product

DATA:T_PRODUCT LIKE T_ZWPBPH OCCURS 0 WITH HEADER LINE.

*Internal Table For Family

DATA:T_FAMILY LIKE T_ZWPBPH OCCURS 0 WITH HEADER LINE.

*Internal Table For Line

DATA:T_LINE LIKE T_ZWPBPH OCCURS 0 WITH HEADER LINE.

*Internal Table For Type

DATA:T_TYPE LIKE T_ZWPBPH OCCURS 0 WITH HEADER LINE.

----

SELECTION SCREEN *

----

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETERS: P_MATN RADIOBUTTON GROUP R1 USER-COMMAND run default 'X'.

SELECT-OPTIONS: P_MATNR FOR MARA-MATNR MODIF ID SC1 .

PARAMETERS: P_DAT RADIOBUTTON GROUP R1 .

SELECT-OPTIONS: : P_DATE FOR MARA-LAEDA MODIF ID SC2.

PARAMETERS: P_DIV RADIOBUTTON GROUP R1 .

SELECT-OPTIONS: : P_DIVIS for mara-spart MODIF ID SC3.

SELECTION-SCREEN END OF BLOCK b1.

----

INITIALIZATION *

----

INITIALIZATION.

REFRESH: T_MARA,

T_ZWPBPH,

T_ALV,

T_MODEL,

T_PRODUCT,

T_FAMILY,

T_LINE,

T_TYPE.

CLEAR: T_MARA,

T_ZWPBPH,

T_ALV,

T_MODEL,

T_PRODUCT,

T_FAMILY,

T_LINE,

T_TYPE.

GD_REPID = SY-REPID.

----

*AT SELECTION-SCREEN OUTPUT.

----

AT SELECTION-SCREEN OUTPUT.

LOOP AT SCREEN.

IF P_MATN = 'X'.

FREE : P_DIVIS,P_DATE.

IF SCREEN-GROUP1 = 'SC2' or SCREEN-GROUP1 = 'SC3'.

SCREEN-INPUT = 0.

MODIFY SCREEN.

CONTINUE.

ENDIF .

ENDIF.

IF P_DAT = 'X'.

FREE : P_MATNR,P_DIVIS.

IF SCREEN-GROUP1 = 'SC1' or SCREEN-GROUP1 = 'SC3'.

SCREEN-INPUT = 0.

MODIFY SCREEN.

CONTINUE.

ENDIF .

ENDIF.

IF P_DIV = 'X'.

FREE : P_DATE,P_MATNR.

IF SCREEN-GROUP1 = 'SC1' or SCREEN-GROUP1 = 'SC2'.

SCREEN-INPUT = 0.

MODIFY SCREEN.

CONTINUE.

ENDIF .

ENDIF.

ENDLOOP.

----

START-OF-SELECTION *

----

START-OF-SELECTION.

PERFORM GET_FIELDCATALOG_BASIC.

PERFORM GET_FIELDCATALOG_SECONDARY.

PERFORM POPULATE_DATA.

----

END-OF-SELECTION *

----

END-OF-SELECTION.

&----

*& FORM GET_FIELDCATALOG_BASIC.

&----

*

Get Fieldcatalog For Basic List

FORM GET_FIELDCATALOG_BASIC.

FIELDCATALOG_BASIC-FIELDNAME = 'MATNR'.

FIELDCATALOG_BASIC-SELTEXT_M = 'Material Number'.

FIELDCATALOG_BASIC-COL_POS = 0.

FIELDCATALOG_BASIC-OUTPUTLEN = 20.

FIELDCATALOG_BASIC-HOTSPOT = 'X'.

FIELDCATALOG_BASIC-EMPHASIZE = 'X'.

FIELDCATALOG_BASIC-KEY = 'X'.

APPEND FIELDCATALOG_BASIC TO FIELDCATALOG_BASIC.

CLEAR FIELDCATALOG_BASIC.

FIELDCATALOG_BASIC-FIELDNAME = 'MTART'.

FIELDCATALOG_BASIC-SELTEXT_M = 'Material Type'.

FIELDCATALOG_BASIC-COL_POS = 0.

FIELDCATALOG_BASIC-OUTPUTLEN = 20.

FIELDCATALOG_BASIC-EMPHASIZE = 'X'.

FIELDCATALOG_BASIC-KEY = 'X'.

APPEND FIELDCATALOG_BASIC TO FIELDCATALOG_BASIC.

CLEAR FIELDCATALOG_BASIC.

FIELDCATALOG_BASIC-FIELDNAME = 'ERSDA'.

FIELDCATALOG_BASIC-SELTEXT_M = 'Created Date'.

FIELDCATALOG_BASIC-COL_POS = 0.

FIELDCATALOG_BASIC-OUTPUTLEN = 12.

FIELDCATALOG_BASIC-EMPHASIZE = 'X'.

FIELDCATALOG_BASIC-KEY = 'X'.

APPEND FIELDCATALOG_BASIC TO FIELDCATALOG_BASIC.

CLEAR FIELDCATALOG_BASIC.

FIELDCATALOG_BASIC-FIELDNAME = 'ERNAM'.

FIELDCATALOG_BASIC-SELTEXT_M = 'Created By'.

FIELDCATALOG_BASIC-COL_POS = 0.

FIELDCATALOG_BASIC-OUTPUTLEN = 15.

FIELDCATALOG_BASIC-EMPHASIZE = 'X'.

FIELDCATALOG_BASIC-KEY = 'X'.

APPEND FIELDCATALOG_BASIC TO FIELDCATALOG_BASIC.

CLEAR FIELDCATALOG_BASIC.

FIELDCATALOG_BASIC-FIELDNAME = 'SPART'.

FIELDCATALOG_BASIC-SELTEXT_M = 'Division PMG'.

FIELDCATALOG_BASIC-COL_POS = 0.

FIELDCATALOG_BASIC-OUTPUTLEN = 14.

FIELDCATALOG_BASIC-EMPHASIZE = 'X'.

FIELDCATALOG_BASIC-KEY = 'X'.

APPEND FIELDCATALOG_BASIC TO FIELDCATALOG_BASIC.

CLEAR FIELDCATALOG_BASIC.

FIELDCATALOG_BASIC-FIELDNAME = 'SPART1'.

FIELDCATALOG_BASIC-SELTEXT_M = 'Division PhWEB'.

FIELDCATALOG_BASIC-COL_POS = 0.

FIELDCATALOG_BASIC-OUTPUTLEN = 14.

FIELDCATALOG_BASIC-EMPHASIZE = 'X'.

FIELDCATALOG_BASIC-KEY = 'X'.

APPEND FIELDCATALOG_BASIC TO FIELDCATALOG_BASIC.

CLEAR FIELDCATALOG_BASIC.

ENDFORM. "GET_FIELDCATALOG_BASIC

&----

*& FORM GET_FIELDCATALOG_SECONDARY

&----

*

Get Fieldcatalog For Secondary List

FORM GET_FIELDCATALOG_SECONDARY.

FIELDCATALOG_SECONDARY-FIELDNAME = 'MATNR'.

FIELDCATALOG_SECONDARY-SELTEXT_M = 'Material Number'.

FIELDCATALOG_SECONDARY-COL_POS = 0.

FIELDCATALOG_SECONDARY-OUTPUTLEN = 20.

FIELDCATALOG_SECONDARY-EMPHASIZE = 'X'.

FIELDCATALOG_SECONDARY-KEY = 'X'.

APPEND FIELDCATALOG_SECONDARY TO FIELDCATALOG_SECONDARY.

CLEAR FIELDCATALOG_SECONDARY.

FIELDCATALOG_SECONDARY-FIELDNAME = 'AENAM'.

FIELDCATALOG_SECONDARY-SELTEXT_M = 'Changed By'.

FIELDCATALOG_SECONDARY-COL_POS = 0.

FIELDCATALOG_SECONDARY-OUTPUTLEN = 20.

FIELDCATALOG_SECONDARY-EMPHASIZE = 'X'.

FIELDCATALOG_SECONDARY-KEY = 'X'.

APPEND FIELDCATALOG_SECONDARY TO FIELDCATALOG_SECONDARY.

CLEAR FIELDCATALOG_SECONDARY.

FIELDCATALOG_SECONDARY-FIELDNAME = 'LAEDA'.

FIELDCATALOG_SECONDARY-SELTEXT_M = 'Changed ON'.

FIELDCATALOG_SECONDARY-COL_POS = 0.

FIELDCATALOG_SECONDARY-OUTPUTLEN = 20.

FIELDCATALOG_SECONDARY-EMPHASIZE = 'X'.

FIELDCATALOG_SECONDARY-KEY = 'X'.

APPEND FIELDCATALOG_SECONDARY TO FIELDCATALOG_SECONDARY.

CLEAR FIELDCATALOG_SECONDARY.

ENDFORM. "GET_FIELDCATALOG_SECONDARY

&----

*& FORM POPULATE_DATA

&----

FORM POPULATE_DATA.

*Get Data From MARA And ZWPBPH Tables

IF P_MATN = 'X'.

SELECT MATNR MTART ERSDA ERNAM AENAM SPART LAEDA FROM MARA INTO TABLE

T_MARA where MATNR IN P_MATNR .

IF SY-SUBRC <> 0.

MESSAGE E001(0) WITH 'Invalid Material Number'.

ENDIF.

ELSEIF P_DAT = 'X'.

SELECT MATNR MTART ERSDA ERNAM AENAM SPART LAEDA FROM MARA INTO TABLE

T_MARA where LAEDA IN P_DATE .

IF SY-SUBRC <> 0.

MESSAGE E001(0) WITH 'Invalid Date Range'.

ENDIF.

ELSEIF P_DIV = 'X'.

SELECT MATNR MTART ERSDA ERNAM AENAM SPART LAEDA FROM MARA INTO TABLE

T_MARA where SPART IN P_DIVIS .

IF SY-SUBRC <> 0.

MESSAGE E001(0) WITH 'Invalid Division'.

ENDIF.

IF T_MARA[] IS NOT INITIAL .

SELECT PBPINO PBPAPINO FROM ZWPBPH INTO TABLE T_ZWPBPH FOR ALL ENTRIES

IN T_MARA WHERE PBPINO = T_MARA-MATNR.

ENDIF.

IF SY-SUBRC = 0.

LOOP AT T_ZWPBPH.

SELECT SINGLE PBPINO PBPAPINO FROM ZWPBPH INTO T_MODEL WHERE

PBPINO = T_ZWPBPH-PBPAPINO.

IF SY-SUBRC = 0.

SELECT SINGLE PBPINO PBPAPINO FROM ZWPBPH INTO T_PRODUCT WHERE

PBPINO = T_MODEL-PBPAPINO.

ENDIF.

IF SY-SUBRC = 0.

SELECT SINGLE PBPINO PBPAPINO FROM ZWPBPH INTO T_FAMILY WHERE

PBPINO = T_PRODUCT-PBPAPINO.

ENDIF.

IF SY-SUBRC = 0.

SELECT SINGLE PBPINO PBPAPINO FROM ZWPBPH INTO T_LINE WHERE

PBPINO = T_FAMILY-PBPAPINO.

ENDIF.

IF SY-SUBRC = 0.

SELECT SINGLE PBPINO PBPAPINO FROM ZWPBPH INTO T_TYPE WHERE

PBPINO = T_LINE-PBPAPINO.

ENDIF.

IF SY-SUBRC = 0.

READ TABLE T_MARA WITH KEY MATNR = T_ZWPBPH-PBPINO.

IF SY-SUBRC = 0.

IF T_MARA-SPART <> T_TYPE-PBPAPINO+1(2).

MOVE-CORRESPONDING T_MARA TO T_ALV.

IF SY-SUBRC = 0.

MOVE T_ZWPBPH-PBPINO TO T_ALV-MATNR.

MOVE T_TYPE-PBPAPINO+1(2) TO T_ALV-SPART1.

APPEND T_ALV.

CLEAR T_ALV.

ENDIF.

ENDLOOP.

ENDIF.

SORT T_ALV BY MATNR.

call function 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = GD_REPID

i_callback_user_command = 'DISPLAY_DETAIL'

it_fieldcat = FIELDCATALOG_BASIC[]

i_save = 'X'

TABLES

t_outtab = T_ALV

EXCEPTIONS

program_error = 1

others = 2.

ENDFORM. "POPULATE_DATA

&----

*& Form DISPLAY_DETAIL

&----

text

----

-->UCOMM text
-->SELFIELD text

----

FORM DISPLAY_DETAIL USING UCOMM LIKE SY-UCOMM

SELFIELD TYPE SLIS_SELFIELD.

Select Material Number For Secondary List

REFRESH T_ALV_TEMP.

IF UCOMM = '&IC1'.

READ TABLE T_ALV INDEX SELFIELD-TABINDEX.

READ TABLE T_MARA WITH KEY MATNR = T_ALV-MATNR.

IF SY-SUBRC = 0.

MOVE-CORRESPONDING T_MARA TO T_ALV_TEMP.

APPEND T_ALV_TEMP.

CLEAR T_ALV_TEMP.

ENDIF.

CALL FUNCTION 'REUSE_ALV_LIST_DISPLAY'

EXPORTING

i_callback_program = GD_REPID

it_fieldcat = FIELDCATALOG_SECONDARY[]

TABLES

t_outtab = T_ALV_TEMP.

ENDIF.

ENDFORM. "DISPLAY_DETAIL

Former Member · ‎2006 Dec 04

Hi Priya,

I suppose, the code which i mentioned in my first reply works in this scenario. When you say "Every time i have to submit pbpapino value to zwpbph-pbpino to get next values", Are you getting different pbpino and pbpapino if check your code in debugging for select statements?

Also, if i am correct you want to check for division of MARA with PBPAPINO+1(2), then can't you do it like this?

IF T_MARA[] IS NOT INITIAL .

SELECT PBPINO PBPAPINO FROM ZWPBPH INTO TABLE T_ZWPBPH FOR ALL ENTRIES IN T_MARA WHERE PBPINO = T_MARA-MATNR.

ENDIF.

LOOP AT T_ZWPBPH.

READ TABLE T_MARA WITH KEY MATNR = T_ZWPBPH-PBPINO.

IF SY-SUBRC = 0.

IF T_MARA-SPART <> T_ZWPBPH-PBPAPINO+1(2).

MOVE-CORRESPONDING T_MARA TO T_ALV.

IF SY-SUBRC = 0.

MOVE T_ZWPBPH-PBPINO TO T_ALV-MATNR.

MOVE T_TYPE-PBPAPINO+1(2) TO T_ALV-SPART1.

APPEND T_ALV.

CLEAR T_ALV.

ENDIF.

ENDLOOP.

I suggest you to check the contents of t_model, t_product, t_family and t_type in debugging..i think they will remain same..

Hope this helps.

Regards,

Vivek

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My Account

My Account

Select statement