topic SELECT INTO CORRESPONDING in Application Development and Automation Discussions

SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 04:50:02 GMT

Dear all,

I need to perform data selection from tables, where some fields are needed from Table A and ALL fields are needed from Table B. And i falled into the dilema as below:

ZAAA - Table A

ZBBB - Table B


TYPES: BEGIN OF TY_DATA,
        CUST_N TYPE ZCUST,                   "field from Table A, actual technical name is CUST
        INV   TYPE ZINV.                     "field from Table A    
        INCLUDE STRUCTURE OF ZBBB.            "ALL fields from Table B
TYPES: END OF TY_DATA.
DATA: TA_DATA TYPE STANDARD TABLE OF TY_DATA.

SELECT * INTO CORRESPONDING TABLE TA_DATA
  FROM ZAAA AS A
  INNER JOIN ZBBB AS B
  ON A~MATNR = B~MATNR
  WHERE A~MATNR IN S_MATNR.

FYI, field CUST is apprearing in both Table A and B, so to avoid the syntax checking, i purposely change to CUST_N in TY_DATA declaration.

The purpose that i select the field(CUST) from both table is to perform comparison, i have no choice but has to do this.

Problem occurs, because in table TA_DATA field CUST_N returned blank value eventhough there is value in Table A. This is the side effect from INTO CORRESPONDING command where it will perform the actual technical field name checking before return the value to destination table.

I would like to seek your advice on how to manipulate the SQL statement(with same idea of INTO CORRESPONDING) in order to achieve my selection.

Please comment.

Thanks in advance.

Re: SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 05:20:45 GMT

use FOR ALL ENTRIES here for getting solution of ur problem and better performance..

See the below example..

SELECT matnr mtart

FROM mara

INTO TABLE t_mara

WHERE matnr IN s_matnr.

IF NOT t_mara[] IS INITIAL.

SELECT matnr maktx

FROM makt

INTO TABLE t_makt

FOR ALL ENTRIES IN t_mara

WHERE matnr EQ t_mara-matnr.

ENDIF.

Reward points if useful.

Thanks & Regards

ilesh 24x7

Re: SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 05:24:06 GMT

AAA - Table A

ZBBB - Table B

TYPES: BEGIN OF TY_DATA,

CUST_N TYPE ZCUST, "field from Table A, actual technical name is CUST

INV TYPE ZINV. "field from Table A

INCLUDE STRUCTURE OF ZBBB. "ALL fields from Table B

TYPES: END OF TY_DATA.

DATA: TA_DATA TYPE STANDARD TABLE OF TY_DATA.

SELECT * INTO CORRESPONDING TABLE TA_DATA

FROM ZAAA AS A

INNER JOIN ZBBB AS B

ON B_{MATNR = A}MATNR

WHERE A~MATNR IN S_MATNR.

WATCH OUT FOR DIS CHANE ,IT MIGHT HELP U OUT.

Re: SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 06:49:29 GMT

Hi,

First of all make sure that from which table you want the cust field.If you want it from A then do this way:

SELECT a~cust <Fl2> <Fl3> into TABLE TA_DATA

FROM ZAAA AS A

INNER JOIN ZBBB AS B

ON A_{MATNR = B}MATNR

WHERE A~MATNR IN S_MATNR.

and if its B then:

SELECT B~cust <fld2> <fld3> into TABLE TA_DATA

FROM ZAAA AS A

INNER JOIN ZBBB AS B

ON A_{MATNR = B}MATNR

WHERE A~MATNR IN S_MATNR.

It is always advisable not to use "into corresponding" statement as it has performance issues.

In your case,where you need just 3 fields,please write those fields from the tables you want to retrieve the value eg.

select a_{f1 b}f2 a~f3

into table itab

FROM ZAAA AS A

INNER JOIN ZBBB AS B

ON A_{MATNR = B}MATNR

WHERE A~MATNR IN S_MATNR.

Hope it is useful.

Thanks,

Sandeep.

Re: SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 06:53:09 GMT

Hi ric :s,

Thanks for your prompt reply. Perhaps i have tested FOR ALL ENTRIES, and the performance is bad due to the data being process is huge, 100K ++.

