JDBC, SQL-SELECT: Aliases for multiple columns?

[former_member192710]

Folks;

using SQL, JDBC with MaxDB 7.6, I am used to doing something like

SELECT A AS FOO_A, B AS FOO_B, C AS BAR_C FROM FOO, BAR WHERE ...

in order to make table columns have names sometimes more "meaningful" than in the actual database scheme. In a give environment, I will have to do a

SELECT * FROM FOO,BAR WHERE ...

with the difficulty that FOO and BAR contain columns of the same name, yet they both contain too many columns to actually enumerate/alias each one of them manually. Playing around trying something like

SELECT FOO.* AS FOO_*, BAR.* AS BAR_* FROM FOO,BAR WHERE ...

of course didn't work, and browsing the web I found there is no "standard SQL" way of doing something like this. Using

java.sql.ResultSet

provides a

getMetaData()

method which allows for determining the "table name" for a given column in the fetched result set, but unfortunately, the outcome returned by this method always seems to be empty. So to ask:

- Is there any way in MaxDB-SQL to allow for "aliasing multiple columns"?

- Alternatively, is there a way to make an SQL query joining multiple tables in MaxDB issued through JDBC generally return table columns in a <tablename>.<columnname> rather than just <columnname> style?

Thanks a bunch in advance, best regards.

Kristian

[lbreddemann]

HI Kristian,

afaik the resultset.getMetaData() method should provide a MetaData-object that in turn implements methods like getColumnCount() or getColumnName() etc...

Can you provide a piece of code to demonstrate that this is not working?

I just tested DB Studio and Squirrel SQL (both use JDBC to connect to the database) and both are able to correctly figure out the structure of the result set for a query like yours.

Anyhow, the whole requirement is not supposed to be supported.

In fact, it's already a kind of 'glitch' that results with non-unique columnnames are actually produced, since relational theory forbids such results.

That's also the reason for why you cannot further work with such a resultset (e.g. use it in a join).

Therefore, no workaround in place here.

If you´re going to have a query like this rather often, you may think about using views and define the aliases there only once.

And besides all this, if you've got many duplicate column names it might also be a unlucky data design.

best regards,

Lars