sql - JOIN ON 子句中的 Validate_conversion 函数 - ORA-00932 : inconsistent datatypes: expected NUMBER got CHAR

Question

我有两个表，并用一些数据填充它们:

create table qz_products (
   id    integer primary key
 , name  varchar2(20) not null unique
);

create table qz_wishlist (
   user_name   varchar2(10) not null
 , id_or_name  varchar2(20) not null
);

insert into qz_products values (1042, 'Bowling ball' );
insert into qz_products values (1088, 'Bottle opener');
insert into qz_products values (2021, 'Bikini top'   );
insert into qz_products values (2069, 'Beach parasol');

insert into qz_wishlist values ('Bob'  ,'1042');
insert into qz_wishlist values ('Bob'  ,'Bottle opener');
insert into qz_wishlist values ('Betty','Bikini top');
insert into qz_wishlist values ('Betty','2069');

我想在特定条件下加入这些表:当字段 id_or_name 是数字数据类型时，我想加入 qz_products 表中的 id 字段。否则，我想使用 qz_products 表中的 name 字段加入，因此输出应如下所示:

USER_NAME          ID NAME
---------- ---------- --------------------
Betty            2021 Bikini top
Betty            2069 Beach parasol
Bob              1042 Bowling ball
Bob              1088 Bottle opener

我想出了一个使用 VALIDATE_CONVERSION 函数的想法。这是我的查询:

select w.user_name, p.id, p.name
from qz_wishlist w inner join qz_products p
on w.id_or_name = (case when validate_conversion(w.id_or_name as number) = 1 then p.id
                    else p.name end);

我有一个错误:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

我知道，这个错误是什么意思，但我不确定，为什么会出现这个错误。
我在那里错过了什么？
此致。

Answer 1

case的所有分支必须返回相同的数据类型，所以你的方法不起作用:使用 or条件代替。

另外，我建议使用 to_number()与 on conversion error选项而不是 validate_conversion() .这会即时验证和转换。如果值不能转换，我们可以做 to_number()返回 null ，所以它不能匹配 p.id .

select w.user_name, p.id, p.name
from qz_wishlist w inner join qz_products p
on 
    to_number(w.id_or_name default null on conversion error) = p.id
    or w.id_or_name = p.name

Demo on DB Fiddle :

USER_NAME |身份证 |姓名
:-------- | ---: | :------------
鲍勃 |第1042章保龄球
鲍勃 |第1088章开瓶器
贝蒂 | 2021 |比基尼上衣
贝蒂 | 2069 |沙滩伞