在ck中能顺利执行,但是在数据集里会报错,报错信息和整个sql语句没有任何关系
ClickHouse exception, code: 36, host: service-ck-ksodvgy18q.ck-ksodvgy18q-hd.jvessel2.jdcloud.com, port: 8123; Code: 36, e.displayText() = DB::Exception: Element of set in IN, VALUES or LIMIT is not a constant expression (result column not found): 1 (version 21.3.5.42 (official build))sql:
翻译
搜索 复制
select t1.audit_date,
t1.cyear,
t1.cmonth,
t1.region_name,
t1.office_name,
t1.old_shop_target,
t1.old_shop_efficiency,
t1.old_shop_shop_num,
t1.old_shop_order_amount,
t1.model_task_amount_target,
t1.model_shop_num,
t1.audit_credit_amount,
t1.py_old_shop_target,
t1.py_old_shop_efficiency,
t1.py_old_shop_shop_num,
t1.py_old_shop_order_amount,
t1.py_model_task_amount_target,
t1.py_audit_credit_amount,
t1.new_shop_num,
t1.new_shop_check_date,
t1.new_shop_order_amount,
t1.new_shop_target,
toDate(t1.audit_date) audit_date_date,
case
when t1.new_shop_check_date is null then 0
else toInt64(replace(toString(t1.new_shop_check_date), '-', '')) end new_shop_check_date_new,
toInt64(replace('2023-01-01', '-', '')) start_date,
toInt64(replace('2023-01-01', '-', '')) stop_date,
(case
when t1.region_name = '华东大区' then case
when t1.new_shop_check_date is null then 0
else case
when toInt64(replace('2023-01-01', '-', '')) >= case
when toInt64(replace(toString(t1.new_shop_check_date), '-', '')) >=
toInt64(replace('2023-01-01', '-', ''))
then toInt64(
left(replace(toString(t1.new_shop_check_date), '-', ''), 6))
else toInt64(left(replace('2023-01-01', '-', ''), 6)) end
then toInt64(left(replace('2023-01-01', '-', ''), 6)) -
case
when toInt64(replace(toString(t1.new_shop_check_date), '-', '')) >=
toInt64(replace('2023-01-01', '-', ''))
then toInt64(
left(replace(toString(t1.new_shop_check_date), '-', ''), 6))
else toInt64(left(replace('2023-01-01', '-', ''), 6)) - 1 end
else 0 end end
else case
when t1.new_shop_check_date is null then 0
else case
when toInt64(replace('2023-01-01', '-', '')) >= case
when toInt64(replace(toString(t1.new_shop_check_date), '-', '')) >=
toInt64(replace('2023-01-01', '-', ''))
then toInt64(
left(replace(toString(t1.new_shop_check_date), '-', ''), 6))
else toInt64(left(replace('2023-01-01', '-', ''), 6)) end
then toInt64(left(replace('2023-01-01', '-', ''), 6)) - case
when toInt64(replace(toString(t1.new_shop_check_date), '-', '')) >=
toInt64(replace('2023-01-01', '-', ''))
then toInt64(
left(replace(toString(t1.new_shop_check_date), '-', ''), 6))
else toInt64(left(replace('2023-01-01', '-', ''), 6)) end +
1
else 0 end end end) * t1.new_shop_num month_num
from db_dm_ck.dm_model_shop_result t1
|
