为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】: 8
【操作系统】:win11
【CPU】: 12900H
【问题描述】*:
执行sql提示 SQL 错误 [22000]: 无效的nlsparam参数
关键错误语句
WHERE mec.is_deleted = 0 and mec.project_id = '1892454696429248512'
改为 WHERE mec.is_deleted = 0 and mec.project_id = '1' 不报错
字段定义为bigint 长度20
完整sql: SELECT
mec.id,
mec.project_id,
td.NAME AS elementTypeName,
mec.unit_id,
mec.NAME,
case when mec.waste_type_id = -1 then null else mec.waste_type_id end as wasteTypeId,
td1.name as wasteTypeName,
mec.precursors_description,
mec.precursors_id,
mec.precursors_source_type,
mec.category_id,
mec.route_id,
mec.direct_emission,
mec.indirect_emission,
mec.spec_elec_val,
mec.elec_emission,
mec.country_id,
mec.factors_unit_id,
mec.electricity_identify_id,
mec.hot_val,
mec.hot_unit_id,
mec.emission_factors_val,
mec.emission_unit_id,
mec.hot_factor_flag,
mec.precursors_source_id,
mec.precursors_version_id,
mec.electri_source_id,
mec.electri_version_id,
ROW_NUMBER() OVER ( PARTITION BY td.NAME ORDER BY NLSSORT(mec.NAME, 'NLS_SORT=GBK') ) AS rowNo
FROM
m_element_config mec
LEFT JOIN t_dictionary td ON mec.element_type_id = td.id
left join t_dictionary td1 on mec.waste_type_id = td1.id
WHERE
mec.is_deleted = 0 and mec.project_id = 1767375204457250816
单独执行单表查询没问题。。。,多表查询报错
抱歉,原描述误导大家了,现在定位到问题sql在: ROW_NUMBER() OVER ( PARTITION BY td.NAME ORDER BY NLSSORT(mec.NAME, 'NLS_SORT=GBK') ) AS rowNo 这句, 当project_id为1时查不到数据, sql执行未报错,导致我误以为是条件问题, 实际可能是sql解析器发现没数据后终止执行了..
问题解决了 将问题sql替换为 ROW_NUMBER() OVER ( PARTITION BY td.NAME ORDER BY NLSSORT(mec.NAME, 'NLS_SORT=SCHINESE_PINYIN_M') ) AS rowNo 即可
直接使用mec.project_id = 1892454696429248512整数进行比较,避免数据库进行字符串到 bigint 的转换,试试这种方式