I understand there are some restrictions on how substitutions are made for items in the query that relate to the table / table metadata. I am not a SQL expert so I cannot advise on the details but you may be able to use a stored procedure/ function to implement the required functionality. I found the following image of an example where a stored procedure was used to specify table name and column data
Precisely Software Inc.
Original Message:
Sent: 02-27-2024 07:06
From: Irakli Chitishvili
Subject: Someone can help-me with the sqlQuery(from field)?
Hi Adrian, this is really interesting. I am also trying to build a dynamic sql statement from the results from API call->transform and wanted to ask which parts of the sql statements can be parametrized? Is it column names in SELECT, table names in FROM? or it only works with WHERE clause? I couldn't make it work with FROM ? where table name was already defined before and JDBC node was configured properly - SQLQuery was selected as an input and sqlQueryFieldBindings was also pointing to the column name in the previous node where the table name was generated.
query example:
select max(date) from ?
This is the error message:
key=brain.node.jdbc.ErrorOnRow bundle=com.lavastorm.brain.nodes.jdbc.ErrorMessages locale=English (United States) args=[0,ORA-00903: invalid table name https://docs.oracle.com/error-help/db/ora-00903/]
Thanks
------------------------------
Irakli Chitishvili
Degroof Petercam SA/NV
Original Message:
Sent: 01-02-2024 09:15
From: Adrian Williams
Subject: Someone can help-me with the sqlQuery(from field)?
Some Data360 Analyze nodes support the sourcing of certain property values from an input field.
When the source for a relevant property is set to <Property_Name> (from Field) this instructs the node to obtain the value for the property from the specified input field e.g.
In the above example the value for the SqlQuery property will be sourced from the QueryString input field. Which in this case would need to comprise a valid SQL statement, e.g.
However, you can alternatively use SQL Query Field Bindings to create a dynamic SQL statement that is to be executed by the node. In this case, the base SQL query is defined as a literal string in the JDBC Query node but the element(s) to be sourced from an input field(s) are represented by the "?" character. For instance, the modified SqlQueryField value is as follows:
The SqlQueryFieldBindings property specifies the field name(s) that contain the values to be substituted into the SQLQuery string, e.g.
Note that the source for the SqlQuery property is set to the default option (i.e. the node uses a literal value) and not the SqlQuery (from Field) option.
Where there are more than one "?" character in the parameterized SQL Query string, the values are substituted from the fields in the order specified in the SqlQueryFieldBindings property.
------------------------------
Adrian Williams
Precisely Software Inc.
Original Message:
Sent: 12-29-2023 07:40
From: Andhrey Barbosa
Subject: Someone can help-me with the sqlQuery(from field)?
Hey, everyone!
Someone can help-me with the synthax?
I'm new on D360 and i need some help.
------------------------------
Andhrey Barbosa
Knowledge Community Shared Account
------------------------------