DataWorks你们这语法是怎么判定参数的, 我入参配置的是字符串,怎么给我解析成数字类型了,而且解析的就不对?
[INFO] [10:53:38.732] resource group is 0[INFO] [10:53:38.752] prepared test environment, take time 158 ms[INFO] [10:53:38.775] api context init , take time 2 ms
[INFO] [10:53:38.776] start to test api[416180068298768]: 查询展馆销售明细
[INFO] [10:53:38.776] test case parameters:
[INFO] [10:53:38.776] QUERY:[{"paramKey":"pageNum","paramValue":"1"},{"paramKey":"pageSize","paramValue":"10"},{"paramKey":"beginTime","paramValue":"20230501"},{"paramKey":"endTime","paramValue":"20230515"},{"paramKey":"projectCode","paramValue":"3300"},{"paramKey":"siteIds","paramValue":"28,45,50,47,52,48,75,77,79,78"},{"paramKey":"sortValue","paramValue":"gmv"}]
[INFO] [10:53:38.776] not use dynamic sql
[INFO] [10:53:38.777] api request execution started. [OK]
[INFO] [10:53:38.778] api sql realsql : SELECT site_id AS siteId, site_name AS siteName, SUM(day_gmv) AS gmv , SUM(day_sales) AS sales, SUM(gmv_goods_sell_count) AS gmvGoodsSellCount , SUM(sales_goods_sell_count) AS salesGoodsSellCount, SUM(gmv_order_count) AS gmvOrderCount , SUM(sales_order_count) AS salesOrderCount FROM odps_wsnb_online_ds_dws_site_sales_detail WHERE time >= 20230501 AND time <= 20230515 AND project_code = '3300' AND CASE WHEN CONCAT('28', '') IS NULL THEN 1 = 1 ELSE site_id IN ('45', '50', '47', '52', '48', '75', '77', '79', '78', '28,45,50,47,52,48,75,77,79,78') END GROUP BY site_id, site_name ORDER BY CASE WHEN 'gmv' IS NULL THEN 'gmv' ELSE 'gmv' END DESC
[INFO] [10:53:38.778] query database started. [OK]
[ERROR] [10:53:38.834] api request executed [FAILED]
[INFO] [10:53:38.834] api request executed, take time : 58 ms
[INFO] [10:53:38.834] api test finished, take time 60 ms
{
"apiLog": null,
"data": null,
"errCode": 1108110622,
"errMsg": "数据源查询失败:[PreparedStatementCallback; SQL [SELECT site_id AS siteId, site_name AS siteName, SUM(day_gmv) AS gmv , SUM(day_sales) AS sales, SUM(gmv_goods_sell_count) AS gmvGoodsSellCount , SUM(sales_goods_sell_count) AS salesGoodsSellCount, SUM(gmv_order_count) AS gmvOrderCount , SUM(sales_order_count) AS salesOrderCount FROM odps_wsnb_online_ds_dws_site_sales_detail WHERE time >= ? AND time <= ? AND project_code = ? AND CASE WHEN CONCAT(?, '') IS NULL THEN 1 = 1 ELSE site_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) END GROUP BY site_id, site_name ORDER BY CASE WHEN ? IS NULL THEN 'gmv' ELSE ? END DESC \n limit 10 offset 0]; ERROR: invalid input syntax for integer: \"28,45,50,47,52,48,75,77,79,78\"\n 位置:529; nested exception is org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: \"28,45,50,47,52,48,75,77,79,78\"\n 位置:529]",
"requestId": "0abb7ee116847240183103713e893f"
}这参数就解析错了
SELECT
site_id AS siteId,
site_name AS siteName,
SUM(day_gmv) AS gmv,
SUM(day_sales) AS sales,
SUM(gmv_goods_sell_count) AS gmvGoodsSellCount,
SUM(sales_goods_sell_count) AS salesGoodsSellCount,
SUM(gmv_order_count) AS gmvOrderCount,
SUM(sales_order_count) AS salesOrderCount
FROM
dws_site_sales_detail
WHERE
time >= ${beginTime}
AND time <= ${endTime}
AND project_code = ${projectCode}
AND (
CASE WHEN CONCAT((${siteIds}), '') IS NULL THEN
1 = 1
ELSE
(site_id IN (${siteIds}))
END)
GROUP BY
site_id,
site_name
ORDER BY
(
CASE WHEN ${sortValue} IS NULL THEN
'gmv'
ELSE
${sortValue}
END) DESC这是我写的脚本,麻烦找下技术支持
这sql是可以执行的,就是api接口解析就解析错了
是开启了加速是吗 执行的是pg语法 看上面截图里执行成功的语句是加了单引号的 数据服务传递的参数里没有加 也加一下试试
此回答整理自钉群“DataWorks交流群(答疑@机器人)”
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。
DataWorks基于MaxCompute/Hologres/EMR/CDP等大数据引擎,为数据仓库/数据湖/湖仓一体等解决方案提供统一的全链路大数据开发治理平台。