topic Re: using select stat. in Application Development and Automation Discussions

using select stat.

Former Member — Tue, 06 Feb 2007 04:38:46 GMT

Hi,

How can I use select stat. rather than join to join to tables so that I have all the fields from both tables appearing in my output.

Re: using select stat.

Former Member — Tue, 06 Feb 2007 04:42:34 GMT

Hi,

Performance wise it is better to use JOIN that to use nested selects.

Hope this helps.

Reward if helpful.

Regards,

Sipra

Re: using select stat.

Former Member — Tue, 06 Feb 2007 04:44:22 GMT

Hi Shaheen ,

In that cae you will have to use a nested select , i.e. one select statement inside another select.

Regards

Arun

Re: using select stat.

Former Member — Tue, 06 Feb 2007 04:45:32 GMT

Use can use for all entries.

select * from vbak into table itab1.

select * from vbap into table itab2

for all entries in itab1

where vbeln = itab1-vbeln.

loop at itab2.

read table itab1 with key vbeln = itab2-vbeln.

if sy-subrc = 0.

move-corresponding itab1 to itab3.

endif.

move-corresponding itab2 to itab3.

append itab3.

clear itab3.

endloop.

now itab3 contains data from both VBAP & VBAK table.

Re: using select stat.

Former Member — Tue, 06 Feb 2007 04:46:27 GMT

Hi,

You can use INNER JOIN...

Example

PARAMETERS: P_EBELN LIKE EKKO-EBELN.

SELECT A_{EBELN B}EBELP B_{NETPR B}MENGE

INTO TABLE ITAB

FROM EKKO AS A INNER JOIN EKPO AS B

ON A_{EBELN = B}EBELN

WHERE A~EBELN = P_EBELN.

LOOP AT ITAB.

WRITE: / ITAB.

ENDLOOP.

Thanks,

Naren

Re: using select stat.

Former Member — Tue, 06 Feb 2007 04:54:29 GMT

shaeen,

Select required fields from table1 into table itab1

where condituions for first table.

if not itba1[] is initial.

select required fields from table2 into table itab2

for all entries in itab1

where keyfields = itab1-field.......

endif.

if you have duplicate values .

Delete adjusent duplicates from itab1 comparing your key fields.

sort : itab1 by key fields,

itab2 by key fields.

LOOP AT ITAB1

if you have multiple records use

loop at itab2 where keyfield = itab1-keyfield

Move : required values for itab1 and itab2 to i_final .

endloop.

if you have single record..

read table itab2 with key mainfield = itab1-keyfield binary serach.

if sy-subrc eq 0.

Move : required values for itab1 and itab2 to i_final .

endif.

ENDLOOP.

Pls. reward if useful

Re: using select stat.

Former Member — Tue, 06 Feb 2007 05:20:50 GMT

Types: begin of str1_tab,

lgnum type lgnum,

ivnum type LVS_IVNUM,

ivpos type LVS_IVPOS,

lgtyp type lgtyp,

lgpla type lgpla,

idatu type LVS_IDATU,

uname type LINK_UNAME,

end of str1_tab.

Data: it_tab1 type table of str1_tab,

wa_tab1 like line of it_tab1,

wa_linp type linp,

wa_link type link.

DATA: it_tab3 TYPE TABLE OF str1_tab,

wa_tab3 LIKE LINE OF it_tab3.

select lgnum ivnum lgtyp uname FROM link INTO CORRESPONDING FIELDS OF

TABLE it_tab1 where lgnum = pa_lgnum AND ivnum IN s_ivnum.

select lgnum ivnum lgpla idatu from linp into corresponding

fields of table it_tab3 for all entries in it_tab1 where lgnum = pa_lgnum and ivnum in s_ivnum.

LOOP AT it_tab3 INTO wa_tab3.

READ TABLE it_tab1 INTO wa_tab1 WITH

KEY lgnum = wa_tab3-lgnum

ivnum = wa_tab3-ivnum.

IF sy-subrc = 0.

move-corresponding it_tab1 to it_tab3.

ENDIF.

append it_tab3.

clear it_tab3.

endloop.

can you please chech the above code.

Re: using select stat.

Former Member — Tue, 06 Feb 2007 05:27:59 GMT

Types: begin of str1_tab,
lgnum type lgnum,
ivnum type LVS_IVNUM,
lgtyp type lgtyp,
uname type LINK_UNAME,
ivpos type LVS_IVPOS,
lgpla type lgpla,
idatu type LVS_IDATU,

