ASP.NET Oracle查询未正确结束？

Question

我是ASP.NET和Oracle的新手。我正在尝试创建一个长SQL字符串(使用UNION ALL)，然后将该字符串传递给处理其余部分的Data类。这个问题肯定存在于我的SQL字符串中。

下面是我代码的第一部分，它运行得完美无缺：

sql = "SELECT 'DCS - HCA (81)', HCA_SENT_DCS, HCA_VALID_DCS, HCA_SUSPEND_DCS, HCA_REJECTED_DCS,"
sql &= " SUM(HCA_VALID_DCS + HCA_SUSPEND_DCS + HCA_REJECTED_DCS) As ""Total Returned"","
sql &= " RESOLVED_CNT, SUM(HCA_REJECTED_DCS - RESOLVED_CNT) As ""To Do"""
sql &= " FROM HUB_CDI_CONTROL_NBRS WHERE REPORT_DATE = :0 "
_param.Add(dateSelected.ToString("dd-MMM-yyyy"))
sql &= " GROUP BY HCA_SENT_DCS, HCA_VALID_DCS, HCA_SUSPEND_DCS, HCA_REJECTED_DCS,"
sql &= " RESOLVED_CNT"

但是，当我尝试使用另一个UNION语句对这个SQL进行SELECT时，我会得到以下异常：

ORA-00933: SQL command not properly ended

以下是完整的代码：

sql = "SELECT 'DCS - HCA (81)', HCA_SENT_DCS, HCA_VALID_DCS, HCA_SUSPEND_DCS, HCA_REJECTED_DCS,"
sql &= " SUM(HCA_VALID_DCS + HCA_SUSPEND_DCS + HCA_REJECTED_DCS) As ""Total Returned"","
sql &= " RESOLVED_CNT, SUM(HCA_REJECTED_DCS - RESOLVED_CNT) As ""To Do"""
sql &= " FROM HUB_CDI_CONTROL_NBRS WHERE REPORT_DATE = :0 "
sql &= " GROUP BY HCA_SENT_DCS, HCA_VALID_DCS, HCA_SUSPEND_DCS, HCA_REJECTED_DCS,"
sql &= " RESOLVED_CNT"

sql &= " UNION ALL"

sql &= "SELECT 'SFDC - HCA (82)', HCA_SENT_SFDC, HCA_VALID_SFDC, HCA_SUSPEND_SFDC, HCA_REJECTED_SFDC,"
sql &= " SUM(HCA_VALID_SFDC + HCA_SUSPEND_SFDC + HCA_REJECTED_SFDC) As ""Total Returned"","
sql &= " RESOLVED_CNT, SUM(HCA_REJECTED_SFDC - RESOLVED_CNT) As ""To Do"""
sql &= " FROM HUB_CDI_CONTROL_NBRS WHERE REPORT_DATE = :0 "
sql &= " GROUP BY HCA_SENT_SFDC, HCA_VALID_SFDC, HCA_SUSPEND_SFDC, HCA_REJECTED_SFDC,"
sql &= " RESOLVED_CNT"

_param.Add(dateSelected.ToString("dd-MMM-yyyy"))
_DH.TheSQL = sql
_DT = _DH.GetTableWithParameters(_param.ToArray)

我关注这个代码的两个部分：

1. 我是否正确地调用了_param.Add()函数？由于我使用:0两次引用该参数，是否只需要使用_param.Add()函数一次？
2. 我觉得我使用GROUP BY语法是错误的。当我试图在不使用GROUP BY的情况下运行查询时，甲骨文对我大喊大叫，但我不确定我是否正确地使用了它们。实际上，我只是将每一列i SELECTed添加到GROUP BY中。

否则，我肯定还有其他的错误我错过了。请帮帮我！

谢谢。

Answer 1

sql &= " UNION ALL"
sql &= "SELECT 'SFDC - HCA (82)', HCA_SENT_SFDC, HCA_VALID_SFDC, HCA_SUSPEND_SFDC, HCA_REJECTED_SFDC,"

结果在中间的某个地方：

... UNION ALLSELECT 'SFDC - HCA (82)', HCA_SENT_SFDC, ...

注：“联合ALLSELECT”

​

其他修复程序的完整代码。我不认为你真的想要分组，加/减，你不需要和()函数。要在UNION中有效，第一部分的所有列都必须命名/别名。

​

sql = "SELECT 'DCS - HCA (81)' Title, HCA_SENT_DCS, HCA_VALID_DCS, HCA_SUSPEND_DCS, HCA_REJECTED_DCS,"
sql &= " HCA_VALID_DCS + HCA_SUSPEND_DCS + HCA_REJECTED_DCS As ""Total Returned"","
sql &= " RESOLVED_CNT, HCA_REJECTED_DCS - RESOLVED_CNT As ""To Do"""
sql &= " FROM HUB_CDI_CONTROL_NBRS WHERE REPORT_DATE = :0 "
sql &= " UNION ALL "
sql &= "SELECT 'SFDC - HCA (82)', HCA_SENT_SFDC, HCA_VALID_SFDC, HCA_SUSPEND_SFDC, HCA_REJECTED_SFDC,"
sql &= " HCA_VALID_SFDC + HCA_SUSPEND_SFDC + HCA_REJECTED_SFDC As ""Total Returned"","
sql &= " RESOLVED_CNT, HCA_REJECTED_SFDC - RESOLVED_CNT As ""To Do"""
sql &= " FROM HUB_CDI_CONTROL_NBRS WHERE REPORT_DATE = :1 "

_param.Add(dateSelected.ToString("dd-MMM-yyyy"))
_param.Add(dateSelected.ToString("dd-MMM-yyyy"))  ' 2nd one
_DH.TheSQL = sql
_DT = _DH.GetTableWithParameters(_param.ToArray)