我正在尝试将选择和更新合并到一个语句中
在select中,我试图根据表"month_dim"
并使用当前日期查找当前会计年度的会计期间。它运行良好,因为它有相应的值。但我需要将select to update语句中的值传递到同一个表中,以更新CY
、PY
和YTD
的值。目前在更新语句中,我正在进行硬编码,并有3个单独的更新语句。如果它也能组合成一个就好了。
select fiscal_period, fiscal_year from "schema_name"."month_dim" where date_sql = current_date
更新语句如下:-
UPDATE "Schema_name"."month_dim" SET
YTD = case when fiscal_period <= '1'
then 'Y' else 'N' end;
UPDATE " Schema_name "."month_dim" SET
CY = case when fiscal_year >= '2021'
then 'Y' else 'N' end;
UPDATE " Schema_name "."month_dim" SET
PY = case when fiscal_year <= '2021'
then 'Y' else 'N' end;
请帮助
感谢
这就是您想要的吗?:
DO
BEGIN
DECLARE FISCALPERIOD NVARCHAR(3);
DECLARE FISCALYEAR NVARCHAR(4);
SELECT
fiscal_period, fiscal_year
INTO fiscalperiod, fiscalyear
FROM
schema_name.month_dim
WHERE
date_sql = current_date;
UPDATE schema_name.month_dim
SET
YTD = CASE
WHEN fiscal_period <= :fiscalperiod THEN 'Y'
ELSE 'N'
END
, CY = CASE
WHEN fiscal_year >= :fiscalyear THEN 'Y'
ELSE 'N'
END
, PY = CASE
WHEN fiscal_year <= :fiscalyear THEN 'Y'
ELSE 'N'
END;
END;