Не подставляет атрибут в SQL

roman_stepanov · 24.Август.2023 13:48:39

Добрый день!
Имеется куб, в котором есть расчетный атрибут

при использовании в качестве размерности строится неверный sql

конфиг дэша


{
  dataSource: {
    koob: 'Vertica_dwh_luxms_svc_r00.DQMON_transfer',
    yAxis: 'measures;mrc',
    measures: [
      "sum(if(pay_type_sndr = 'Банковская карта'  and category_name='Внутренний перевод' and (client_type = 'C-C' or client_type = 'C-B'), nvl(accept_cnt,0),0))/sum(if(category_name='Внутренний перевод' and (client_type = 'C-C' or client_type = 'C-B'), nvl(accept_cnt,0),1)):card_part",
    ],
    dimensions: [
      'mrc',
      'year_month',
    ],
    xAxis: 'year_month',
    filters: {
      mrc: [
        '=',
        'Волга',
        'Москва',
        'Дальний Восток',
        'Северный Кавказ',
        'Северо-Запад',
        'Сибирь',
        'Урал',
        'Южный',
        'Центр',
      ],
      sender: true,
      last_oper: true,
      rcpn_name: true,
      recipient: true,
      client_type: true,
      system_code: true,
      receive_type: true,
      category_name: true,
      pay_type_rcpn: true,
      pay_type_sndr: true,
      return_reason: true,
      corporate_flag: true,
      payment_direction: true,
      send_in_addition_flag: true,
    },
  },
  frame: {
    x: 15,
    y: 5,
    w: 6,
    h: 3,
  },
  view_class: '1II.line',
  title: 'Доля оплат картой',
}

roman_stepanov · 28.Август.2023 10:54:36

еще один случай пытался просуммировать card_share

