Toad World® Forums

Compare Data incorrectly uses '' to synchronize nulls to spaces


#1

Howdy,

In 12.1.0.22, I’m using the wonderful Compare Data to synchronize the data between two tables with the “Create Script” option. The generated statements work except when the difference between two column values is spaces on the source and nulls on the target. The generated statement says:

UPDATE MYSCHEMA.MYCOOLTBL SET SILLYCOL = ‘’ WHERE ROWID = ‘AAAcOfAAUAAHRM8AAJ’;

On a nullable column, this doesn’t do anything since the target is already nulls. It should be set to the same number of spaces as the source. Technically, since the source for this example is type NCHAR, the SQL should have the implicit string preceded with an “N”, as well, shouldn’t it?

TIA!

Rich


#2

Source and target are both NCHAR – not sure how that should rectified if they are not both the same…

Rich


#3

I guess if we have spaces in source and nulls in target, we could do something like

set sillycol = ’ ’ (one space).


#4

John!

For CHAR, yes, would work, but shouldn’t VARCHAR2 still have the same length? Or is that not an issue?

Thoughts on using “N” for the NCHAR?


#5

Hm, yeah, I guess we ought to be using the N.

By the way, if you go to Options -> Data Grids -> Data -> Trim string data in CHAR and NCHAR columns, and uncheck it, that might give you spaces instead lf ‘’ in your update statement.


#6

Mine’s already unchecked. :frowning:

Thanks!


#7

Yeah, I see what’s going on now. It’s too late for 12.5 but I’ll have it fixed in 12.5+n beta #1 where n in [0.1, 0.5, 1.5]


#8

I’m too swamped for the next few months to do much of anything else besides the project I’m on, so It’s All Good™.

Thanks John!

Rich