ORACLE SQL-在WHERE子句中使用带有GROUP BY无效标识符的SUM

Matt 发表于 Dev

马特

我在ORACLE SQL中有一个查询，仅在debet_amount或credit_amount的总和大于或等于每个v_no 1000时，才需要返回结果。

当前错误

ORA-00904: "g"."COMPANY": invalid identifier
00904. 00000 -  "%s: invalid identifier"

当前查询

SELECT DISTINCT
g.company,
g.v_type,
g.v_no,
g.v_date,
g.a_year,
g.a_period, 
g.entry_date,
g.correction,
CASE WHEN g.mcompany IS NULL THEN 'FALSE' ELSE g.mcompany END AS mcompany,
g.debet_amount,
g.credit_amount,
g.amount,
v.userid
FROM genrow g, 
(SELECT v_no, SUM(debet_amount) AS debcomp FROM genrow GROUP BY v_no) subdeb,
(SELECT v_no, SUM(credit_amount) AS credcomp FROM genrow GROUP BY v_no) subcred
INNER JOIN genv v ON g.v_no = v.v_no AND g.company = v.company
WHERE g.v_no = subdeb.v_no
AND g.v_no = subcred.v_no
AND (subdeb.debcomp >= 1000 or subcred.credcomp >= 1000)
AND g.v_type IN ('M','Q','E')

预期输出示例

company v_type  v_no    v_date          a_year  a_period    entry_date  correction  mcompnay    debet_amount    credit_amount   amount  userid
02      EXT     2000    25-JAN-14       2014    1           28-JAN-14   N           FALSE       600                             600     USER13
02      EXT     2000    25-JAN-14       2014    1           28-JAN-14   N           FALSE       200                             200     USER16
02      EXT     2000    25-JAN-14       2014    1           28-JAN-14   N           FALSE       300                             300     USER12
02      EXT     4000    25-JAN-14       2014    1           28-JAN-14   N           FALSE                       500             500     USER19
02      M       4000    29-MAR-14       2014    3           07-APR-14   N           FALSE                       200             200     USER12
02      EXT     4000    25-JAN-14       2014    1           28-JAN-14   N           FALSE                       120             120     USER18
02      EXT     4000    25-JAN-14       2014    1           28-JAN-14   N           FALSE                       700             700     USER14
02      EXT     6000    25-JAN-14       2014    1           28-JAN-14   N           FALSE       1100                            1100    USER19

托斯滕·凯特纳（Thorsten Kettner）

问题在于您正在混合无法正确解析的隐式和显式联接。

这应该工作：

SELECT DISTINCT
    g.company,
    g.v_type,
    g.v_no,
    g.v_date,
    g.a_year,
    g.a_period, 
    g.entry_date,
    g.correction,
    CASE WHEN g.mcompany IS NULL THEN 'FALSE' ELSE g.mcompany END AS mcompany,
    g.debet_amount,
    g.credit_amount,
    g.amount,
    v.userid
FROM genrow g
INNER JOIN (SELECT v_no, SUM(debet_amount) AS debcomp FROM genrow GROUP BY v_no) subdeb ON g.v_no = subdeb.v_no
INNER JOIN (SELECT v_no, SUM(credit_amount) AS credcomp FROM genrow GROUP BY v_no) subcred ON g.v_no = subcred.v_no
INNER JOIN genv v ON g.v_no = v.v_no AND g.company = v.company
WHERE (subdeb.debcomp >= 1000 or subcred.credcomp >= 1000)
AND g.v_type IN ('M','Q','E');

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。