2023-08-28 12:33:09.831 103425 [QueryPool-3208  ] ERROR c.l.b.h.e.WebExceptionHandler ::  java.sql.SQLException: [Vertica][VJDBC](4856) ERROR: Syntax error at or near ")" :: ReactiveQuery{traceId=luxmsbi=88EdqsrFF3E=, dataSource=HikariDataSource (default:Vertica_dwh_luxms_svc_r00_AK), query='SELECT mrc as mrc, sum(nvl(accept_cnt,0)) as kol_acc_tr, sum(nvl(card_part_common,0)) as oplata_kartoi, sum(()) as sum_card_share
 FROM (select sch_irina_prikazchikova."mt_2"."accept_cnt" as "accept_cnt",
 sch_irina_prikazchikova."mt_2"."accept_cnt_py" as "accept_cnt_py",
 sch_irina_prikazchikova."mt_2"."accept_date" as "accept_date",
 sch_irina_prikazchikova."mt_2"."accept_flag" as "accept_flag",
 sch_irina_prikazchikova."mt_2"."accepted_amt" as "accepted_amt",
 sch_irina_prikazchikova."mt_2"."accepted_amt_py" as "accepted_amt_py",
 sch_irina_prikazchikova."mt_2"."category_name" as "category_name",
 sch_irina_prikazchikova."mt_2"."client_type" as "client_type",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."corporate_flag"='Y' THEN '....'
 WHEN sch_irina_prikazchikova."mt_2"."corporate_flag"='N' THEN '......'
 ELSE sch_irina_prikazchikova."mt_2"."corporate_flag"
 END as "corporate_flag",
 sch_irina_prikazchikova."mt_2"."destination_mr" as "destination_mr",
 sch_irina_prikazchikova."mt_2"."destination_ops_type" as "destination_ops_type",
 sch_irina_prikazchikova."mt_2"."destination_ufps" as "destination_ufps",
 sch_irina_prikazchikova."mt_2"."index_ops" as "index_ops",
 sch_irina_prikazchikova."mt_2"."last_oper" as "last_oper",
 sch_irina_prikazchikova."mt_2"."measuretype_name" as "measuretype_name",
 sch_irina_prikazchikova."mt_2"."mrc" as "mrc",
 sch_irina_prikazchikova."mt_2"."ops_type" as "ops_type",
 sch_irina_prikazchikova."mt_2"."part_month" as "part_month",
 sch_irina_prikazchikova."mt_2"."pay_type_rcpn" as "pay_type_rcpn",
 sch_irina_prikazchikova."mt_2"."pay_type_sndr" as "pay_type_sndr",
 sch_irina_prikazchikova."mt_2"."payed_amt" as "payed_amt",
 sch_irina_prikazchikova."mt_2"."payed_amt_py" as "payed_amt_py",
 sch_irina_prikazchikova."mt_2"."payment_cnt" as "payment_cnt",
 sch_irina_prikazchikova."mt_2"."payment_cnt_py" as "payment_cnt_py",
 sch_irina_prikazchikova."mt_2"."payment_direction" as "payment_direction",
 sch_irina_prikazchikova."mt_2"."payment_flag" as "payment_flag",
 sch_irina_prikazchikova."mt_2"."pochtamt" as "pochtamt",
 sch_irina_prikazchikova."mt_2"."rcpn_name" as "rcpn_name",
 sch_irina_prikazchikova."mt_2"."receive_type" as "receive_type",
 sch_irina_prikazchikova."mt_2"."reception_mr" as "reception_mr",
 sch_irina_prikazchikova."mt_2"."reception_ops_type" as "reception_ops_type",
 sch_irina_prikazchikova."mt_2"."reception_ufps" as "reception_ufps",
 sch_irina_prikazchikova."mt_2"."recipient" as "recipient",
 sch_irina_prikazchikova."mt_2"."return_reason" as "return_reason",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."send_in_addition_flag"='Y' THEN '..........'
 WHEN sch_irina_prikazchikova."mt_2"."send_in_addition_flag"='N' THEN '..............'
 ELSE sch_irina_prikazchikova."mt_2"."send_in_addition_flag"
 END AS "send_in_addition_flag",
 sch_irina_prikazchikova."mt_2"."sender" as "sender",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."social_flag"='Y' THEN '....'
 WHEN sch_irina_prikazchikova."mt_2"."social_flag"='N' THEN '......'
 ELSE sch_irina_prikazchikova."mt_2"."social_flag"
 END AS "social_flag",
 sch_irina_prikazchikova."mt_2"."system_code" as "system_code",
 sch_irina_prikazchikova."mt_2"."tariff" as "tariff",
 sch_irina_prikazchikova."mt_2"."tariff_py" as "tariff_py",
 sch_irina_prikazchikova."mt_2"."tariff_val" as "tariff_val",
 sch_irina_prikazchikova."mt_2"."tariff_val_py" as "tariff_val_py",
 sch_irina_prikazchikova."mt_2"."ufps" as "ufps",
 substr(sch_irina_prikazchikova."mt_2"."accept_date",3 ,5) as "year_month",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."payment_cnt">0 and sch_irina_prikazchikova."mt_2"."measuretype_name"='........' then sch_irina_prikazchikova."mt_2"."tariff"
 else null
 END as "payment_incom",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."accept_cnt">0 and sch_irina_prikazchikova."mt_2"."measuretype_name"='........' then sch_irina_prikazchikova."mt_2"."tariff"
 else null
 END as "accept_incom",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."measuretype_name"='........' then sch_irina_prikazchikova."mt_2"."tariff"
 else null
 END as "plan_incom",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."measuretype_name"='........' then sch_irina_prikazchikova."mt_2"."tariff"
 else null
 END as "total_incom",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."measuretype_name"='........' then sch_irina_prikazchikova."mt_2"."tariff_val"
 else null
 END as "total_incom_val",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."pay_type_sndr" = '.................... ..........' and sch_irina_prikazchikova."mt_2"."category_name"='.................... ..............' and sch_irina_prikazchikova."mt_2"."client_type" in ('C-C', 'C-B')
 then sch_irina_prikazchikova."mt_2"."accept_cnt"
 else null
 END as "card_part_common",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."category_name"='.................... ..............' and sch_irina_prikazchikova."mt_2"."client_type" in ('C-C', 'C-B')
 then sch_irina_prikazchikova."mt_2"."accept_cnt"
 else null
 END as "total_transfer",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."pay_type_sndr" = '.................... ..........' and sch_irina_prikazchikova."mt_2"."category_name"='.................... ..............' and sch_irina_prikazchikova."mt_2"."client_type" = 'C-B'
 then sch_irina_prikazchikova."mt_2"."accept_cnt"
 else null
 END as "card_part_s_v",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."category_name"='.................... ..............' and sch_irina_prikazchikova."mt_2"."client_type" = 'C-B'
 then sch_irina_prikazchikova."mt_2"."accept_cnt"
 else null
 END as "total_transfer_s_v",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."pay_type_sndr" = '.................... ..........' and sch_irina_prikazchikova."mt_2"."category_name"='.................... ..............' and sch_irina_prikazchikova."mt_2"."client_type" = 'C-C'
 then sch_irina_prikazchikova."mt_2"."accept_cnt"
 else null
 END as "card_part_s_s",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."category_name"='.................... ..............' and sch_irina_prikazchikova."mt_2"."client_type" = 'C-C'
 then sch_irina_prikazchikova."mt_2"."accept_cnt"
 else null
 END as "total_transfer_s_s",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."pay_type_sndr" = '.................... ..........' and sch_irina_prikazchikova."mt_2"."measuretype_name" = '........'
 then sch_irina_prikazchikova."mt_2"."accepted_amt"
 else null
 END as "card_part_amt",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."pay_type_sndr" = '.................... ..........'
 then sch_irina_prikazchikova."mt_2"."accept_cnt"
 else null
 END as "card_part_cnt",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."pay_type_sndr" = '................ ............' and sch_irina_prikazchikova."mt_2"."measuretype_name" = '........'
 then sch_irina_prikazchikova."mt_2"."accepted_amt"
 else null
 END as "cash_part_amt",
 CASE
 WHEN sch_irina_prikazchikova."mt_2"."pay_type_sndr" = '................ ............'
 then sch_irina_prikazchikova."mt_2"."accept_cnt"
 else null
 END as "cash_part_cnt",
 nvl("card_part_common",0)/case when nvl("total_transfer",1)=0 then 1 end
 as card_share
 from sch_irina_prikazchikova."mt_2") AS sch_irina_prikazchikova_mt_2
 GROUP BY mrc
 ORDER BY mrc LIMIT 128', args=[], tx=true, thread='QueryPool-3210', fetchSize=64, queryTimeout=60}

roman_stepanov · 11.Сентябрь.2023 07:54:42

еще один случай:
в куб добавлены расчетные поля

формируется неверный запрос