sql - 选择列表中的“DEV_EIMDW_BOJ_REPORTS.dbo.bojdeposit_tab.CUSTOMER_ID”列无效
问题描述
我正在尝试使用创建表并填充它的过程进行插入。错误是:列 'DEV_EIMDW_BOJ_REPORTS.dbo.bojdeposit_tab.CUSTOMER_ID' 在选择列表中无效,因为它既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
INSERT INTO
dbo.PortfolioTable(custIdTrn,portfolioValue,scheme_type,jmdvalue,jmdeq,usdeq,usdvalue,cadvalue,gbpvalue,eurvalue,usdoth)
select trn,portfolio_size, scheme_type, sum(jmdval), sum(jmdeq),sum(usdeq),
sum(usdvalue), sum(cadvalue), sum(gbpvalue), sum(eurvalue),sum(usdoth)
from
( SELECT
CASE WHEN b.trn IS NULL THEN b.customer_id ELSE b.trn END AS trn,
p.portfolio_size,
b.scheme_type,
case currency when 'JMD' then transaction_balance else 0 end jmdval,
case currency when 'JMD' then 0 else dbo.FN_ConvertAmountAsOfDate(@asAtDate, currency,'JMD',transaction_balance,'REV') end jmdeq,
case currency when 'JMD' then 0 else dbo.FN_ConvertAmountAsOfDate(@asAtDate, currency,'USD',transaction_balance,'REV') end usdeq,
case currency when 'USD' then transaction_balance else 0 end usdvalue ,
case currency when 'CAD' then transaction_balance else 0 end cadvalue ,
case currency when 'GBP' then transaction_balance else 0 end gbpvalue ,
case currency when 'EUR' then transaction_balance else 0 end eurvalue ,
case currency when 'JMD' then 0 when 'USD' then 0 when 'CAD' then 0 when 'GBP' then 0 when 'EUR' then 0
else dbo.FN_ConvertAmountAsOfDate(@asAtDate, currency,'USD',transaction_balance,'REV') end usdoth
FROM
[DEV_EIMDW_BOJ_REPORTS].[dbo].[bojdeposit_tab] b
INNER JOIN
[DEV_EIMDW_BOJ_REPORTS].[dbo].[BOJ_DEPOSIT_PORTFOLIO] p
ON
b.customer_id = p.cust_id
WHERE
NOT EXISTS (SELECT 1 FROM boj_deposits_exclusion e where e.acid = b.acid)
AND borrower_category_code IN (SELECT VALUE FROM string_split((SELECT borrowercat_code FROM M11_CATEGORY WHERE description = 'BUSFIRMS'),','))
AND sector_code IN (SELECT VALUE FROM string_split((SELECT sector_code FROM M11_CATEGORY WHERE description = 'BUSFIRMS'),','))
AND sub_sector_code IN (SELECT VALUE FROM string_split((SELECT sub_sector_code FROM M11_CATEGORY WHERE description = 'BUSFIRMS'),','))
AND not EXISTS (SELECT acid from [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_ALR] WHERE acct_label IN ('MUNDO', 'BOJCAMBIO', 'VOSTROACCT') AND acid = b.acid)
AND b.borrower_category_code != '500'
group by trn,portfolio_size, scheme_type
) AS Portfolio
解决方案
所有未参与聚合的列都必须在GROUP BY
子句中指定。事实上,原始查询有一个正确的GROUP BY
,但它被放置到错误的行并且必须移到外面。
调整后的查询:
INSERT INTO
dbo.PortfolioTable(custIdTrn,portfolioValue,scheme_type,jmdvalue,jmdeq,usdeq,usdvalue,cadvalue,gbpvalue,eurvalue,usdoth)
select trn,portfolio_size, scheme_type, sum(jmdval), sum(jmdeq),sum(usdeq),
sum(usdvalue), sum(cadvalue), sum(gbpvalue), sum(eurvalue),sum(usdoth)
from
( SELECT
CASE WHEN b.trn IS NULL THEN b.customer_id ELSE b.trn END AS trn,
p.portfolio_size,
b.scheme_type,
case currency when 'JMD' then transaction_balance else 0 end jmdval,
case currency when 'JMD' then 0 else dbo.FN_ConvertAmountAsOfDate(@asAtDate, currency,'JMD',transaction_balance,'REV') end jmdeq,
case currency when 'JMD' then 0 else dbo.FN_ConvertAmountAsOfDate(@asAtDate, currency,'USD',transaction_balance,'REV') end usdeq,
case currency when 'USD' then transaction_balance else 0 end usdvalue ,
case currency when 'CAD' then transaction_balance else 0 end cadvalue ,
case currency when 'GBP' then transaction_balance else 0 end gbpvalue ,
case currency when 'EUR' then transaction_balance else 0 end eurvalue ,
case currency when 'JMD' then 0 when 'USD' then 0 when 'CAD' then 0 when 'GBP' then 0 when 'EUR' then 0
else dbo.FN_ConvertAmountAsOfDate(@asAtDate, currency,'USD',transaction_balance,'REV') end usdoth
FROM
[DEV_EIMDW_BOJ_REPORTS].[dbo].[bojdeposit_tab] b
INNER JOIN
[DEV_EIMDW_BOJ_REPORTS].[dbo].[BOJ_DEPOSIT_PORTFOLIO] p
ON
b.customer_id = p.cust_id
WHERE
NOT EXISTS (SELECT 1 FROM boj_deposits_exclusion e where e.acid = b.acid)
AND borrower_category_code IN (SELECT VALUE FROM string_split((SELECT borrowercat_code FROM M11_CATEGORY WHERE description = 'BUSFIRMS'),','))
AND sector_code IN (SELECT VALUE FROM string_split((SELECT sector_code FROM M11_CATEGORY WHERE description = 'BUSFIRMS'),','))
AND sub_sector_code IN (SELECT VALUE FROM string_split((SELECT sub_sector_code FROM M11_CATEGORY WHERE description = 'BUSFIRMS'),','))
AND not EXISTS (SELECT acid from [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_ALR] WHERE acct_label IN ('MUNDO', 'BOJCAMBIO', 'VOSTROACCT') AND acid = b.acid)
AND b.borrower_category_code != '500'
-- the line below was commented out, because it should be stated outside!
-- group by trn,portfolio_size, scheme_type
) AS Portfolio
-- Moved outside from the inner query:
GROUP BY trn,portfolio_size, scheme_type
推荐阅读
- angular - textarea点击角度的输入验证
- python - 训练 CNN 时 For Loop 中断
- angular-slickgrid - 在 Angular-Slickgrid 中获取选定的行数据
- r - 使用函数 get() 以编程方式分配 quanteda docvars
- python.net - Python.Net 与我的应用程序一起分发:在线安装还是离线安装?
- react-native - 反应钩子表单设置默认值不起作用
- gradle - 空手道加特林:错误 io.gatling.app.Gatling$ - 运行崩溃,DataWriters 未正确初始化
- sql - 按减法表达式 sql 排序
- c - 在堆栈中交替显示顶部和底部
- typescript - 打字稿手动缩小子范围内的联合类型