我正在尝试创建一个报告显示订单迟到,到期月,下个月等。使用DB2 SQL从IBM I Server(AS/400(提取数据。但是,我遇到了比较操作员的问题。我有一个潜在的解决方案,但是它是如此乏味,以至于它会使我的代码大大更长,并且很容易出现错误。
例如,我正在按请求日期提取客户订单:
SELECT C6D0NB /* REQUEST DATE */
FROM MBC6REP /* CUSTOMER ORDER FILE */
WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */
ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */
导致:
Request
date
118/08/28
118/06/29
118/06/15
118/06/07
....
117/07/21
117/06/14
117/04/14
117/03/01
因此,此"请求日期"(C6D0NB(字段在我的ERP系统(Infor XA(中标记为"日期"格式。我可以使用比较操作员使用静态比较(例如,2018年1月1日至2018年1月15日之间,包括(:
SELECT C6D0NB /* REQUEST DATE */
FROM MBC6REP /* CUSTOMER ORDER FILE */
WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */ AND
C6D0NB >= '1180101' AND
C6D0NB <= '1180115'
ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */
结果:
Request
date
118/01/15
118/01/15
118/01/12
118/01/12
....
118/01/03
118/01/03
118/01/03
118/01/02
但是,当我想使用"当前日期"功能时,我会遇到错误。当我查询时:
SELECT CURRENT DATE
FROM SYSIBM.SYSDUMMY1
我收到预期的:
CURRENT DATE
02/12/18
我可以按预期使用日,月,年的功能:
SELECT DAY(CURRENT DATE)
FROM SYSIBM.SYSDUMMY1
返回:
DAY
12
我不能结合两个:
SELECT C6D0NB /* REQUEST DATE */
FROM MBC6REP /* CUSTOMER ORDER FILE */
WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */ AND
C6D0NB >= CURRENT DATE
ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */
给我错误:
Comparison operator >= operands not compatible.
如果我尝试使用"请求日期"的月份,
SELECT MONTH(C6D0NB) /* REQUEST DATE */
FROM MBC6REP /* CUSTOMER ORDER FILE */
WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */
ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */
我收到此错误:
Argument 1 of function MONTH not valid.
例如,我如何在当月的所有日期中创建动态报告?我可以运行以下代码,但是非常乏味:
SELECT C6D0NB /* REQUEST DATE */
FROM MBC6REP /* CUSTOMER ORDER FILE */
WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */ AND
MONTH(TO_DATE((CONCAT(
CONCAT(
(CONCAT(SUBSTRING(C6D0NB,4,2), '/')),
(CONCAT(SUBSTRING(C6D0NB,6,2), '/'))),
SUBSTRING(C6D0NB,2,2))), 'MM/DD/YY')) = MONTH(CURRENT DATE) AND
YEAR(TO_DATE((CONCAT(
CONCAT(
(CONCAT(SUBSTRING(C6D0NB,4,2), '/')),
(CONCAT(SUBSTRING(C6D0NB,6,2), '/'))),
SUBSTRING(C6D0NB,2,2))), 'MM/DD/YY')) = YEAR(CURRENT DATE)
ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */
这确实是编码它的最简洁的方法吗?
谢谢!
c6d0nb实际上不是日期列,它是一个数字列,其值由您的erp解释为cyymmdd格式的日期。
IBM I及其先前的化身,iSeries和As/400已经支持了真实的日期类型。
不幸的是,它们没有在许多较旧的应用中使用...
最佳解决方案,使用UDF从数字值转换为实际日期。您可以写自己的书,或下载Alan Campin的Idate工具...
然后您将可以简单地做...
SELECT C6D0NB /* REQUEST DATE */
FROM MBC6REP /* CUSTOMER ORDER FILE */
WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */ AND
idate(C6D0NB,'*CYMD') >= CURRENT DATE
ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */
实际上,从性能的角度来看,最好将日期转换为cyymmdd数字。艾伦的软件包包括这样的功能...
SELECT C6D0NB /* REQUEST DATE */
FROM MBC6REP /* CUSTOMER ORDER FILE */
WHERE C6DCCD = '1' /* ONLY ORDERS, NOT QUOTES */ AND
C6D0NB >= convertToiDate(CURRENT DATE, '*CYMD')
ORDER BY C6D0NB DESC /* DATES DESCENDING ORDER */
这允许使用C6D0NB上的索引...
另外,您可以创建一个"日期"或"日历"表。除了将其从数字日期转换为实际日期类型外,它对任何DB中的许多其他基于集合的操作都很有用。
看着cyymmdd日期不太漂亮,但是可以使用内置的varchar_format函数来完成。
我要从位置2进行比较,因为那是21世纪的" 1"。
select * from mytable
where mycyymmdd date =
substring( varchar_format(current timestamp,'CCYY/MM/DD'), 2)
从这里重复出于轻松查找:
由于没有任何变体适合我的需求,所以我自己出来了。
这很简单:
select * from yourschema.yourtable where yourdate = int(CURRENT DATE - 1 days) - 19000000;
此days
是全年的leap,并且最适合最需要的东西。
可以将days
转到months
或years
。不需要像VARCHAR_FORMAT
/TIMESTAMP_FORMAT
这样的重型火炮。