我对SQL相当陌生，所以如果我没有使用正确的术语或遗漏了一些简单的东西，请原谅我!
我试图创建一个输出文件与RC_ID在左边，和成本类型在顶部与成本金额为每个RC_ID/成本类型组合求和。我试过使用子查询和使用WITH来创建一个CTE来引用。我使用任何一种方法都得到相同的错误。
当我使用Select *时，我得到"ORA-00918:列定义不明确，"即使为所有列命名。当我尝试列出要返回的特定字段时，我得到ORA-00904:无效标识符错误。
任何帮助将不胜感激!
我正在尝试这样的东西(在我的查询中列出的所有成本类型有更多的列，最终更多的RC行。
输入图片描述
这个SQL返回错误"ORA-00918:列定义不明确"。Select *">
WITH TABLE1 AS (
SELECT RC_ID, COST_TYPE AS COST_TYPE, SUM(AMOUNT) AS COST_AMOUNT, SUM(REC_AMT) AS COST_RELEASED
FROM REDACTED.RC_LN_COST_V
WHERE RC_ID = 1837161
GROUP BY RC_ID, COST_TYPE)
SELECT *
FROM TABLE1
PIVOT (SUM(COST_AMOUNT),SUM(COST_RELEASED) FOR COST_TYPE IN ('Commissions','Commissions Adjustment','Commissions Adjustment2','Fringe Commissions','Fringe Cost Adjustment','Fringe Cost Adjustment2','Install Costs Estimate',
'3rd Party License Costs','3rd Party Software Usage TERMLIC Costs'))
ORDER BY RC_ID;
当我尝试输入要从表1返回的实际列时，我在第二个Select语句上得到ORA-00904: "COST_RELEASED": invalid标识符。
注意-对于COST_NAME和COST_AMOUNT也会出现相同的错误。
WITH TABLE1 AS (
SELECT RC_ID, COST_TYPE AS COST_TYPE, SUM(AMOUNT) AS COST_AMOUNT, SUM(REC_AMT) AS COST_RELEASED
FROM REDACTED.RC_LN_COST_V
WHERE RC_ID = 1837161
GROUP BY RC_ID, COST_TYPE)
SELECT RC, COST_TYPE, COST_AMOUNT, COST_RELEASED
FROM TABLE1
PIVOT (SUM(COST_AMOUNT),SUM(COST_RELEASED) FOR COST_TYPE IN ('Commissions','Commissions Adjustment','Commissions Adjustment2','Fringe Commissions','Fringe Cost Adjustment','Fringe Cost Adjustment2','Install Costs Estimate',
'3rd Party License Costs','3rd Party Software Usage TERMLIC Costs'))
ORDER BY RC_ID;
当你有值时，你需要别名它们，这样它们就不会含糊不清
WITH TABLE1 AS (
SELECT RC_ID, COST_TYPE AS COST_TYPE, SUM(AMOUNT) AS COST_AMOUNT, SUM(REC_AMT) AS COST_RELEASED
FROM REDACTED.RC_LN_COST_V
WHERE RC_ID = 1837161
GROUP BY RC_ID, COST_TYPE)
SELECT *
FROM TABLE1
PIVOT (SUM(COST_AMOUNT) AS SUM1,SUM(COST_RELEASED) AS SUM2 FOR COST_TYPE IN ('Commissions','Commissions Adjustment','Commissions Adjustment2','Fringe Commissions','Fringe Cost Adjustment','Fringe Cost Adjustment2','Install Costs Estimate',
'3rd Party License Costs','3rd Party Software Usage TERMLIC Costs'))
ORDER BY RC_ID;
扩展一下@nbk的答案，如果您简化当前查询，使您只有一个和和一个枢轴值:
PIVOT (SUM(COST_AMOUNT) FOR COST_TYPE IN ('Commissions'))
，那么您将看到结果集以一个名为'Commissions'的列结束(带有引号和大小写)。如果你得到的是另一个和:
PIVOT (SUM(COST_RELEASED) FOR COST_TYPE IN ('Commissions'))
…然后在结果集中得到完全相同的列名。如果两者都做:
PIVOT (SUM(COST_AMOUNT), SUM(COST_RELEASED) FOR COST_TYPE IN ('Commissions'))
…然后得到两个具有相同名称的结果列，这就是ORA-00918错误出现的地方。正如@nbk所说，如果您别名聚合:
PIVOT (SUM(COST_AMOUNT) AS SUM1,SUM(COST_RELEASED) AS SUM2 FOR COST_TYPE IN ('Commissions'))
…由于列名现在是'Commissions'_SUM1和'Commissions'_SUM2，所以这些列名是唯一的，因此解决了歧义。很丑，但很独特。
如果您使用的是只支持标识符长度为30个字符(或字节)的旧版本的Oracle，则会出现进一步的问题。有些枢轴值超过了这个长度;例如，'3rd Party Software Usage TERMLIC Costs'将生成一个45个字符的列名'3rd Party Software Usage TERMLIC Costs'_SUM1。在30个字符的限制下，这些标识符名称将被截断，因此'3rd Party Software Usage TERMLIC Costs'_SUM1和'3rd Party Software Usage TERMLIC Costs'_SUM2都变成'3rd Party Software Usage TERM，并且您又回到了歧义名称和ORA-00918。
您可以避免这种可能性，并且还可以使列名更容易使用(因为它们不必被视为引号标识符)，方法是将值也混叠，例如:
PIVOT (
SUM(COST_AMOUNT) AS amt, SUM(COST_RELEASED) AS rel
FOR COST_TYPE IN (
'Commissions' AS comm,
'Commissions Adjustment' AS comm_adj,
'Commissions Adjustment2' AS comm_adj2,
'Fringe Commissions' AS fringe_comm,
'Fringe Cost Adjustment' AS fringe_comm_adj,
'Fringe Cost Adjustment2' AS fring_comm_adj2,
'Install Costs Estimate' AS inst_cost_est,
'3rd Party License Costs' AS tp_lic_cost,
'3rd Party Software Usage TERMLIC Costs' AS tp_su_term_lic_cost
)
)
获取列名称COMM_AMT,COMM_REL,COMM_ADJ_AMT，…，TP_SU_TERM_LIC_COST_AMT和TP_SU_TERM_LIC_COST_REL.
您也不需要CTE，因此您可以简化为:
SELECT *
FROM RC_LN_COST_V
PIVOT (
SUM(AMOUNT) AS amt, SUM(REC_AMT) AS rel
FOR COST_TYPE IN (
'Commissions' AS comm,
'Commissions Adjustment' AS comm_adj,
'Commissions Adjustment2' AS comm_adj2,
'Fringe Commissions' AS fringe_comm,
'Fringe Cost Adjustment' AS fringe_comm_adj,
'Fringe Cost Adjustment2' AS fring_comm_adj2,
'Install Costs Estimate' AS inst_cost_est,
'3rd Party License Costs' AS tp_lic_cost,
'3rd Party Software Usage TERMLIC Costs' AS tp_su_term_lic_cost
)
)
WHERE RC_ID = 1837161
ORDER BY RC_ID;
在21c中出现，在11g中出现同样的情况，表明它在应用了所有别名的情况下仍然有效。(倒数第三个查询，没有值别名，仍然在11g中得到ORA-00918，因为有30字节的限制。)
当然，你可以根据自己的需要缩写它们。您可以将自己的列别名分配给外部选择列表中生成的名称-而不是SELECT *，您可以执行SELECT RC_ID, COMM_AMT AS "Commisions COST_AMOUNT", ...-尽管在旧版本中您仍然被限制为30字节的别名，并且这种别名，特别是带引号的标识符，通常属于显示/报告层。
当我尝试输入要从表1返回的实际列时，我得到ORA-00904: "COST_RELEASED":无效标识符
这是因为这些列名在枢轴后的投影中不存在。它们被枢轴操作"消耗"，因为它们在聚合函数和FOR子句中被引用。唯一保留在投影中的原始列是RC_ID，因为它不是pivot子句的一部分。有效地将主透视聚合按非主透视列分组。