导入Oracle UNION ALL查看实体框架EDMX [英] Import Oracle UNION ALL View Into Entity Framework EDMX

问题描述

我有两个不同的表可以容纳两组不同的报表数据。然而，我想要有一个UNION ALL视图，所以我可以一起显示共同的列。我首先尝试单独列：

  SELECT NVL（ROW_NUMBER（）OVER（ORDER BY REPORT_PERIOD DESC，REPORT_ID DESC），0 ）ROW_ID，u.REPORT_ID，u。REPORT_PERIOD，u。EXCEL_DOC，u。XML_DOC，u。STATUS，u。CREATED_BY，u。CREATED_ON，u。MODIFIED_BY，u 。MODIFIED_ON
 FROM 
（
 SELECT（'​​DR'|| TO_CHAR（DISTRIBUTION_REPORT_ID））AS REPORT_ID，
 REPORT_PERIOD，
 EXCEL_DOC，
 XML_DOC ，
状态，
 CREATED_BY，
 CREATED_ON，
 MODIFIED_BY，
 MODIFIED_ON 
 FROM DISTRIBUTION_REPORT 
 
 UNION ALL 
 
 SELECT（'​​TR'|| TO_CHAR（TREATMENT_REPORT_ID））AS REPORT_ID，
 REPORT_PERIOD，
 EXCEL_DOC，
 XML_DOC，
 STATUS，
 CREATED_BY，
 CREATED_ON，
 MODIFIED_BY，
 MODIFIED_ON 
 FROM TREATMENT_REPORT 
）u 
  

Visual Studio不会让我补充，因为它不能推断一个主键。那么我添加了一个：

  ALTER VIEW VW_ALL_REPORT ADD CONSTRAINT VW_ALL_REPORT_PK PRIMARY KEY（ROW_ID）DISABLE 
  

然后我收到一个错误，表示键的所有部分必须不可为空。然后，我尝试添加一个rownumber（）列并尝试相同的事情，并得到相同的确切错误。

在每种情况下，Oracle似乎认为关键列是尽管如此，如果它是半途智慧的话，它是不可以的。所以它传递给这个不准确的Visual Studio。

如果双击视图，您将获得每列的属性。通知Nullable = Yes

< img src =https://i.stack.imgur.com/vupzH.pngalt =enter image description here>

我意识到我可以添加名为ALL_REPORT的第三个表，并且可以为其他两个表中的每一行添加行。但是，这真的好像浪费资源。任何想法？

---

我正在使用VS 2013，Oracle 11g和VB.NET。

---

解决方案

我不知道创建视图的可空性是什么。

另一方面，这个代码不应该在Oracle中工作：

  SELECT（'​​DR '+ TO_CHAR（DISTRIBUTION_REPORT_ID））AS REPORT_ID，
  

正确的配方是：

  SELECT（'​​DR'|| TO_CHAR（DISTRIBUTION_REPORT_ID））AS REPORT_ID，
  

+ 是一个算术运算符，对字符串无效。

I have two different tables that hold two different sets of report data. Yet I want to have a UNION ALL view so I can display the common columns together. I first tried making a separate column:

SELECT NVL(ROW_NUMBER() OVER (ORDER BY REPORT_PERIOD DESC, REPORT_ID DESC),0) ROW_ID,u.REPORT_ID,u."REPORT_PERIOD",u."EXCEL_DOC",u."XML_DOC",u."STATUS",u."CREATED_BY",u."CREATED_ON",u."MODIFIED_BY",u."MODIFIED_ON"
FROM 
(
  SELECT ('DR' || TO_CHAR(DISTRIBUTION_REPORT_ID)) AS REPORT_ID,
         REPORT_PERIOD,
         EXCEL_DOC,
         XML_DOC,
         STATUS,
         CREATED_BY,
         CREATED_ON,
         MODIFIED_BY,
         MODIFIED_ON
   FROM DISTRIBUTION_REPORT

   UNION ALL

  SELECT ('TR' || TO_CHAR(TREATMENT_REPORT_ID)) AS REPORT_ID,
         REPORT_PERIOD,
         EXCEL_DOC,
         XML_DOC,
         STATUS,
         CREATED_BY,
         CREATED_ON,
         MODIFIED_BY,
         MODIFIED_ON
  FROM TREATMENT_REPORT
) u

Visual Studio would not let me add that because it could not infer a primary key. So then I added one:

ALTER VIEW VW_ALL_REPORT ADD CONSTRAINT VW_ALL_REPORT_PK PRIMARY KEY (ROW_ID) DISABLE

I then got an error saying that all parts of the key must be non-nullable. I then tried adding a rownumber() column and tried the same things, and got the same exact errors.

In each case, Oracle seems to think that the key column is nullable, even though it shouldn't if it were half way intelligent. So it passes on this inaccuracy to Visual Studio.

If you double click the view you will get the properties for each column. Notice "Nullable = Yes"

I realize I could add a third table called "ALL_REPORT" and could just add rows for each row in the other two tables. However, this really seems like a waste of resources. Any ideas?

---

I am using VS 2013, Oracle 11g, and VB.NET.

---

解决方案

I have no idea what nullability would have to do with creating a view.

On the other hand, this code should not work in Oracle:

SELECT ('DR' + TO_CHAR(DISTRIBUTION_REPORT_ID)) AS REPORT_ID,

The correct formulation is:

SELECT ('DR' || TO_CHAR(DISTRIBUTION_REPORT_ID)) AS REPORT_ID,

+ is an arithmetic operator that doesn't work well on strings.

这篇关于导入Oracle UNION ALL查看实体框架EDMX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！