Cross Join in ABAP Open SQL

[Former Member]

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

[Former Member]

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.

endloop.

Where it_final has all the fields from tab1 and tab2.

Regards,

Ravi

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

[Former Member]

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

[Former Member]

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

[RichHeilman]

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

[Former Member]

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

[Former Member]

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.

endloop.

Where it_final has all the fields from tab1 and tab2.

Regards,

Ravi

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

[Former Member]

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.

[Former Member]

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.

[sridhar_k1]

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.