我有一个名为F0008 Schema的表。它有14列,我可以在其中找到开始日期(采用JDE Date格式),该日期可以从列名称中得知我的财政年度是什么月份。
这是一个示例,该示例将为我返回4的Test314的测试日期为2020年11月9日:
DECLARE @TestDate INT
SET @TestDate = 120314
SELECT CASE
WHEN F0008.CDD01J >= @TestDate THEN 1
WHEN F0008.CDD02J >= @TestDate THEN 2
WHEN F0008.CDD03J >= @TestDate THEN 3
WHEN F0008.CDD04J >= @TestDate THEN 4
WHEN F0008.CDD05J >= @TestDate THEN 5
WHEN F0008.CDD06J >= @TestDate THEN 6
WHEN F0008.CDD07J >= @TestDate THEN 7
WHEN F0008.CDD08J >= @TestDate THEN 8
WHEN F0008.CDD09J >= @TestDate THEN 9
WHEN F0008.CDD10J >= @TestDate THEN 10
WHEN F0008.CDD11J >= @TestDate THEN 11
WHEN F0008.CDD12J >= @TestDate THEN 12
WHEN F0008.CDD13J >= @TestDate THEN 13
WHEN F0008.CDD14J >= @TestDate THEN 14
END AS int_num
FROM [ODS].[PRODDTA].[F0008] F0008
where F0008.CDDTPN = 'B'
and F0008.CDFY = 20
然后,我有了一个名为F42119 Schema的订单表。该表中有一列称为SDIVD,它是JDE格式的更新日期。我可以从该表中获取日期范围,如下所示:
SELECT distinct F42119.SDIVD
FROM [ODS].[PRODDTA].[F42119] F42119
WHERE F42119.SDIVD BETWEEN 120314 AND 120334
order by 1
我需要做的是结合这两个查询的结果,因此当我从F42119中提取日期列表时,我也可以从F0008中提取case语句的对应结果。
简而言之,我需要知道F42119上任何交易的财务月份号。
除了看上去令人恐惧之外,还有什么阻止您将其扔到子查询中的功能吗?
SELECT distinct F42119.SDIVD,
(
SELECT CASE
WHEN F0008.CDD01J >= F42119.SDIVD THEN 1
WHEN F0008.CDD02J >= F42119.SDIVD THEN 2
WHEN F0008.CDD03J >= F42119.SDIVD THEN 3
WHEN F0008.CDD04J >= F42119.SDIVD THEN 4
WHEN F0008.CDD05J >= F42119.SDIVD THEN 5
WHEN F0008.CDD06J >= F42119.SDIVD THEN 6
WHEN F0008.CDD07J >= F42119.SDIVD THEN 7
WHEN F0008.CDD08J >= F42119.SDIVD THEN 8
WHEN F0008.CDD09J >= F42119.SDIVD THEN 9
WHEN F0008.CDD10J >= F42119.SDIVD THEN 10
WHEN F0008.CDD11J >= F42119.SDIVD THEN 11
WHEN F0008.CDD12J >= F42119.SDIVD THEN 12
WHEN F0008.CDD13J >= F42119.SDIVD THEN 13
WHEN F0008.CDD14J >= F42119.SDIVD THEN 14
END AS int_num
FROM [ODS].[PRODDTA].[F0008] F0008
where F0008.CDDTPN = 'B'
and F0008.CDFY = 20
-- NOTE: you could also hide this in a function to make your life simpler
) as mySubqueryResult
FROM [ODS].[PRODDTA].[F42119] F42119
WHERE F42119.SDIVD BETWEEN 120314 AND 120334
order by 1
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句