Spotfire: The AS-part of "SELECT xxx AS yyy" in an information link's SQL query cannot be used to rename a column during import into Spotfire.

Spotfire: The AS-part of "SELECT xxx AS yyy" in an information link's SQL query cannot be used to rename a column during import into Spotfire.

When Spotfire generates SQL (original SQL), it always uses an AS statement to remove underscores. However, when the information link is returned, the original column name remains. Similarly, when using the AS statement in Modified SQL, the new column alias is not returned. It is expected that if the query says to return ITEM_NAME AS "NAME", the column would be titled "NAME", but that is not what it is doing.

Resolution:

Information on the information-link SQL query editing limits is included below. One of these limits is that the "AS" part of "SELECT xxx AS yyy" cannot be used to rename a column during import into Spotfire.   

The following are among the reasons for this design limit: 

- To prevent accidentally providing the same name for multiple columns in the same data table. 

- To prevent altering the order of the columns being imported (which violates the query's runtime conditions).     

The original (auto-generated) SQL query's use of "SELECT xxx AS yyy" to remove underscores from column names in the data table is needed to ensure that any use of the SQL keyword "LIKE" (during the JDBC drivers' own internal data processing) won't interpret underscores as wildcard characters in their working version of the column names. (The SQL92 "LIKE" keyword uses '_' and '%' as wildcard characters if they haven't been "escaped" to force their treatment as ordinary characters.)  You can find example discussions of this online, using a Google search on the following search string: 

      JDBC underscore

The original column names are stored separately as part of the SQL query's runtime conditions, and they are assigned to the imported columns after all of the JDBC drivers' processing is complete. This is why user-defined use of "SELECT xxx AS yyy" has no effect on the imported column names.  It is also the reason for the prohibition on changing the order of the imported columns. Column order is used in assigning the original column names back to their correct columns.

   =================================================== 

To edit an information link's SQL Query, click the "SQL..." button, and use the "Edit SQL" dialog to modify the auto-generated SQL as needed, within the limits outlined below. You will need to keep these limits in mind, whenever you edit an information link's auto-generated SQL. 

  ----------- 

Note: The modified SQL is not validated before execution. For this reason, you should not: 

-alter the name of a column (the AS-part of SELECT xxx AS yyy) 

-alter the number of columns returned 

-alter the datatype of columns returned

-alter the order of columns returned 

-remove <temporary_result_#> from the FROM-clause in information links that go against multiple data sources 

-remove the trailing WHERE <conditions>, as it will be replaced by any conditions applied at runtime

 -----------  

This list is copied from the help file titled "Editing the SQL of an Information Link", which you can find by selecting "Help" from the installed client's main menu, followed by selecting the "Search" tab, then typing "edit SQL" and pressing the "List Topics" button.  You can find more information on this topic in that help file and others it is linked to. 

===================================================