在下面,您将找到三个示例表和数据以及一个查询。该示例看似人为的,但它是更大(近1500行)SQL查询的一部分。原始查询效果很好,但是在添加一些新功能时遇到了问题。
CREATE TABLE rule_table (
id_rule_table NUMBER (10),
name VARCHAR2 (24),
goal NUMBER (10),
amount NUMBER (10)
);
INSERT INTO rule_table (id_rule_table, name, goal, amount) VALUES(1, 'lorem', 2, 3);
INSERT INTO rule_table (id_rule_table, name, goal, amount) VALUES(2, 'ipsum', 3, 3);
INSERT INTO rule_table (id_rule_table, name, goal, amount) VALUES(3, 'dolor', 4, 3);
CREATE TABLE content_table (
id_content_table NUMBER (10),
name VARCHAR2 (24),
show_flag NUMBER (10)
);
INSERT INTO content_table (id_content_table, name, show_flag) VALUES(1, 'lorem', 0);
INSERT INTO content_table (id_content_table, name, show_flag) VALUES(2, 'ipsum', 1);
INSERT INTO content_table (id_content_table, name, show_flag) VALUES(3, 'dolor', 1);
CREATE TABLE module_table (
id_module_table NUMBER (10),
id_content_table NUMBER (10),
name VARCHAR2 (24),
amount NUMBER (10)
);
INSERT INTO module_table (id_module_table, id_content_table, name, amount) VALUES(1, 2, 'lorem', 10);
INSERT INTO module_table (id_module_table, id_content_table, name, amount) VALUES(2, 2, 'ipsum', 11);
INSERT INTO module_table (id_module_table, id_content_table, name, amount) VALUES(3, 2, 'dolor', 12);
SELECT RULE.id_rule_table
FROM rule_table RULE
WHERE (
CASE
WHEN RULE.goal <= (
WITH contentTbl (id_content_table)
AS (
SELECT id_content_table
FROM content_table
WHERE show_flag = 1
),
modulesTbl (id_content_table, id_module_table)
AS (
SELECT C.id_content_table, M.id_module_table
FROM contentTbl C
JOIN module_table M ON M.id_content_table = C.id_content_table
WHERE 4 < M.amount - RULE.amount
)
SELECT SUM(M.id_module_table)
FROM contentTbl C
JOIN modulesTbl M ON C.id_content_table = M.id_content_table
)
THEN 1
ELSE 0
END
) = 1;
DROP TABLE rule_table;
DROP TABLE content_table;
DROP TABLE module_table;
如果尝试这样做,您将收到错误ORA-00904:“ RULE”。“ AMOUNT”:无效的标识符。问题在于“ WHERE 4 <M.amount-RULE.amount”这一行。
如果在该行中将RULE.amount替换为某个数字(例如WHERE 4 <M.amount-3),则查询将正常运行。
如上所述,这是来自更大查询的摘要测试用例,因此查询的结构不能(或希望不需要)改变太多。也就是说,理想情况下,我正在寻找一种解决方案,该解决方案允许我在子查询中使用RULE.amount而不更改“ WHEN RULE.goal <=()”块内的SQL。
我正在尝试在Oracle 11g上运行它。
最后一件事,我试图在google和stackoverflow上搜索解决方案,但是我找不到正确的术语来描述我的问题。最接近的事物似乎是嵌套的相关子查询,但这似乎并不完全正确。
考虑到这只是一个更大查询的一部分,以下是完成这项工作所需的外科手术更改:
将WHERE 4 < M.amount - RULE.amount
条件移出CTE并移到主查询中,这样RULE
就在范围之内。
修改modulesTbl
CTE以返回附加列,amount
以便M.amount
现在可用于主查询。
经过这两个更改,查询将如下所示:
SELECT RULE.id_rule_table
FROM rule_table RULE
WHERE (
CASE
WHEN RULE.goal <= (
WITH contentTbl (id_content_table)
AS (
SELECT id_content_table
FROM content_table
WHERE show_flag = 1
),
modulesTbl (id_content_table, id_module_table, amount) -- add amount
AS (
SELECT C.id_content_table, M.id_module_table, M.amount -- add amount
FROM contentTbl C
JOIN module_table M ON M.id_content_table = C.id_content_table
)
SELECT SUM(M.id_module_table)
FROM contentTbl C
JOIN modulesTbl M ON C.id_content_table = M.id_content_table
AND 4 < M.amount - RULE.amount -- moved from CTE to here
)
THEN 1
ELSE 0
END
) = 1;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句