1

So I am comparing two Oracle databases by grabbing random rows in database A, and searching for these rows in database B based off their key columns. Then I compare the rows which are returned in java.

I am using the following query to find rows in database B using the key columns from database A:

select * from mytable 
Where (Key_Column_A,Key_Column_B,Key_Column_C) 
in (('1','A', 'cat'),('2','B', 'dog'),('3','C', ''));

This works just fine for the first two sets of keys, but the third key('3','C', '') does not work because there is a null value in the third column. Changing the statement to ('3','C', NULL) or changing the SQL to 

select * from mytable 
Where (Key_Column_A,Key_Column_B,Key_Column_C) 
in ((('1','A', 'cat'),('2','B', 'dog'),('3','C', ''))
OR (Key_Column_A,Key_Column_B,Key_Column_C) IS NULL);

will not work either.

Is there a way to include a null column in an IN clause? And if not, is there a way to efficiently do the same thing? (My only solution currently is to create a check to make sure there are no nullable columns in my keys which would make this process rather unefficient and somewhat messy).

JoshKni8
  • 145
  • 1
  • 3
  • 11

1 Answers1

2

You can use it this way. I think it would work. 

 select * from mytable 
  Where (NVL(Key_Column_A,''),NVL(Key_Column_B,''),NVL(Key_Column_C,'')) 
   in (('1','A', 'cat'),('2','B', 'dog'),('3','C', ''));

I am not sure about this (Key_Column_A,Key_Column_B,Key_Column_C) IS NULL. Wouldn't this imply that all of the columns (A,B,C) are NULL ?

Fnaxiom
  • 387
  • 1
  • 8
  • NVL is a unknown command in my version of Oracle. That attempt was based on the answer I saw here: http://stackoverflow.com/questions/6362112/in-clause-with-null-or-is-null – JoshKni8 Jan 20 '16 at 16:15
  • I don't know about your version. Use the equivalent and it would work. `Coalesce` for instance. – Fnaxiom Jan 20 '16 at 16:16
  • Btw, the thread you've shared is different from yours. It compares a value against a list. Here, you are comparing a list against a list. – Fnaxiom Jan 20 '16 at 16:18
  • Ahh my mistake. Adding NVL to every column removes the unknown command error. But this still doesn't seem to be working as it doesn't return any row for me when I am sure it exists. – JoshKni8 Jan 20 '16 at 16:18
  • is your `IN` statement generated by java ? – Fnaxiom Jan 20 '16 at 16:24
  • Yes, it is generated dynamically through java. – JoshKni8 Jan 20 '16 at 16:26
  • Is it possible to see the code that generated this : `('1','A', 'cat'),('2','B', 'dog'),('3','C', '')` ? – Fnaxiom Jan 20 '16 at 16:28
  • Why is that necessary? I am certain it is not an error with the way the SQL is generated. Does the SQL you originally posted actually work for you, or is it more of a theoretical solution? – JoshKni8 Jan 20 '16 at 16:32
  • Yes. It worked. Are you sure that you did not add spaces within the quotes or anything ? I am using a postgresql : `select case when ('A','B','') = (coalesce('A',''),coalesce('B',''), coalesce(NULL,'')) then 'Worked' else 'Did not' end` – Fnaxiom Jan 20 '16 at 16:35
  • Hmm here is a direct example of what is generated (only replaced column names and table): select * from schema.table Where (NVL(Col_A,''), NVL(Col_B,''), NVL(Col_C,''), NVL(Col_D,''), NVL(Col_E,'')) IN (('','1','C002340237A','8258','MK')); – JoshKni8 Jan 20 '16 at 16:39
  • 3
    An empty string is the same as null in Oracle, so `NVL(Key_Column_A,'')` is `NVL(Key_Column_A,null)`, which doesn't achieve anything. – Alex Poole Jan 20 '16 at 16:39
  • 1
    @Alex Poole Right ! In that way, you can use `NVL(Key_Column_A,'''''')` (which is not beautiful) – Fnaxiom Jan 20 '16 at 16:43
  • 1
    @AlexPoole AHH I think you have solved my problem. I need to replace the searched null value with a temporary value. Like NVL(Key_Column_A,'N') IN ('N') – JoshKni8 Jan 20 '16 at 16:43
  • 2
    @JoshKni8 You'll have to make sure also that the target search also gets the temporary value and not ''. – Fnaxiom Jan 20 '16 at 16:47