SQL字符类型问题求助：修复SW_CUSTOMER.TELCNPJ格式错误

执行SQL查询时返回字符类型错误：

Bad character in format or data of SW_CUSTOMER.TELCNPJ.
at gosqldriver/teradatasql.formatError ErrorUtil.go:85

对应的Python代码及SQL查询语句如下：

cod_cli = df['cod_cli'].tolist()

cod_cli_str = ','.join(map(str, cod_cli))

# clientesAtacado =

query = f"""

select * from P_VTVVIEDB.VBRA_CITY

where bracityid in (select bracityid

from P_VTVVIEDB.VSW_ADDRESS

where swobjectid in (select swsiteid from P_VTVVIEDB.VSW_SITE where swcustomerid in

(SELECT swcustomerid FROM P_VTVVIEDB.VSW_CUSTOMER WHERE CONCAT('40',left(TELCNPJ,8)) IN (${cod_cli_str}))))

;
"""

核心问题分析 #

错误根源在CONCAT('40',left(TELCNPJ,8)) IN (${cod_cli_str})部分：

• cod_cli_str是纯逗号拼接的字符串，IN子句中的字符串值必须用单引号包裹，否则Teradata会识别为语法错误或数值类型不匹配，触发字符格式校验失败。
• 若TELCNPJ是数值类型，直接用LEFT函数处理会引发类型转换异常，导致字符格式错误。

具体修复步骤 #

1. 给IN子句的每个值添加单引号
   修改Python拼接逻辑，确保每个客户编码都被单引号包裹：

   cod_cli_str = ','.join([f"'{str(c)}'" for c in cod_cli])
   

   生成的字符串格式会变为'40xxxxxx','40yyyyyy'，符合SQL字符串比较要求。

2. 统一TELCNPJ的字符类型并过滤无效数据
   先把TELCNPJ转换为字符类型再截取，同时过滤空值或长度不足8位的记录：

   SELECT swcustomerid 
   FROM P_VTVVIEDB.VSW_CUSTOMER 
   WHERE TELCNPJ IS NOT NULL 
     AND LENGTH(CAST(TELCNPJ AS VARCHAR(20))) >= 8
     AND CONCAT('40', LEFT(CAST(TELCNPJ AS VARCHAR(20)), 8)) IN ({cod_cli_str})
   

3. 规范f-string变量引用
   Python f-string中直接用{cod_cli_str}即可，无需额外加$：

   query = f"""
   select * from P_VTVVIEDB.VBRA_CITY
   where bracityid in (select bracityid
   from P_VTVVIEDB.VSW_ADDRESS
   where swobjectid in (select swsiteid from P_VTVVIEDB.VSW_SITE where swcustomerid in
   (SELECT swcustomerid FROM P_VTVVIEDB.VSW_CUSTOMER WHERE CONCAT('40',LEFT(CAST(TELCNPJ AS VARCHAR(20)),8)) IN ({cod_cli_str}))))
   ;
   """
   

内容的提问来源于stack exchange，提问作者vituu999