SAP HANA: Table Selector fails on tables with quote in column OR dublicate pretty names

SAP HANA has columns technical names which is German abbreviations.
Each column also has a “pretty” name associated.

Last year?, KNIME made a super feature, to show the pretty names instead of the technical. This is the most amazing feature and a GREAT help.

We found 2x issues, where this is not working in DB Table Selector.

  • Quote " in column name in Table
  • Dublicate “Pretty columns names” in Table

Example 1 - Quote " in column name in Table
image

Example 2 - Dublicate “Pretty columns names” in Table
image

SAP DBTech JDBC: [268] (at 22): column ambiguously defined
image

Looking here:
image

image

My quick fix is a component node

SELECT SCHEMA_NAME, VIEW_NAME, POSITION, COLUMN_NAME, COMMENTS, DATA_TYPE_NAME, CS_DATA_TYPE_NAME, LENGTH
FROM VIEW_COLUMNS
WHERE SCHEMA_NAME = '_SYS_BIC'
AND VIEW_NAME = '$${SVIEW_NAME}$$'
ORDER BY VIEW_NAME, POSITION

// Your custom imports:
import java.util.*;
import java.util.regex.*;
import java.util.stream.Collectors;

// system variables
public class JSnippet extends AbstractJSnippet {
  // Fields for input columns
  /** Input column: "COMMENTS" */
  public String c_COMMENTS;
  /** Input column: "COLUMN_NAME" */
  public String c_COLUMN_NAME;
  /** Input column: "VIEW_NAME" */
  public String c_VIEW_NAME;
  /** Input column: "DATA_TYPE_NAME" */
  public String c_DATA_TYPE_NAME;
  /** Input column: "LENGTH" */
  public Integer c_LENGTH;

  // Fields for output columns
  /** Output column: "SQL" */
  public String out_sql;

// Your custom variables:
List<String> allSql = new ArrayList<>();
List<String> allC = new ArrayList<>();
String c;
String cNew;
int ci = 2;
String sql;

// expression start
    public void snippet() throws TypeException, ColumnException, Abort {
// Enter your code here:
c = c_COMMENTS.replaceAll("[^a-zA-Z0-9-_ ]+", "");
cNew = c;
while (allC.contains(cNew)) {
 cNew = c + ci;
 ci++;
}
ci = 2;
allC.add(cNew);

sql = String.format("\"%s\" as \"%s\" /*%s(%s)*/", c_COLUMN_NAME, cNew, c_DATA_TYPE_NAME,c_LENGTH);
allSql.add(sql);

out_sql = "SELECT \n" 
+ String.join(",\r\n", allSql) 
+ "\r\n/*WHERE \"TECHNAME\" IN ('VAL1','VAL2')*/"
+ "\r\nFROM \"_SYS_BIC\".\"" + c_VIEW_NAME + "\""
+ "\r\nLIMIT 10";

image

Would it be possible to fix this in KNIME node?

Trying to add the correct screenshot for the error

SAP DBTech JDBC: [268] (at 22): column ambiguously defined

Hi @tescnovonesis
Thank you for highlighting this. I’d like to inform you that I have forwarded this bug report internally - it will be evaluated by our Product Management for fixing in a future product version.
However we are unable to provide more information until the evaluation is finished, which can take time.

1 Like