选择具有关键字和内部查询的查询给出错误- ORA-00979

Question

loan_transaction表中有插入类事务的记录。因此，amount列可以有正负两个值。

Tableloan_transactionsomething like below

<表类> id set_off_id 数量account_idtbody><<tr>111002321-10023

关键字HAVING后面应该有一个聚合函数。

解决方案是将该表达式合并到现有的Where子句中(根本没有第一个Having子句)，或者将当前Having子句中select语句所做的选择进行聚合。

我只是试着模拟你的代码(有一些语法错误引起错误)-和我自己的惊喜它工作用三种不同的方法(包括你自己的方法)

以下是模拟:

--  First - your way (with corrections)
SELECT ad.ID      -- you have wrong alias here - lad.id -> lad does not exist
FROM 
(Select 2 "ID", 'ASDS' "ACCOUNT_STATUS_CODE" From Dual) ad  -- acting like your account_detail table
WHERE 
ad.ACCOUNT_STATUS_CODE = 'ASDS'
HAVING 
(
SELECT COUNT(DISTINCT lto.TXN_DATE)
FROM  (
Select '2X' "ID", 2 "ACCOUNT_ID", To_Date('01.11.2022', 'dd.mm.yyyy') "TXN_DATE", '2X' "SET_OFF_ID", -12 "AMOUNT" From Dual Union All
Select '2X' "ID", 2 "ACCOUNT_ID", To_Date('02.11.2022', 'dd.mm.yyyy') "TXN_DATE", '2X' "SET_OFF_ID", 14 "AMOUNT" From Dual
) lto  -- acting like your loan_transaction table
WHERE lto.ID IN   (
SELECT lti.SET_OFF_ID
FROM  (
Select '2X' "ID", 2 "ACCOUNT_ID", To_Date('01.11.2022', 'dd.mm.yyyy') "TXN_DATE", '2X' "SET_OFF_ID", -12 "AMOUNT" From Dual Union All
Select '2X' "ID", 2 "ACCOUNT_ID", To_Date('02.11.2022', 'dd.mm.yyyy') "TXN_DATE", '2X' "SET_OFF_ID", 14 "AMOUNT" From Dual
) lti   -- acting like your loan_transaction table (Again)
WHERE lti.ACCOUNT_ID = ad.ID
GROUP BY lti.SET_OFF_ID   -- you have lt.set_off_id  --> lt alias does not exist
HAVING SUM(lti.AMOUNT) > 0)
) > 1;
--  
--  Second - incorporate it into existing Where clause
--  change HAVING with AND
...
WHERE 
ad.ACCOUNT_STATUS_CODE = 'ASDS'
AND 
(
SELECT COUNT(DISTINCT lto.TXN_DATE)
FROM  (
...
--
-- Third - aggregate the selection of query in your HAVING clause
--
...
HAVING 
Max((
SELECT COUNT(DISTINCT lto.TXN_DATE)
FROM  (
Select '2X' "ID", 2 "ACCOUNT_ID", To_Date('01.11.2022', 'dd.mm.yyyy') "TXN_DATE", '2X' "SET_OFF_ID", 12 "AMOUNT" From Dual Union All
Select '2X' "ID", 2 "ACCOUNT_ID", To_Date('02.11.2022', 'dd.mm.yyyy') "TXN_DATE", '2X' "SET_OFF_ID", 12 "AMOUNT" From Dual
) lto
WHERE lto.ID IN   (
SELECT lti.SET_OFF_ID
FROM  (
Select '2X' "ID", 2 "ACCOUNT_ID", To_Date('01.11.2022', 'dd.mm.yyyy') "TXN_DATE", '2X' "SET_OFF_ID", -12 "AMOUNT" From Dual Union All
Select '2X' "ID", 2 "ACCOUNT_ID", To_Date('02.11.2022', 'dd.mm.yyyy') "TXN_DATE", '2X' "SET_OFF_ID", 14 "AMOUNT" From Dual
) lti
WHERE lti.ACCOUNT_ID = ad.ID
GROUP BY lti.SET_OFF_ID
HAVING SUM(lti.AMOUNT) > 0)
)) > 1;
--
-- Result for each of the above simulations is the same
--
ID
----------
2

问候……