I/’ getting error in the script worked in 4.3.4
select p.Pat_MRN_Id
, $pat_name$ as Pat_Name, $ordering_date$ as Ordering_Date,
$prov_name$ as Provider_Name
,$clinician_title$ as Clinician_Title
,$description$ as Description
, $Ass_Dx$ as Ass_Dx, $Ass_Dx_Name$ as Ass_Dx_Name
–et.name as ENC_TYPE
, max(pe.Contact_Date) as Last_Office_Visit_Date
from pat_enc pe with(nolock)
join ZC_DISP_ENC_TYPE et with(nolock)
on et.DISP_ENC_TYPE_C = pe.enc_type_c and pe.enc_type_c =‘101’
join patient p with(nolock)
on p.pat_id = pe.pat_id
where p.Pat_MRN_Id = $ref Pat_MRN_ID$
and pe.Visit_prov_id = $med_presc_prov_id$
group by p.Pat_MRN_Id
ERROR Parameterized DB Query Reader 4:126 Execute failed: Missing value found in column Ass_Dx. Missing values are not supported in identification (WHERE) columns
Even more strange message come when I comment this line out
–$Ass_Dx$ as Ass_Dx, $Ass_Dx_Name$ as Ass_Dx_Name
WARN Parameterized DB Query Reader 4:126 Cannot retrieve an output data specification. Caused by Invalid parameter index 8.
Hello @izaychik63 ,
with 4.5.0 we added the new " Fail if WHERE clause contains any missing value" option to all database connector nodes. If the option is enabled KNIME will check the values that are passed into the WHERE part of the query and fails if one is missing. This is a precaution since missing values in KNIME are converted to NULL in the database and any comparison with NULL will be false. To use NULL in a WHERE clause you need to use the specific IS NULL or IS NOT NULL operations. This option is by default enabled for all connector node created with version 4.5.0 whereas for existing it is disabled to be backward compatible.
The Parameterized DB Query Reader node does not interprets the query and is maybe a bit overcautious by assuming that all KNIME columns you are using in your query are part of the WHERE clause. To disable the check go to the Advanced Tab of the database connector node and disable the option.
Regarding the “Invalid parameter index 8” KNIME does not understands your query but simply replaces the column placeholder with values in the SQL query which results in the error message. Since KNIME sends one more value to the database than the database expects.
Thank you, Tobias for explanation. I believe something is wrong on parameters interpretation because when I comment the line, warning is spits out. When I delete the line no warning. It means that “–” comment is not interpreted correctly.
Also, performance of the node is still needs to be better.
P.S. Option for Nulls is not available
Now that it is disabled the Parameterized DB Query Reader should no longer complain about a missing value in the WHERE clause.
Regarding the comment, you are right. KNIME is not trying to understand the query and thus has no idea about comments. This wouldn’t be easy to do since KNIME tries to support many databases with a lot of different SQL syntaxes even the once we do not know of. So it only checks for the column variables and replaces each of the column variables with a placeholder in the query. During execution every placeholder is filled with the values of the current row and executed. So in your case KNIME is setting 9 parameters. However the db identifies your comment and expects 2 less values hence the error message. If you comment out a part with column variables please remove the first $ from the variable name e.g. Ass_DX$ instead of $Ass_DX$ and you should be fine.
Bye
Tobias
Thank you, Tobias. The issue is coming from the node not following the rules.
The WF worked and been migrated. By the rule the setting in connector is off.
But node is reporting the null value. That means node does not see/ignore the setting.
The error pointing on the parameter in the where clause. That means that all 9 variables are considered as part of where clause even though only 2 really used.
I tried to reproduce the problem with the two attached workflows that where created with KNIME 4.3 and KNIME 4.4 but failed. Both behave as expected. The “Fail if WHERE clause contains any missing value” option is disabled in the connector and the Parameterized DB Query Reader node executes as before for each workflow. However when I manually enable the setting or use a new DB Connector node the node fails with the missing value in where clause exception.
Can you provide me with some more information e.g. which database you try to connect to?
Regarding the error pointing on the WHERE clause. I agree that this is incorrect for the Parameterized DB Query node since the node checks all variables independent where they are used in the query. I will create a ticket for this (internal number AP-18083) but it will be expensive to fix since the node allows you to write any query for any JDBC database.
Tobias, I use Microsoft SQL Server Connector. Possibly in my case there is a difference how to migrate WF. I migrated a whole workspace. In this case nodes may not reset on WF opening. As you created a special WF, it most likely was reset. I usually not reset connection nodes as they configured to reconnect on load.
Hello @izaychik63 ,
thanks for the information. You are right, if the connection is restored new parameters are not set to the backward compatible value but to the new default value. I have created a ticket (internal number AP-18090) to fix this.
Sorry for the inconveniences.
Bye
Tobias
Hello @izaychik63 ,
we have just release version 4.5.1 of the KNIME Analytics Platform which will use the backward compatible settings when restoring an existing database session.
Bye
Tobias