end of str1_tab.


Types: begin of str2_tab,
lgnum type lgnum,
ivnum type LVS_IVNUM,
lgpla type lgpla,
idatu type LVS_IDATU,
uname type LINK_UNAME,
ivpos type LVS_IVPOS,
lgtyp type lgtyp,

end of str2_tab.


Data: it_tab1 type table of str1_tab,
wa_tab1 like line of it_tab1,
wa_linp type linp,
wa_link type link.

DATA: it_tab3 TYPE TABLE OF str2_tab,
wa_tab3 LIKE LINE OF it_tab3.

select lgnum ivnum lgtyp uname FROM link INTO 
TABLE it_tab1 where lgnum = pa_lgnum AND ivnum IN s_ivnum.

select lgnum ivnum lgpla idatu from linp into  table it_tab3 for all entries in it_tab1 where lgnum = pa_lgnum and ivnum in s_ivnum.
sort it_itab1.
LOOP AT it_tab3 INTO wa_tab3.

READ TABLE it_tab1 INTO wa_tab1 WITH
KEY lgnum = wa_tab3-lgnum
ivnum = wa_tab3-ivnum binary search .
IF sy-subrc = 0.
move-corresponding it_tab1 to it_tab3.
ENDIF. 
append it_tab3.
clear it_tab3.
endloop.

Re: using select stat.

Former Member — Tue, 06 Feb 2007 05:29:09 GMT

Types: begin of str1_tab,

lgnum type lgnum,

ivnum type LVS_IVNUM,

ivpos type LVS_IVPOS,

lgtyp type lgtyp,

lgpla type lgpla,

idatu type LVS_IDATU,

uname type LINK_UNAME,

end of str1_tab.

Data: it_tab1 type table of str1_tab,

wa_tab1 like line of it_tab1,

wa_linp type linp,

wa_link type link.

DATA: it_tab3 TYPE TABLE OF str1_tab,

wa_tab3 LIKE LINE OF it_tab3.

****Declare one more table.

DATA: it_tab4 TYPE TABLE OF str1_tab,

wa_tab4 LIKE LINE OF it_tab4.

select lgnum ivnum lgtyp uname FROM link INTO CORRESPONDING FIELDS OF

TABLE it_tab1 where lgnum = pa_lgnum AND ivnum IN s_ivnum.

if not it_itab1[] is initial.

sort it_itab1 by lgnum.

select lgnum ivnum lgpla idatu from linp into corresponding

fields of table it_tab3 for all entries in it_tab1 where lgnum = pa_lgnum and ivnum in s_ivnum.

endif.

LOOP AT it_tab3 INTO wa_tab3.

READ TABLE it_tab1 INTO wa_tab1 WITH

KEY lgnum = wa_tab3-lgnum

ivnum = wa_tab3-ivnum.

IF sy-subrc = 0.

move-corresponding wa_tab1 to it_tab4.

ENDIF.

move-corresponding wa_itab3 to it_itab4.

append it_tab4.

clear it_tab4.

endloop.

now itab4 contains all data.

Re: using select stat.

Former Member — Tue, 06 Feb 2007 05:41:56 GMT

I'm getting this error---->

THE WHERE CONDITION DOES NOT REFER TO THE FOR ALL ENTRIES TABLES

Re: using select stat.

Former Member — Tue, 06 Feb 2007 05:49:36 GMT

select lgnum ivnum lgpla idatu from linp into table it_tab3 for all entries in it_tab1 where lgnum = it_tab1-lgnum and ivnum = it_tab1- ivnum .

Re: using select stat.

Former Member — Tue, 06 Feb 2007 06:57:44 GMT

I'm still not getting the LGPLA AND IDATU recods of the linp tables.

Re: using select stat.

Former Member — Tue, 06 Feb 2007 07:04:23 GMT

select lgnum ivnum lgtyp uname FROM link INTO CORRESPONDING FIELDS OF

TABLE it_tab1 where lgnum = pa_lgnum AND ivnum IN s_ivnum.

*******See the mod below

if not it_itab1[] is initial.

sort it_itab1 by lgnum ivnum.

select lgnum ivnum lgpla idatu from linp into corresponding

fields of table it_tab3 for all entries in it_tab1 where lgnum = pa_lgnum and

ivnum = itab1-ivnum.

endif.

sort it_tab1 by lgnum ivnum.

