Accessing same named columns in different database tables

[wgilbert]

I have a DataSource which access three database tables. Two of the tables have columns which have the same name. Obviously the following is ambiguous and does not work but illustrate the problem

<field type="integer"   name="ARTICLES" tableName="ALL" hidden="true"/>
<field type="integer"   name="ARTICLES" tableName="TOP30" hidden="true"/>

On a lark, which I know is wrong, I tried the following which created a valid SQL select but the resulting Map in the DSRequest records did not contain an "ARTICLES as T_ARTICLES" or a "T_ARTICLES" Map.Entry.

<field type="integer"   name="ARTICLES" tableName="ALL" hidden="true"/>
<field type="integer"   name="ARTICLES as T_ARTICLES" tableName="TOP30" hidden="true"/>

Other than creating a go-between-view for the TOP30 table is there a way to specify an "AS" in the field XML or the 'operationBindings' definition.

Here is my 'operationBindings' XML

    <operationBindings>
        <operationBinding operationType="fetch">
            <tableClause>MAIN, ALL, TOP30</tableClause>
            <whereClause>
               MAIN.ALL_ID = ALL.ID AND MAIN.TOP30_ID = TOP30.ID 
            AND  ($defaultWhereClause)</whereClause>
        </operationBinding>
    </operationBindings>

Am using the Power Edition's FilterBuilder component to create an wonderfully flexible, user-driven table.

Would also be willing to fetch the TOP30 values from within my DMI, but am not sure how to execute and additional SQL queries from inside there. However, this would be useful to know in some cases.

[Isomorphic]

You can use DataSourceField.customSelectExpression to provide an "as" expression as you attempted to the do with the name attribute. There's an overview of this and similar SQL customizations in the QuickStart Guide (SQL & SQL Templating sections under Server Framework).

To execute a second SQL query (not needed for this case) you can simply create another DSRequest and call execute() on it to obtain a DSResponse. There's a brief overview of this and similar DMI business logic under the DMI sections of the Server Framework chapter.

[will-gilbert]

Thanks for prompt and helpful response.

For future readers of this thread here is the resolution. N.B. I had to remove the "tableName='TOP30'" attribute from the field definition. This showed up clearing by logging the generated SQL statement as enabled below.

<field type="integer"   name="T_ARTICLES"
       customSelectExpression="TOP30.ARTICLES"
       hidden="true"/>

This also helps in the llog4j.isc.config.xml file during development.

<!-- Set to 'DEBUG' to see SQL statements -->
<category name="com.isomorphic.sql.SQLDataSource">
  <priority value="DEBUG" />
  <appender-ref ref="${log4j.logger}"/>
</category>