Today, I have found a funny problem when use "Union" in Oracle.
Just like this,
I have two tables, one calls "TB_STUDENT" and the other "TB_RESOURCE"
TB_STUDENT
{
WORKER_ID NVARCHAR2(40),
SEX INTEGER
}
TB_RESOURCE
{
RESOURCE_ID NVARCHAR2(40),
...
}
for the two tables, "TB_STUDENT" has some records while "TB_RESOURCE" has no record!
then, I try the following SQL sentence:
SELECT WORKER_ID FROM TB_STUDENT
UNION
SELECT '' FROM TB_RESOURCE
it got an error "ORA-12704: 字符集不匹配";
I thought maybe it was caused by '', because '' (empty string) denotes the NULL value in Oracle.
So, changed it to ' ';
Unfortunately, it still didn't work!
...
oh, maybe it requires strictly type check!
SELECT WORKER_ID FROM TB_STUDENT
UNION
SELECT CAST('' AS NVARCHAR2(40)) FROM TB_RESOURCE
ok,run passed!
// new added, 05-09-27
oftenly, we think it would be "OK" when using Parameter.
for instance,
SELECT LMS_TEACHER.TEACHER_ID FROM LMS_TEACHER WHERE LMS_TEACHER.CODE='I0001' AND LMS_TEACHER.TEACHER_ID<>'';
I use this sentence to find whether there exist a record with "LMS_TEACHER.CODE='I0001'", because LMS_TEACHER.TEACHER_ID is a primary key, so there is no record accord with the condition "LMS_TEACHER.TEACHER_ID<>''". And in the program, I use a sentence with parameter instead the front sentence, just like:
SELECT LMS_TEACHER.TEACHER_ID FROM LMS_TEACHER WHERE LMS_TEACHER.CODE=@NewCode AND LMS_TEACHER.TEACHER_ID<>@TeacherID;
Unfortunately, it returns no record when there is a record with LMS_TEACHER.CODE='I0001' ;
Then I change [<>''] to [IS NOT NULL], it runs ok.
the same result appeared when I try the following setences:
SELECT * FROM LMS_TEACHER WHERE LMS_TEACHER.TEACHER_ID<>' '; (ok)
SELECT * FROM LMS_TEACHER WHERE LMS_TEACHER.TEACHER_ID<>''; (no record)
SELECT * FROM LMS_TEACHER WHERE LMS_TEACHER.TEACHER_ID IS NOT NULL; (ok)
[ :( it scratches my head over ]