SQL查询各库存商品最新采购价报MAXTRDATE列不存在错误求助

问题原因 #

你遇到的SQL0205是IBM DB2数据库的典型标识符大小写匹配错误：子查询中给max(a7trdt)设置别名时使用了双引号"maxtrdate"，DB2中双引号包裹的标识符会严格区分大小写，外层查询引用b.maxtrdate时，数据库会默认将未加双引号的标识符转为大写查找MAXTRDATE，和子查询中的小写别名不匹配，因此报字段不存在错误。

解决方案 #

方案1：修复原有连接查询逻辑 #

去掉别名的双引号，同时按需补充日期格式化逻辑即可正常运行：

select
  a.a7itno as "Item No", 
  a.a7appr as "Unit Price", 
  -- 把YYYYMMDD格式的存储值转为DD/MM/YYYY格式，需要短横线分隔可替换第二个参数为'DD-MM-YYYY'
  TO_CHAR(TO_DATE(CAST(b.maxtrdate AS CHAR(8)), 'YYYYMMDD'), 'DD/MM/YYYY') as "Trans Date"
from m3edbprod.fcaavp a
left join
( select a7itno, max(a7trdt) as maxtrdate -- 去掉别名双引号，默认大小写不敏感可正常匹配
from m3edbprod.fcaavp
group by a7itno) b
on a.a7itno = b.a7itno and a.a7trdt = b.maxtrdate
where a.a7itno = '110ABC452'

如果同一个商品最新交易日存在多笔不同价格的记录，上述语句会返回所有当日的价格记录，如果需要仅取一条，可以额外补充排序过滤规则。

方案2：使用窗口函数（更简洁高效） #

用ROW_NUMBER()窗口函数直接按商品分组、按交易日期倒序排序，取每组第一条即可，不需要自连接，执行效率更高：

select 
  a7itno as "Item No",
  a7appr as "Unit Price",
  TO_CHAR(TO_DATE(CAST(a7trdt AS CHAR(8)), 'YYYYMMDD'), 'DD/MM/YYYY') as "Trans Date"
from (
  select 
    a7itno, a7appr, a7trdt,
    -- 按商品分组，交易日期倒序排序，同一天有多条记录可追加交易时间等字段排序取最新
    ROW_NUMBER() over (partition by a7itno order by a7trdt desc) as rn
  from m3edbprod.fcaavp
  where a7itno = '110ABC452' -- 过滤条件放内层可提前缩小数据范围，提升执行效率
) t
where rn = 1

如果需要查询全量商品的最新价格，去掉内层的where过滤条件即可。

内容的提问来源于stack exchange，提问作者Vincent Lee