对连接字符串的计算字段求和(拆分acct#s)

Question

首先：我正在使用"from Microsoft Query"和直接SQL查询Excel 2007中IBM I System包中内置的DB2服务器。这是一个金融数据库。

DB的结构使得账号被划分为8个整数字段，分别用于基金、部门、部门、活动1、活动2、元素1、元素2和对象。为了使查询结果可读，我将它们与以下代码连接起来：

select 
right(trim(trailing from (concat('00',cast(T1."GMFUND" as Char(8))))),3)||'-'
||right(trim(trailing from (concat('00',cast(t1."GMDPT" as Char(8))))),2) 
||right(trim(trailing from (concat('00',cast(T1."GMDIV" as Char(8))))),2) ||'-'
||right(trim(trailing from (concat('00',cast(T1."GMSTAB" as Char(8))))),2)
||right(trim(trailing from (concat('00',cast(T1."GMSTAS" as Char(8))))),1)||'.'
||right(trim(trailing from (concat('00',cast(T1."GMELM1" as Char(8))))),1)
||right(trim(trailing from (concat('00',cast(T1."GMELM2" as Char(8))))),1)||'-'
||right(trim(trailing from (concat('00',cast(T1."GMOBJ" as Char(8))))),2)
as AJAccount

读起来有点困难，所以我会解释：对于每个字段，我将"00"连接到每个#类型转换为一个8个字符的字符串(添加前导零)，然后修剪尾部空格(来自类型转换)，最后使用right()为每个字段获取正确的数字#。这最终构成了一个单独的账户#，我们每天都以以下格式使用：

000-0000-000.00-00

现在是更复杂的部分。我需要对借记和贷记的计算字段求和，定义如下：

IFNULL(T1."GMDAMT",0)-IFNULL(T1."GMCAMT",0) as Trans_Total

但这还没有总结。这就是我遇到问题的地方。当我查询这两个字段时，我会得到数据库中每个事务的准确结果，标记为w/acct#s。但是，当我试图将[借记-信用]字段包含在SUM()聚合函数中并通过"AJAccount"进行分组时，我的查询抛出了一个错误。

错误指向我的级联acct#字段的以下部分：

right(trim (trailing from (concat('00',cast(T1."GMOBJ" as Char(8))))),2)

它特别强调"GMOBJ"是错误的来源。但如果没有SUM()函数，代码运行得很好，生成的结果如下所示：

001-0000-243.00-00  |  166898.00
001-0000-244.00-00  | -166898.00    
161-0000-243.00-00  |    3000.00     
161-0000-244.00-00  |   -3000.00    
470-0000-243.00-00  |    4999.00     
470-0000-244.00-00  |   -4999.00    
490-0000-243.00-00  |    1000.00     
490-0000-244.00-00  |   -1000.00

这甚至不是我真正需要做的一半(因为事务数据按事务类型划分为4个不同的表，我最终需要加入这些表…但这是另一项任务)。但在我完成这项工作之前，我是一个死胡同。

这是整个代码块：

select 
right(trim(trailing from (concat('00',cast(T1."GMFUND" as Char(8))))),3)||'-'
||right(trim(trailing from (concat('00',cast(t1."GMDPT" as Char(8))))),2) 
||right(trim(trailing from (concat('00',cast(T1."GMDIV" as Char(8))))),2) ||'-'
||right(trim(trailing from (concat('00',cast(T1."GMSTAB" as Char(8))))),2)
||right(trim(trailing from (concat('00',cast(T1."GMSTAS" as Char(8))))),1)||'.'
||right(trim(trailing from (concat('00',cast(T1."GMELM1" as Char(8))))),1)
||right(trim(trailing from (concat('00',cast(T1."GMELM2" as Char(8))))),1)||'-'
||right(trim(trailing from (concat('00',cast(T1."GMOBJ" as Char(8))))),2)
as AJAccount,
SUM(IFNULL(T1."GMDAMT",0)-IFNULL(T1."GMCAMT",0)) as Trans_Total
from "HTEDTA"."GM310AP" T1
where T1."GMAPYR" = 2014
group by 'AJAccount'

我想我可能打字错了，所以我做了以下更简单的查询：

select 
T1."GMFUND",
SUM(IFNULL(T1."GMDAMT",0)-IFNULL(T1."GMCAMT",0)) as Trans_Total
from "HTEDTA"."GM310AP" T1
where T1."GMAPYR" = 2014 and T1."GMAPMO" between 1 and 4
group by T1."GMFUND"
order by T1."GMFUND"

它运行得很好，产生了以下结果：

1  |  20090901.49  
111  |     32635.15  
114  |         0.00  
115  |         0.00  
131  |      5916.66

所以我怀疑我缺乏关于计算字段和SUM()的知识。有人能启发我吗？

Answer 1

Am假设错误为SQL0206-未找到Column或全局变量AJAccount。

如果为true，则问题在于GROUP BY子句在数据库表中查找列，而不是SELECT子句中的派生列。尝试将整个派生列放入GROUP BY：中

select...
where...
group by right(trim (trailing from (concat('00',cast(T1."GMFUND" as Char(8))))),3) || '-' || right(trim (trailing from (concat('00',cast(t1."GMDPT" as Char(8))))),2) || right(trim (trailing from (concat('00',cast(T1."GMDIV" as Char(8))))),2) || '-' ||  right(trim (trailing from (concat('00',cast(T1."GMSTAB" as Char(8))))),2) || right(trim (trailing from (concat('00',cast(T1."GMSTAS" as Char(8))))),1) || '.' || right(trim (trailing from (concat('00',cast(T1."GMELM1" as Char(8))))),1) || 
right(trim (trailing from (concat('00',cast(T1."GMELM2" as Char(8))))),1) || '-' || right(trim (trailing from (concat('00',cast(T1."GMOBJ" as Char(8))))),2) 
order by 1

Answer 2

使用通用表表达式的简化示例：

WITH gm310ap AS (SELECT 
RIGHT('00'||DIGITS(gmfund),3) || '-' ||
RIGHT('0'||DIGITS(gmdpt),2) || 
RIGHT('0'||DIGITS(gmdiv),2) || '-' || 
RIGHT('0'||DIGITS(gmstab),2) ||
RIGHT(DIGITS(gmstas),1) || '.' ||
RIGHT(DIGITS(gmelm1),1) ||
RIGHT(DIGITS(gmelm2),1) || '-' ||
RIGHT('0'||DIGITS(gmobj),2) AS AJAccount,
COALESCE(gmdamt,0) - COALESCE(gmcamt,0) AS Trans_Total
FROM "HTEDTA"."GM310AP"
WHERE gmapyr = 2014)
SELECT AJAccount, SUM(Trans_Total)
FROM gm310ap
GROUP BY AJAccount

标量函数的文档：

• 合作伙伴
• 十二月
• 数字
• 权利

Answer 3

如果您显示的代码正是您运行的代码，那么您的查询就有一个简单的语法问题。你说GROUP BY 'AJAccount'是一个字符串文字。双引号("AJAccount")将使其成为区分大小写的列引用。但是，当您说as AJAaccount时，您没有使用双引号，因此名称不区分大小写，因此您不需要GROUP BY.中的引号

假设你的列至少和你试图提取的部分一样长，我会尝试以下查询：

SELECT (  RIGHT(DIGITS(gmfund),3) ||'-' 
|| RIGHT(DIGITS(gmdpt),2)
|| RIGHT(DIGITS(gmdiv),2) || '-'
|| RIGHT(DIGITS(gmstab),2)
|| RIGHT(DIGITS(gmstas),1) || '.'
|| RIGHT(DIGITS(gmelm1),1)
|| RIGHT(DIGITS(gmelm2),1) || '-'
|| RIGHT('0'||DIGITS(gmobj),2)
) as AJAccount,
sum(gmdamt) - sum(gmcamt) AS Trans_Total
FROM "HTEDTA"."GM310AP"
WHERE gmapyr = 2014
GROUP BY AJAccount

我的猜测是，可能还有很多其他查询会使用这个串联的帐号。如果是这种情况，您可能会考虑创建一个视图来存储此逻辑，以便在其他地方使用，可能如下所示：

CREATE OR REPLACE VIEW HTEDTA.GM310AP_AC
SELECT (  RIGHT(DIGITS(gmfund),3) ||'-' 
|| RIGHT(DIGITS(gmdpt),2)
|| RIGHT(DIGITS(gmdiv),2) || '-'
|| RIGHT(DIGITS(gmstab),2)
|| RIGHT(DIGITS(gmstas),1) || '.'
|| RIGHT(DIGITS(gmelm1),1)
|| RIGHT(DIGITS(gmelm2),1) || '-'
|| RIGHT('0'||DIGITS(gmobj),2)
) as AJAccount,
gmdamt,
gmcamt      -- include any other useful columns
FROM HTEDTA.GM310AP

一旦创建了它，您就可以编写诸如之类的查询

SELECT AJAccount,
SUM( IFNULL(gmdamt,0) - IFNULL(gmcamt) ) as Trans_Total
FROM HTEDTA.GM310AP_AC
GROUP BY AJAccount
ORDER BY AJAccount

或

WITH Q AS
( SELECT AJAccount,
SUM(gmdamt) as TotD,
SUM(gmcamt) as TotC
FROM HTEDTA.GM310AP_AC
GROUP BY AJAccount
)
SELECT AJAccount, 
TotD - TotC   as Trans_Total
FROM Q
ORDER BY AJAccount

附言：只有当这些列可以为null时，才需要IFNULL。这在DB2fori上传统上相当罕见，所以如果您检查了这一点，您可能会为自己简化一些事情。