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?
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.