Hi Chris,

Thanks a lot. If t1 c1 has so many special characters such as '+','#','*', I hope those special characters without change in the t2 c1 column based on the same id. How can I come out a SQL for this?

Kind Regards,

Derek

Hi Chris,

Sorry, let me rephrase my question. My purpose is table T1 column c1 has null values, special characters and numeric string and normal characters such as abc,cde,etc.

I transferred the t1.c1 to t2.c1, I would like to confirm those null values, special characters,and numeric strings no change based on the same id. Only normal character strings of the column changed from t1 to t2.

Can one sql do this for the first situation?

Kind Regards,

Derek

Hi Chris,

Thank you for your reply. Sorry probably I didn't

I have two tables like SQLs below,

create table t1 (id char(4),c1 char(4));
create table t2 (id char(4),c1 char(4));

insert into t1 values (1,null);
insert into t2 values (1,null);

insert into t1 values (2,9);
insert into t2 values (2,9);

insert into t1 values (3,null);
insert into t2 values (3,8);

insert into t1 values (4,'+');
insert into t2 values (4,'+');

insert into t1 values (5,'*');
insert into t2 values (5,'!');

insert into t1 values (6,'a');
insert into t2 values (6,null);

insert into t1 values (7,'b');
insert into t2 values (7,'b');

insert into t1 values (8,'c');
insert into t2 values (8,'d');

I would the SQLs based on the same id like,

Scenario 1:

If t1.c1 is numeric, the query will return equal or not equal c1.

Scenario 2:

If t1.c1 is null, the query will return equal or not equal, I treat if both are null, they should be return.

Scenario 3:

If t1.c1 is special character, the query will return equal or not equal c1.

Scenario 4:

If t1.c1 is alpha characters, the query will be return equal or not equal c1.

Each scenario can be one sql.

Kind Regards,

Derek

Hi Chris,

Actually, my purpose is I have a table t1 (id char(2), c1 varchar2(10)). c1 has the values null, special characters like '*','+', '-' and numeric and alpha values.

I created t2 table the same structure like t1. I used a plsql to transferred the data from t1 to t2. I would like to check all data transferred correctly based on the different patterns .

Kind Regards,

Derek

Thanks Chris for your reply and I will prepare my scripts to show my ideas then update you later.

Kind Regards,

Derek

Doesn't seem like the datatype matters.
maybe something like this?
https://livesql.oracle.com/apex/livesql/s/hmcg74tyhnfah17qvwctp0rxf

select  
  t1.id,  
  t1.c1 "one", 
  t2.c1 "two", 
  case 
  when t1.c1= t2.c1 then 'EQ'
  when t1.c1 is null and t2.c1 is null then 'EQ'
  end "a" 
from  
  t1, t2 
 where t1.id=t2.id

ID one two a
1     -   -  EQ
2    9    9    EQ
3     -  8     - 
4    +    +    EQ
5    *    !     - 
6    a     -   - 
7    b    b    EQ
8    c    d     - 

select *
  from t1, t2
 where t1.id = t2.id
   and nvl(t1.c1, 'null') <> nvl(t2.c1, 'null')

1. Create your test2 table with the default clause on c1 give it a Martian default value ie create table test2 ( Id number, c1 char(30) default 'DonaldTrump' )
Then use a simple Minus operator between test1 and test2.
If you desire the whole informations from both tables you could use the Lateral or Cross apply..