topic Re: About SQL Query using joins in Application Development and Automation Discussions

About SQL Query using joins

Former Member — Fri, 15 Oct 2010 07:44:32 GMT

Hi,

I want to write a SQL statement using Joins. The base table "A" has about 100 fields and the join table "B" a few. I would like to fetch all fields from table "A" and a few from table "B".

Is there a way to write a query wherein I can use "Select * " for table "A" rather than writing all 100 field name in the query? Please keep in mind that I want to use a Join.

Thanks.

Re: About SQL Query using joins

ThomasZloch — Fri, 15 Oct 2010 07:52:10 GMT

You can use

SELECT * INTO CORRESPONDING FIELDS OF struc FROM ... JOIN ...

and declare struc with the required fields, e.g. include the entire structure of table A and only the desired fields of table B. Also works with INTO CORRESPONDING FIELDS OF TABLE itab.

Make sure there are no duplicate field names in table A and B with the exception of the fields providing the JOIN condition.

No, INTO CORRESPONDING does not cause performance problems.

Thomas

Re: About SQL Query using joins

Former Member — Fri, 15 Oct 2010 08:12:15 GMT

Hi Thomas,

I am using a join.

For eg:

Select A_{* B}fld1 B~fld2.... from A

Join B On B~fld1 = Afld1

into table itab

where...

Is there is way to write A~* or something which will fetch all fields from table A?

Thanks.

Re: About SQL Query using joins

Former Member — Fri, 15 Oct 2010 08:39:50 GMT

Hi,

you can not use A~* or anything similar. But you can specify a list of columns to be selected dynamically. Like in this SAP help example:

PERFORM my_select USING `CITYFROM`. 
PERFORM my_select USING `CITYTO`. 

FORM my_select USING l_group TYPE string. 
  DATA: l_columns   TYPE TABLE OF string, 
        l_container TYPE string, 
        l_count     TYPE i. 
  APPEND l_group TO l_columns. 
  APPEND `count( * )` TO l_columns. 
  SELECT (l_columns) 
         FROM spfli 
         INTO (l_container, l_count) 
         GROUP BY (l_group). 
    WRITE: / l_count, l_container. 
  ENDSELECT. 
ENDFORM.

For getting a list of table fields, you can use a selection from table DD03L.

Regards,

Adrian

Re: About SQL Query using joins

ThomasZloch — Fri, 15 Oct 2010 09:51:01 GMT

I understand you are using a join. By using SELECT * INTO CORRESPONDING, all the columns with matching names in the joined tables will be moved to the respective columns of your target structure or internal table, without the need to specifiy single columns, also no need for dynamic column list as suggested in the previous post.

Thomas

Re: About SQL Query using joins

Former Member — Fri, 15 Oct 2010 10:17:56 GMT

Hi Thomas,

but if there are some fields which are in both tables, SELECT * will be not sufficient. E.g. fields ERNAM, ERDAT, AENAM, AEDAT are such fields which can occur in more tables. In case that a target area will contain these fields, by * with addition INTO CORRESPONDING FIELDS OF, you are not able to specify from which table these values should be taken. In such case, it can be solved by specifying field list dynamically. If it is not that case SELECT * is sufficient. You should check intersection of common fields for tables you are joining. Thereafter you should decide if you can use SELECT *.

Maybe for better understanding, what exactly I am talking about, look at this example:

TYPES: BEGIN OF ts_data,
        ernam TYPE mara-ernam,
       END OF ts_data.

DATA: ls_data TYPE ts_data,
      ls_mara TYPE mara,
      ls_mchb TYPE mchb.

START-OF-SELECTION.

  ls_mara-matnr = '1'.
  ls_mchb-matnr = ls_mara-matnr.
  ls_mara-ernam = 'A'.
  ls_mchb-ernam = 'B'.

  INSERT mara FROM ls_mara.
  INSERT mchb FROM ls_mchb.


  SELECT SINGLE * FROM mara INNER JOIN mchb ON mara~matnr = mchb~matnr INTO CORRESPONDING FIELDS OF ls_data.

  ROLLBACK WORK.

You as a developer are not able to tell from which table common fields should be selected. But maybe this is really too specific case...

Regards,

Adrian

Re: About SQL Query using joins

ThomasZloch — Fri, 15 Oct 2010 10:30:03 GMT

Adrian, that's what I meant with my statement in my first post:

Make sure there are no duplicate field names in table A and B with the exception of the fields providing the JOIN condition.

but your explanation is much more detailed of course. Your dynamic field list can be a workaround for such cases.

Thomas