查询工作时 ASP.NET DataAdapter 查询无效

Question

大约 6 个月前，我开始在我的 ASP.net Web 应用程序中使用 DataSet。它是一个漂亮的工具，允许我快速开发 MVC 应用程序，而不必做数据库连接/查询中的所有脏活。

但今天我遇到了一些奇怪的问题。它从以下查询开始：

select a.MR_PART_CODE as PART_CODE,

       b.PART_DESC as PART_DESC, 
       b.PM_MAD_CAT_CODE as CATEGORY, 
       c.MPC_MIN_QTY as CAT_SS, 
       a.MR_MAX_LEAD_TIME as LEAD_TIME, 
       a.MR_MAD as MAD, 
       ROUND((a.MR_MAD * a.MR_MAX_LEAD_TIME)) as CAL_SS, 
       greatest(ROUND((a.MR_MAD * a.MR_MAX_LEAD_TIME)),c.MPC_MIN_QTY) as SS,
       d.SOH as SOH, 
       d.SOO as SOO,
       (select sum(back_order) from STK_REQUEST where part_code=b.part_code) as BO,
       (d.SOH+a.MR_SOO) as AVAIL,
       ((d.SOH + a.MR_SOO)-greatest(ROUND((a.MR_MAD * a.MR_MAX_LEAD_TIME)),c.MPC_MIN_QTY)) as ROQ,
       (d.SOH - greatest(ROUND((a.MR_MAD * a.MR_MAX_LEAD_TIME)),c.MPC_MIN_QTY) ) as VAR,
       a.MR_REMARKS as REMARKS           
    from ROQ a, PART_MASTER b, MAD_PARTS_CATEGORY c, PART_STATS d
    where a.MR_PART_CODE = b.PART_CODE
    and d.PART_CODE = b.PART_CODE
    and b.PM_MAD_CAT_CODE = c.MPC_CAT_CODE
    and b.RETIRE_FLAG = 'N'
    and a.mr_year = (select max(mr_year) from roq)
    and a.mr_month = (select max(mr_month) from roq where mr_year= (select max(mr_year) from roq))
    and a.mr_period = (select max(mr_period) from roq where mr_month=(select max(mr_month) from roq where mr_year= (select max(mr_year) from roq)) and mr_year= (select max(mr_year) from roq))
    and     greatest(ROUND((a.MR_MAD * a.MR_MAX_LEAD_TIME)),c.MPC_MIN_QTY) > d.SOH`

该查询在 Toad for Oracle 中运行良好，但显然当我尝试在 DataAdapter 对象中设置为新查询时它失败了。它对这一行说类似“函数参数列表中的错误：SELECT 无法识别”之类的内容： (select sum(back_order) from STK_REQUEST where part_code=b.part_code) as BO

我做错了什么？

仅供参考，数据库是Oracle。

Answer 1

这似乎是某些 ASP 类试图解析您的 SQL 的异常。如果消息来自 Oracle，则会出现 ORA-xxxxx 错误号。

你不应该把这样的 SQL 放在 ASP 中。相反，创建一个视图，也许就可以了。