SAP HANA:创建一个最大启用日期的连接

请检查以下HANA db SQLScript好吗

我用过 multiple SQL CTE expressions on HANA SQLScript 获取每种货币兑换为欧元的最新条目 然后将这个CTE表(最后一个CTE3)连接到VBAK表

实际上,我并没有使用货币汇率进行金额转换,我认为您可以使用选择列表中的乘法或除法等进行处理

with cte as (
    select 
        to_date( to_nvarchar(99999999 - gdatu) ) gdate,
        * 
    from "SAPS4S".TCURR
    where tcurr = 'EUR'
), cte2 as (
    select 
        row_number() over (partition by fcurr, YEAR(gdate), MONTH(gdate) order by gdate desc) as rn,
         YEAR(gdate) as gdate_year, 
         MONTH(gdate) as gdate_month,
        * 
    from cte
), cte3 as (
    select * from cte2 where rn = 1
)
select 
    vbeln,
    erdat,
    netwr,
    waerk,
    cte3.* 
from "SAPS4S".VBAK as vbak
left join cte3
    on 
        vbak.waerk = cte3.fcurr and
        YEAR(vbak.erdat) = cte3.gdate_year and
        MONTH(vbak.erdat) = cte3.gdate_month;

你好,厄尼, 根据您的第二条评论,我对SQLScript查询做了如下更改

with cte as (
    select 
        to_date( to_nvarchar(99999999 - gdatu) ) gdate,
        * 
    from "SAPABAP1".TCURR
    where tcurr = 'EUR'
), cte2 as (
select 
    vbeln,
    erdat,
    netwr,
    waerk,
    sum(1) over (partition by vbeln order by gdate desc rows unbounded preceding) as rownum,
    cte.*
from "SAPABAP1".VBAK as vbak
left join cte
    on 
        vbak.waerk = cte.fcurr and
        vbak.erdat >= cte.gdate
)
select *
from cte2 
where ifnull(rownum,1) = 1 

如果它能在你的数据库上运行并得到你的反馈,我会很高兴的

TCURR表中有空记录,因为没有货币汇率条目,或者文档货币已定义为欧元(实际上,汇率应等于1)