DB_OUTPUT_TABLE component and DELETE query

maxani

• October 15, 2009 00:00

• Answered

Follow

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.

• 

  ciscochi

  • October 15, 2009 19:14

  0

  Comment actions Permalink

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

  maxani

  • October 16, 2009 13:22

  0

  Comment actions Permalink

  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.

Please sign in to leave a comment.