'How to avoid NULL check in JOIN condition in Tableau
I am trying to change backend DB for Tableau dashboard. Tableau is generating JOIN SQLs with conditions such as:
ON a.col1 = b.col2 OR (a.col1 is null and b.col2 is null)
Is there a way we can avoid having OR (a.col1 is null and b.col2 is null) condition? I tried ZN and IFNULL with the column name but still such conditions are getting added. The Druid DB supports JOIN only with equality condition and because of IS NULL check , the query is failing. Thanks
Solution 1:[1]
Tableau is treating NULL as if it were a value and in SQL that is not the case, it is the absence of a value. According to https://help.tableau.com/current/pro/desktop/en-us/joining_tables.htm in the section named "About null values in join keys" it mentions an option to set "Join null values to null values", perhaps that is turned on in your case.
On the Druid side if you want to treat NULL as meaning a default value, then a possible route is to transform the NULL into a special value (say -1 or whatever is out of the normal range of the values) and have that value exist on both tables instead of NULL.
In Druid at ingestion time you can use:
...
"transformSpec": {
"transforms": [
{
"type": "expression",
"name": "col1",
"expression": "nvl( col1, -1)"
}
]
...
which will replace col1 with the calculated column col1 ( this is called shadowing) that has replaced NULL values with -1.
for more info on the transformSpec and on expression functions that are available you can go to:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |