Ошибка "Invalid token. Dynamic SQL Error. SQL error code = -104. Invalid command. Count of column list and variable list do not match."
Примечание:
SELECT
"ControllersList"."ControllerName" AS "Контроллер",
sum("AccountingCurrent"."Pay") AS "Задолженность",
count(CASE when "AccountingCurrent"."PayType" = 99 and "AccountingCurrent"."Pay" > 0 and "AccountingCurrent"."Pay" < 50 then "AccountingCurrent"."Pay" end) AS "0-50",
count(CASE when "AccountingCurrent"."PayType" = 99 and "AccountingCurrent"."Pay" >= 50 and "AccountingCurrent"."Pay" < 200 then "AccountingCurrent"."Pay" end) AS "50-200",
count(CASE when "AccountingCurrent"."PayType" = 99 and "AccountingCurrent"."Pay" >= 200 then "AccountingCurrent"."Pay" end) AS "Выше 200"
FROM
"AccountingCurrent"
LEFT OUTER JOIN "Abonents" ON ("Abonents"."Abonent_Account" = "AccountingCurrent"."Abonent_Account")
LEFT OUTER JOIN "ControllersList" ON ("ControllersList"."Controller_ID" = "Abonents"."Controller_ID")
WHERE
"AccountingCurrent"."Pay" > 0 AND
"AccountingCurrent"."PayType" = 99 AND
"ControllersList"."Controller_ID" IN (33,34,150,135,7,15,256,257,139,140,9,17,124,125,11,19,10,18,43,42,114,115,141,137)
GROUP BY
"ControllersList"."ControllerName"
UNION
SELECT
COUNT(*) AS "Всего лс",
SUM("RPT_TempAbonentsAddr"."CountersFlag") AS "Счетчиков"
FROM
"Abonents"
LEFT OUTER JOIN "ControllersList" ON ("ControllersList"."Controller_ID" = "Abonents"."Controller_ID")
LEFT OUTER JOIN "RPT_TempAbonentsAddr" ON ("RPT_TempAbonentsAddr"."Abonent_Account" = "Abonents"."Abonent_Account")
WHERE
"ControllersList"."Controller_ID" IN (33,34,150,135,7,15,256,257,139,140,9,17,124,125,11,19,10,18,43,42,114,115,141,137)
GROUP BY
"ControllersList"."ControllerName"
Примечание:
Извиняюсь, нарушена целостность. вот полная версия моего скрипта.
SELECT
COUNT(*) AS "Всего лс",
SUM("RPT_TempAbonentsAddr"."CountersFlag") AS "Счетчиков"
FROM
"Abonents"
LEFT OUTER JOIN "ControllersList" ON ("ControllersList"."Controller_ID" = "Abonents"."Controller_ID")
LEFT OUTER JOIN "RPT_TempAbonentsAddr" ON ("RPT_TempAbonentsAddr"."Abonent_Account" = "Abonents"."Abonent_Account")
WHERE
"ControllersList"."Controller_ID" IN (33,34,150,135,7,15,256,257,139,140,9,17,124,125,11,19,10,18,43,42,114,115,141,137)
GROUP BY
"ControllersList"."ControllerName"
UNION
SELECT
"ControllersList"."ControllerName" AS "Контроллер",
sum("AccountingCurrent"."Pay") AS "Задолженность",
count(CASE when "AccountingCurrent"."PayType" = 99 and "AccountingCurrent"."Pay" > 0 and "AccountingCurrent"."Pay" < 50 then "AccountingCurrent"."Pay" end) AS "0-50",
count(CASE when "AccountingCurrent"."PayType" = 99 and "AccountingCurrent"."Pay" >= 50 and "AccountingCurrent"."Pay" < 200 then "AccountingCurrent"."Pay" end) AS "50-200",
count(CASE when "AccountingCurrent"."PayType" = 99 and "AccountingCurrent"."Pay" >= 200 then "AccountingCurrent"."Pay" end) AS "Выше 200"
FROM
"AccountingCurrent"
LEFT OUTER JOIN "Abonents" ON ("Abonents"."Abonent_Account" = "AccountingCurrent"."Abonent_Account")
LEFT OUTER JOIN "ControllersList" ON ("ControllersList"."Controller_ID" = "Abonents"."Controller_ID")
WHERE
"AccountingCurrent"."Pay" > 0 AND
"AccountingCurrent"."PayType" = 99 AND
"ControllersList"."Controller_ID" IN (33,34,150,135,7,15,256,257,139,140,9,17,124,125,11,19,10,18,43,42,114,115,141,137)
GROUP BY
"ControllersList"."ControllerName"
Примечание:
То есть, уравнять количество столбцов в первом и втором запросах? Можете поправить в скрипте!?
Примечание:
В итоге у меня строки Null идут, а затем мои данные.. И отсутствую столбцы из моего следующего запроса