Just because of this, i decided to use INNER JOIN to improve 50% of the processing time that needed by FOR ALL ENTRIES.

If i need to stick with INNER JOIN for the sake of the performance, do you think i can get my problem solve with INTO CORRESPONDING as mentioned in my earlier post?

Please comment.

Thanks in advance.

Re: SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 06:59:46 GMT

Hi Sandeep,

Thanks for your reply.

For my case, i want the field CUST return from both Table A and B. And i always keep in mind not to use INTO CORRESPONDING. But for my case, i need 2 fields from Table A and ALL the fields from Table B. Furthermore FOR ALL ENTRIES did not performed well as expected(mentioned in my reply earlier.

Is there a way to achieve?

Please comment

Re: SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 07:09:04 GMT

Hi,

Then also you can do this way:

Since you need the same field from both the tables,then why not declare 2 separate fields for the same field in the internal table.

Declare the internal table with the fields from both the tables instead of just one:

eg. let itab have a field y_cust for table A and field cust for table B.

Then use

select a_{cust b}cust a_{fld1 b}fld2 and so on..

into table itab

from zztab as a

inner join ......so on...

Please remember to keep the fields in itab in the same order as you select in the query.

Hope it is useful.

Thanks,

Sandeep

Re: SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 09:12:24 GMT

Hi Sandeep,

Thanks for your opinion. However i have came across with your suggestion by perform an explicit fields selection in SQL statement(select a_{cust b}cust a_{fld1 b}fld2) instead of Select * . This is very very tedious and my SQL statement is very long, because my Table B having 100++ fields and i need to extract all the fields. plus 2 fields from Table A.

Furthermore if the structure of Table B change, then i may need to maintain the code accordingly. Since this is higher cost for maintenance, that's why i drop this options.

Can you think of any other options? Please comment.

Re: SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 09:16:41 GMT

Hi,

The better way is use INTO CORRESPONDING

There is no harm considering the large number of fields in your table and writing them individually will definately be a maintainance issue.

The other option is you can create a DB view based on these table and then select data from that view.

Regards,

Atish

Re: SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 13:16:29 GMT

Hi Atish,

Thanks for your post, however DB view is not approved by management. Could you please further comment on how should i fix the problem via the SQL statement?

Thanks in advance.

Re: SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 14:33:53 GMT

Hi,

Then you can use the SQL in the same way as yuo are using and shown in post above.

Regards,

Atish

Re: SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 15:27:53 GMT

Hi Atish,

My way can not achieve the correct result. As i need to select a same field CUST from both Table A and B. The result return BLANK from Table A since i declared the field name as CUST_N.

Furthermore full table fields are needed from Table B, hence i used INTO CORRESPONDING command where i do not need to explicitly define the each field name since Table B contain 100++ fields.

Any ideas?

Re: SELECT INTO CORRESPONDING

rahul2000 — Sun, 28 Oct 2007 15:33:38 GMT

I want to make a couple of points here:

1:-Try avoiding into corresponding as it creates performance issues.

2:-Also inner joins statments may or may not work.

Thay might work fine in DEV and QA but in PRD with the large database,when the inner join statments goes and hits the database it is more likey to cause errors.

Re: SELECT INTO CORRESPONDING

Former Member — Sun, 28 Oct 2007 15:35:14 GMT

Hi Rahul,

Thanks for your suggestion i will keep that in mind, in the mean time perhaps can you comment on the issue above?

Thanks in advance.

Re: SELECT INTO CORRESPONDING

former_member656234 — Sun, 28 Oct 2007 15:40:18 GMT

Hi,

You can try this...

SELECT A_{CUST A}INV B~* INTO TABLE TA_DATA FROM ZAAA AS A

INNER JOIN ZBBB AS B

ON A_{MATNR = B}MATNR

WHERE A~MATNR IN S_MATNR.

cya,

Dimas

Re: SELECT INTO CORRESPONDING

Former Member — Mon, 29 Oct 2007 14:49:09 GMT

Hi Dimas ,

Thanks for your suggestion, perhaps i did tried on

B~*

But it can not pass through the syntax check where system returned error "Unkown column name B~*. not determined until runtime, you can not specify a field list."

Please comment.