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
Example 2 - Dublicate “Pretty columns names” in Table
SAP DBTech JDBC: [268] (at 22): column ambiguously defined
Looking here:
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";
Would it be possible to fix this in KNIME node?
