topic Re: SELECT * in a join using Open SQL in Application Development and Automation Discussions

SELECT * in a join using Open SQL

alejandro_bindi — Wed, 09 May 2007 14:01:57 GMT

In a table join, how to select all fields from table1 (without having to write them one by one) and only some of table2?

I was trying something like this but it doesn't work:

DATA: BEGIN OF i_data OCCURS 0.
        INCLUDE STRUCTURE zt1.
DATA:   field4 TYPE zt2-field4,
      END OF i_data.

SELECT z1~* z2~field4
INTO TABLE i_data
FROM zt1 AS z1
  INNER JOIN zt2 AS z2
    ON z1~field1 = z2~field1 AND
       z1~field2 = z2~field2 AND
       z1~field3 = z2~field3
WHERE...

Points will be rewarded for helpful answers.

Many thanks

Re: SELECT * in a join using Open SQL

andreas_mann3 — Wed, 09 May 2007 14:10:35 GMT

you must use following syntax:

DATA : BEGIN OF fields OCCURS 0,
       name LIKE dd03l-fieldname,
       END OF fields.

fill table fields from DD02L with tables z1 and z2:

concatenate Z1~ dd03l-fieldname into fields-name.



SELECT (fields) 
INTO TABLE i_data
FROM zt1 AS z1
  INNER JOIN zt2 AS z2
    ON z1~field1 = z2~field1 AND
       z1~field2 = z2~field2 AND
       z1~field3 = z2~field3
WHERE...

Re: SELECT * in a join using Open SQL

alejandro_bindi — Wed, 09 May 2007 14:22:34 GMT

Thanks Andreas, so the only way is that (building dynamically the fields list)?

Re: SELECT * in a join using Open SQL

Former Member — Wed, 09 May 2007 14:22:37 GMT

Hi Alejandro,

Select A_{* B}matnr B_{werks into table itab from maar as a inner join marc as B on A}matnr = B~matnr

where A~matnr in s_matnr.

rewad points if helpful.

Regards,

Hemant

Re: SELECT * in a join using Open SQL

ferry_lianto — Wed, 09 May 2007 14:33:45 GMT

Hi,

Please try this.


DATA: BEGIN OF I_DATA OCCURS 0.
        INCLUDE STRUCTURE ZT1.
DATA:   FIELD4 TYPE ZT2-FIELD4,
      END OF I_DATA.
 
SELECT *
INTO CORRESPONDING FIELDS OF I_DATA
FROM ZT1 AS Z1
INNER JOIN ZT2 AS Z2
   ON Z1~FIELD1 = Z2~FIELD1 AND
      Z1~FIELD2 = Z2~FIELD2 AND
      Z1~FIELD3 = Z2~FIELD3
WHERE <condition>
  APPEND I_DATA.
ENDSELECT.

Regards,

Ferry Lianto

Re: SELECT * in a join using Open SQL

alejandro_bindi — Wed, 09 May 2007 14:36:10 GMT

Hemant, your code is the same as the one i've tried and returns the same error: "Unknown column name "A~*". not determined until runtime, you cannot specify a field list.

Re: SELECT * in a join using Open SQL

alejandro_bindi — Wed, 09 May 2007 14:55:53 GMT

Hi Ferry, i'd like to avoid INTO CORRESPONDING, however yours is a valid alternative also.

I rewarded points. If someone has other alternatives please add them.