SQL Joins with distinct columns

Consider two (possibly temporary) tables X and Y each with many columns. In DB2, if I make a cross join like this,

select *
from X, Y
where X.key=Y.key

I get the error SQLCODE=-153, SQLSTATE=42908, which complains about duplicate column names (surely, "key" occurs twice). The help page here suggests to specify the column names exactly, but this is cumbersome due to the large number of columns.

Is there a way to retain the compact syntax and get the full data? Or asked differently, is it possible to use only distinct rows, i.e. let "key" appear only once?

Answers


Not that i know of. You could make this:

select X.*, Y.Field1, Y.Field2, ...from X, Y where X.key=Y.key

So you get all the fields from X, but you need to explicit name the field on Y.


Need Your Help

API java 5 and more: should I return an array or a Collection?

java arrays api generics collections

In the spirit of Best Practices: Always return a ____, never a ____, I face a similar question in my upcoming migration from JDK1.4.2 to JDK5 and more. (Yes, I know, JDK1.4.2 is EOL! ;-) ).