So I am having this error:
while converting CASE WHEN `g`.`GPNActivityVK.GPNExpired` = TRUE THEN 'Просрочено' WHEN `g`.`GPNActivityVK.GPNExpired` = FALSE AND `g2`.`GPNActivityStatus.Name` = SOME ('{"На исполнении", "На доработке"}') THEN 'Выполняется' WHEN `g`.`GPNActivityVK.GPNExpired` = FALSE AND `g2`.`GPNActivityStatus.Name` = SOME ('{"Выполнено – на согласовании в ДВАКР", "Выполнено – на согласовании в бизнесе"}') THEN 'Выполнено' WHEN `g2`.`GPNActivityStatus.Name` = 'Закрыто' AND `g3`.`name` = SOME ('{"Проверено", "Проверено камерально", "Не применимо"}') THEN 'Закрыто - проверено' WHEN `g2`.`GPNActivityStatus.Name` = 'Закрыто' AND `g3`.`name` = 'Руководство берет на себя риск' THEN 'Менеджмент берет на себя риск' ELSE NULL END.
This is part of this sql query:
SELECT
"GPNActivityVKId" ,
CASE
when "GPNActivityVK.GPNExpired" = True then 'Просрочено'
when "GPNActivityVK.GPNExpired" = False and "GPNActivityStatus.Name" = ANY('{"На исполнении", "На доработке"}') then 'Выполняется'-- должно быть "В работе", но такого статуса нет.
when "GPNActivityVK.GPNExpired" = False and "GPNActivityStatus.Name" = ANY('{"Выполнено – на согласовании в ДВАКР", "Выполнено – на согласовании в бизнесе"}') then 'Выполнено'
when "GPNActivityStatus.Name" = 'Закрыто' and g3.name = ANY('{"Проверено", "Проверено камерально", "Не применимо"}') then 'Закрыто - проверено'
when "GPNActivityStatus.Name" = 'Закрыто' and g3.name = 'Руководство берет на себя риск' then 'Менеджмент берет на себя риск'
end ActivityStatus
from "cvg"."dm_cvg".gpnactivityvk g
left join "cvg"."dm_cvg".gpnactivitystatus g2 on g."GPNActivityVK.GPNStatusID" = g2."GPNActivityStatus.ID"
left join "cvg"."dm_cvg".gpnactivityvkclosectatus g3 on g."GPNActivityVK.GPNCloseStatusId" = g3."id"
Query itself works fine. Error seems to occure only when I am joing this query with another query (which alone works fine as well). For example this query (the other variations wil do the same) will return aforementioned error:
with t6 as (SELECT
"GPNActivityVKId" ,
CASE
when "GPNActivityVK.GPNExpired" = True then 'Просрочено'
when "GPNActivityVK.GPNExpired" = False and "GPNActivityStatus.Name" = ANY('{"На исполнении", "На доработке"}') then 'Выполняется'-- должно быть "В работе", но такого статуса нет.
when "GPNActivityVK.GPNExpired" = False and "GPNActivityStatus.Name" = ANY('{"Выполнено – на согласовании в ДВАКР", "Выполнено – на согласовании в бизнесе"}') then 'Выполнено'
when "GPNActivityStatus.Name" = 'Закрыто' and g3.name = ANY('{"Проверено", "Проверено камерально", "Не применимо"}') then 'Закрыто - проверено'
when "GPNActivityStatus.Name" = 'Закрыто' and g3.name = 'Руководство берет на себя риск' then 'Менеджмент берет на себя риск'
end ActivityStatus
from "cvg"."dm_cvg".gpnactivityvk g
left join "cvg"."dm_cvg".gpnactivitystatus g2 on g."GPNActivityVK.GPNStatusID" = g2."GPNActivityStatus.ID"
left join "cvg"."dm_cvg".gpnactivityvkclosectatus g3 on g."GPNActivityVK.GPNCloseStatusId" = g3."id"),
t1 as (SELECT
"rp"."GPNRiskProcessId",
CASE
WHEN "kp"."GPNNewGuaranteesRequired" = 'true' THEN 1
WHEN "kp"."GPNNewGuaranteesRequired" = 'false' THEN 0
ELSE NULL
END AS "GPNNewGuaranteesRequired",
"rf"."GPNRiskFactorId",
"kp"."GPNKeyControlProcedureId",
"kp"."GPNKeyControlProcedure.GPNName",
"concat"(CAST("GPNRiskFactorInRisk.GPNNumberCVG" AS INTEGER),
'.',
CAST("GPNKeyControlProcedure.GPNNumberCVG" AS INTEGER)) AS "GPNNumberCVG",
"rl"."GPNRiskLack.GPNName",
"av"."GPNActivityVK.GPNName",
"av"."GPNActivityVKId",
CAST("av"."GPNActivityVK.GPNNumberCVG" AS INTEGER) AS "GPNActivityVK.GPNNumberCVG",
"av"."GPNActivityVK.Owner",
CAST("av"."GPNActivityVK.GPNFinishDate" AS DATE) AS "GPNActivityVK.GPNFinishDate",
CASE
WHEN "av"."GPNActivityVK.GPNStatusIndex" = 1 THEN 'Просрочено'
WHEN "av"."GPNActivityVK.GPNStatusIndex" = 2 THEN 'В работе'
WHEN "av"."GPNActivityVK.GPNStatusIndex" = 3 THEN 'Выполнено'
ELSE NULL
END AS "Status",
CASE
WHEN "GPNRiskLack.GPNName" IS NULL
AND "GPNActivityVK.GPNName" IS NULL
AND "GPNActivityVK.Owner" IS NULL
AND "GPNActivityVK.GPNFinishDate" IS NULL THEN 0
ELSE 1
END AS "nullcheck"
FROM
"cvg"."dm_cvg"."GPNRiskProcess" AS "rp"
LEFT JOIN "cvg"."dm_cvg"."GPNRiskFactorInRisk" AS "rf" ON
"rf"."GPNRiskProcessId" = "rp"."GPNRiskProcessId"
LEFT JOIN "cvg"."dm_cvg"."GPNKeyControlProcedure" AS "kp" ON
"rf"."GPNRiskFactorId" = "kp"."GPNRiskFactorId"
LEFT JOIN "cvg"."dm_cvg"."GPNRiskLack" AS "rl" ON
"kp"."GPNKeyControlProcedureId" = "rl"."GPNKeyControlProcedureId"
LEFT JOIN "cvg"."dm_cvg"."GPNActivityVK" AS "av" ON
"av"."GPNLackinRiskId" = "rl"."GPNLackinRiskId"
WHERE
"GPNOrganizationalElementId" IN (
SELECT
"GPNObjectProcessId"
FROM
"cvg"."dm_cvg"."GPNObjectProcess"))
select * from t6 left join t1 on t1.GPNActivityVKId = t6.GPNActivityVKId
HOWEVER this query works completely fine if I am using dbeaver and connectin to db directly.
Any help would be highly appreciated!
