ORACLE Database query (null) values in variable

[Anjo]

Hi,

I want to query my database where certain column fields match elements in a list.

My oracle 19c database table looks like this:

[TESTNAME] | [UNITS]
test 1 | mg
test 2 | (null)
test 3 | kg
test 4 | (null)
test 5 | cm
test6 | µM

And my KNIME table like this:

[testname] | [units]
test 1 | mg
test 2 | ?
test 4 | ?
test 5 | cm

What I do is I generate two variables using Java Snippet concatenating the values from the KNIME table:
test_variable: ‘test 1’, ‘test 2’, ‘test 4’, ‘test 5’
units_variable: ‘mg’, ‘(null)’, ‘(null)’, ‘cm’

Then I use the DB Table Selector Node with the following SQL statement:

SELECT table.TESTNAME, table.UNITS
FROM table
WHERE (table.“TESTNAME” IN ($${Stest_variable}$$) AND table.“UNITS” IN ($${Sunits_variable}$$))

The result is the following table missing all the (null) entries:
[testname] | [units]
test 1 | mg
test 5 | cm

The correct output for me would be:
[testname] | [units]
test 1 | mg
test 2 | ?
test 4 | ?
test 5 | cm

I tried several variations, e.g. changing the ‘(null)’ to ?, NULL … I also tried the Table Row To Variable Loop Start-Loop End feeding the values one at a time but nothing worked so far.

I know (null)/ NULL can be tricky (https://www.red-gate.com/hub/product-learning/sql-prompt/the-null-mistake-and-other-sql-null-heresies) but how do you solve it :)?

Would be nice if somebody has an idea how to solve this problem with the (null) entries.

Many thanks in advance

[takbb]

Can you change your where clause to use the oracle
NVL() statement to convert a returned null from oracle to be something else such as the question mark you mentioned. A null value will itself never match a where condition.

So your statement would be something like

SELECT table.TESTNAME, table.UNITS
FROM table
WHERE (table.“TESTNAME” IN ($${Stest_variable}$$) AND
NVL(table.“UNITS”, ‘?’)
IN ($${Sunits_variable}$$))

and then that should match if your variable contains a ‘?’

In addition, you could also return the units with the nvl statement as well if you want to

SELECT table.TESTNAME, nvl(table.units, ‘?’) as units

… would cause a null units to be returned to knime as a ‘?’

[Anjo]

Hi,

that worked like a charm! The query you suggested returned exactly the matching rows. I did various google searches but never came accross the NVL solution (or I may have overlooked it).

Great! You saved my day.

Many thanks!

[takbb]

No problem. Glad it worked for you. If you ever find yourself in a similar situation with other SQL databases, they generally have a similar function but unfortunately there isn’t a standard. On MS Sql Server or Sybase, the equivalent of NVL() is ISNULL(), and on MySql it’s IFNULL().

[Anjo]

Ah good to know! I think I remember to read about ISNULL and IFNULL but I did not realize that there is a different Oracle standard. Now I know… it actually really helps for some other parts in my workflow.