longtext类型字段没法使用in过滤
为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【GBase版本】:9.5.3.28.31 patch.1.b821c648e1
【操作系统】:redhat-linux
【CPU】:
【问题描述】*:
执行下面的sql报错:(GBA-01EX-700) Gbase general error: Column __fcol_1 is not supported data type
select
`T_D21A46`.`param_code` as `__fcol_0`,
`T_D21A46`.`param_name` as `__fcol_1`,
`T_D21A46`.`param_type_code` as `__fcol_2`
from (SELECT param_code,parent_id,CONCAT(param_code, ' :', param_name) AS param_name,param_type_code FROM sys_level_param
) as `T_D21A46`
) as `t0`
where (
`t0`.`__fcol_2` in ('C_CURR')
and `t0`.`__fcol_1` in ('CNY :人民币元')
)
param_code和param_name字段类型都是varchar(10922),使用create TEMPORARY TABLE tmp as SELECT param_code,parent_id,CONCAT(param_code, ' :', param_name) AS param_name_concat,param_type_code FROM sys_level_param创建tmp表后,发现param_name_concat字段的类型的longtext,直接使用select * from tmp where param_name_concat in ('a)也报错Column param_name_concat is not supported data type
评论
可考虑在 CONCAT 外层使用 CAST 或 CONVERT 转为 varchar(需要注意varchar 的最大长度限制)。
热门帖子
- 12025-12-01浏览数:183726
- 22023-05-09浏览数:26431
- 42023-09-25浏览数:20095
- 52020-05-11浏览数:18821