LOOP AT it_tab3 INTO wa_tab3.

READ TABLE it_tab1 INTO wa_tab1 WITH

KEY lgnum = wa_tab3-lgnum

ivnum = wa_tab3-ivnum binary search.

IF sy-subrc = 0.

move-corresponding wa_tab1 to it_tab4.

ENDIF.

move-corresponding wa_itab3 to it_itab4.

append it_tab4.

clear it_tab4.

endloop.

Re: using select stat.

Former Member — Tue, 06 Feb 2007 07:15:02 GMT

Instead of code

LOOP AT it_tab3 INTO wa_tab3.

READ TABLE it_tab1 INTO wa_tab1 WITH

KEY lgnum = wa_tab3-lgnum

ivnum = wa_tab3-ivnum binary search.

IF sy-subrc = 0.

move-corresponding wa_tab1 to it_tab4.

ENDIF.

move-corresponding wa_itab3 to it_itab4.

append it_tab4.

clear it_tab4.

endloop.

write the below code:

data: p_index like sy-tabix.

LOOP AT it_tab3 INTO wa_tab3.

p_index = sy-tabix.

READ TABLE it_tab1 INTO wa_tab1 WITH

KEY lgnum = wa_tab3-lgnum

ivnum = wa_tab3-ivnum binary search.

IF sy-subrc = 0.

move-corresponding wa_tab1 to wa_itab3.

ENDIF.

modify it_tab3 from wa_itab3 index p_index transporting lgtyp uname.

endloop.

In the end it_tab3 will have all the required fields.

Thanks.

Re: using select stat.

Former Member — Tue, 06 Feb 2007 07:23:34 GMT

Hello,

Perfer a INNER JOIN instead of nested select atatements which can reduce the perfomance.

see the 'Tips & Tricks' in the transaction se30.

Regards,

Shehryar Dahar

Re: using select stat.

Former Member — Tue, 06 Feb 2007 07:43:46 GMT

Yes that's exactly what i'm doing but I don't know why i'm not getting those 2 feilds records.

Re: using select stat.

jayanthi_jayaraman — Tue, 06 Feb 2007 08:04:34 GMT

Hi,

There are two ways to handle the problem.

1. For all entries

Types: begin of str1_tab,

lgnum type lgnum,

ivnum type LVS_IVNUM,

ivpos type LVS_IVPOS,

lgtyp type lgtyp,

lgpla type lgpla,

idatu type LVS_IDATU,

uname type LINK_UNAME,

end of str1_tab.

Data: it_tab1 type table of str1_tab,

wa_tab1 like line of it_tab1.

DATA: it_tab3 TYPE TABLE OF str1_tab,

wa_tab3 LIKE LINE OF it_tab3.

select lgnum ivnum lgtyp uname FROM link INTO CORRESPONDING FIELDS OF

TABLE it_tab1 where lgnum = pa_lgnum AND ivnum IN s_ivnum.

if sy-subrc eq 0.

select lgnum ivnum lgpla idatu from linp into corresponding

fields of table it_tab3 for all entries in it_tab1 where lgnum = itab1-lgnum and ivnum = itab1-ivnum.endif.

LOOP AT it_tab3 INTO wa_tab3.

clear wa_tab1.

READ TABLE it_tab1 INTO wa_tab1 WITH

KEY lgnum = wa_tab3-lgnum

ivnum = wa_tab3-ivnum.

IF sy-subrc = 0.

move-corresponding it_tab1 to it_tab3.

modify it_tab3 from wa3 transporting lgtyp uname where lgnum = wa_tab3-lgnum

and ivnum = wa_tab3-ivnum.ENDIF.

clear it_tab3.

endloop.

2. Use inner join

Types: begin of str1_tab,

lgnum type lgnum,

ivnum type LVS_IVNUM,

ivpos type LVS_IVPOS,

lgtyp type lgtyp,

lgpla type lgpla,

idatu type LVS_IDATU,

uname type LINK_UNAME,

end of str1_tab.

Data: it_tab1 type table of str1_tab,

wa_tab1 like line of it_tab1.

select link_{lgnum link}ivnum link_{lgtyp link}uname linp_{lgpla linp}idatu FROM link inner join linp on link_{lgnum = linp}lgnum

and link_{ivnum = link}ivnum

into TABLE it_tab1 where lgnum = pa_lgnum AND ivnum IN s_ivnum.

Now itab1 will contain all the values