为什么一直显示此错误：ORA-00936：缺少表达式

CREATE OR REPLACE FORCE EDITIONABLE VIEW  "VU_REPORT5" ("Selling Report") AS
  SELECT  a2.FIRST_NAME || a2.SUR_NAME ||', living in ' || a4.COUNTRY ||
            ', ' || a4.CITY || ' ' || a4.LINE_1 || a4.LINE_2 || 
            a4.LINE_3 || a4.LINE_4 || ', bought an ' || a1.MAKE || '  ' ||
            a1.MODEL || ' from employee ' || a3.FIRST_NAME || ' ' || 
            a3.SUR_NAME || ' at ' || a1.SOLD_DATE || ', Making a profit of ' ||
            to_char(a1.SOLD_PRICE - a1.PURCHASE_PRICE) ||' pounds.'  AS "Selling Report",
          to_char(SELECT sum(a5.SOLD_PRICE)-sum(a5.PURCHASE_PRICE)
                    FROM CAR a5
                    where (to_date(a5.SOLD_DATE,'mm-dd-yyyy') <= to_date(a1.SOLD_DATE,'mm-dd-yyyy'))
                    ORDER BY a5.SOLD_DATE
                    GROUP BY a5.SOLD_DATE) AS "OVERALL Report"
FROM CAR a1,
     CUSTOMER a2,
     staff a3,
     ADDRESS a4
WHERE a1.BOUGHT_BY_CUSTOMER_NO = a2.CUSTOMER_NO and
      a1.SOLD_BY_STAFF_NO = a3.STAFF_NO and
      a4.ADDRESS_NO = a2.ADDRESS_NO
ORDER BY a1.SOLD_DATE

1 个解决方案

#1

One issue is that the view is defined as returning a single column ("Selling Report"), but the query actually returns two columns ("Selling Report" and "OVERALL Report").

一个问题是视图被定义为返回单个列(“销售报告”),但查询实际上返回两列(“销售报告”和“总体报告”)。

Another issue is that you can't put a sub-select into a function call; in this case

另一个问题是你不能把一个子选择放入一个函数调用;在这种情况下

to_char(SELECT sum(a5.SOLD_PRICE)-sum(a5.PURCHASE_PRICE)
                    FROM CAR a5
                    where (to_date(a5.SOLD_DATE,'mm-dd-yyyy') <= to_date(a1.SOLD_DATE,'mm-dd-yyyy'))
                    ORDER BY a5.SOLD_DATE
                    GROUP BY a5.SOLD_DATE)

simply isn't valid. I'm not sure what you're trying to do with this sub-query so I can't really advise you on how to correct the problem.

根本无效。我不确定你要对这个子查询做什么,所以我不能真正建议你如何纠正这个问题。

Best of luck.

祝你好运。

#1