topic Re: Cross Join in ABAP Open SQL in Application Development and Automation Discussions

Cross Join in ABAP Open SQL

Former Member — Wed, 31 May 2006 15:19:20 GMT

Hi guys!

I need to implement a CROSS JOIN (cartesian product) based on two tables. To my understanding there is no construct to implement a cross join in OPEN SQL. So the question is first is this correct? And secondly in which way would you then implement a cross join of two tables with the constructs at hand in ABAP?

I have come up with the following solution of cross joining two tables, but would like to know if there is a better (not so ugly way).

Example:

SELECT * FROM tab1 AS t1 JOIN tab2 AS t2 ON t1_{mandt = t2}mandt INTO CORRESPONDING FIELDS OF itab.

Note that this only works if the tables tab1 and tab2 is client dependent.

Regards,

Christian

Re: Cross Join in ABAP Open SQL

Former Member — Wed, 31 May 2006 15:25:28 GMT

Hi Christian,

What do you exactly mean by cross join?

I don't find anything ugly about your statement..;-)..

Thats the way to do it and moreover it doesn't make any sense to pull the data from two disjoint tables(Which have no field in common(MANDT is one such common field for all client dependent tables).

Regards,

Ravi

Re: Cross Join in ABAP Open SQL

Former Member — Wed, 31 May 2006 15:25:39 GMT

Hi,

Well if you want to join two tables with out any condition (other than MANDT), then I guess you are on the right track.

Else, work around could be to fetch the data from the two tables indivudally into internal tables and then loop at one of the internal table and then loop at the table without any conditions.

Regards,

Ravi

Note :Please mark the helpful answers

Re: Cross Join in ABAP Open SQL

RichHeilman — Wed, 31 May 2006 15:25:44 GMT

If cross joins are the same as inner joins, then yes you can do that.

Select * into corresponding fields of table itab
             from mara
                  inner join marc
                       on mara~matnr = marc~matnr
                                 where matnr in s_matnr.

Regards,

Rich Heilman

Re: Cross Join in ABAP Open SQL

Former Member — Wed, 31 May 2006 15:34:01 GMT

Hi and thanks alot for the quick answers!

However, what I mean with a cross join is a join without condition. Which will result in the cartesian product (all combinations) being selected from tab1 and tab2.

This would be useful for selecting all possible material / location combinations based on a table with materials and a table with locations.

Is there no better way of doing this than using the mandt column to implement a "always true" condition as I described above?

Many thanks

/ Christian

Re: Cross Join in ABAP Open SQL

Former Member — Wed, 31 May 2006 15:38:35 GMT

Hi ,

i think there no use doing cross join.

what u can do is select data in 2 different internal tables.

in this way u will have all the data but in 2 different internal tables.

it will space effective.

later in ur program u can use read statements.

i dont know why u want cross join.

Regards.

Re: Cross Join in ABAP Open SQL

Former Member — Wed, 31 May 2006 15:40:29 GMT

Hi Christian,

The other way is to go for two different select statements.

select * from tab1 into table it_tab1 where <where con>.

select * from tab2 into table it_tab2 where <where con>.

loop at it_itab1.

loop at it_itab2.

move-corresponding it_itab1 to it_final.

move-corresponding it_itab2 to it_final.

append it_final.

clear it_final.

endloop.

Where it_final has all the fields from tab1 and tab2.

Regards,

Ravi

P.S: Don't know how ugly is this..;-)..

Re: Cross Join in ABAP Open SQL

Former Member — Wed, 31 May 2006 16:08:51 GMT

Hi christian,

declare it_final with all the fiels in tables itab1 and itab2.

select * from itab1 into corresponding fields of table it_final.

if sy-subrc = 0.

select * from itab2 into corresponding fields of table it_final.

endif.

loop at it_final.

write:/ it_final-fd1,...

endloop.

hope this helps.

Do reward if helpful.

regards,

keerthi.

Re: Cross Join in ABAP Open SQL

sridhar_k1 — Thu, 01 Jun 2006 01:17:49 GMT

Christian,

There's no cross join construct in abap. Join statement results in a cartesin product rarely because of completely obsolete statistics missing selection or join conditions.

Try the following join to get result similar to cartesian product

select tab1_{location tab2}matnr into itab

from tab1 join tab2 on tab1_{location ne tab2}matnr.

if you really want to implement cross join, use native sql, try the following code,

beware of cartesian products, Basis folks doesn't like em....

data: begin of itab occurs 0,

loc like tab1-sloc

matnr like tab2-matnr,

end of itab.

exec sql performing append_itab.

select a.loc, b.matnr

into :itab

from tab1 a

cross join tab2 b

endexec.

form append_itab.

append itab.

endform.

Regards

Sridhar.