DB_OUTPUT_TABLE component and DELETE query

CloverDX Platform
1

Hi!

In a graph I use a DB_OUTPUT_TABLE component to delete some records in an Oracle database.

So I Have in the SQL Query attribute a query like this :

DELETE FROM mytable
WHERE myfield1 = $myfield1
AND myfield2 = $myfield2

The problem appears when one or all of the fields are NULL.

Indeed “DELETE FROM mytable WHERE myfield1 = 1 AND myfield2 = null” doesn’t work
The good delete query must be “DELETE FROM mytable WHERE myfield1 = 1 AND myfield2 IS null”

So is there a way to manage this issue with CloverETL ? How to replace “=” by “IS” in this case ?

Thanks a lot.

See you.

2

You can construct your query like this:
delete from x
where a = $a
and (b = $b or (b is null and $b is null))

3

Hi!

OK thank you. Indeed it works great and SQL code makes sense.

I didn’t think about it. I was looking for a solution with CloverETL.

Thanks again.

See you.