在公式属性上使用投影

发布于 2024-07-21 20:32:42 字数 2711 浏览 5 评论 0原文

我的 XML 映射文件中有一个属性,例如

<property name="CreatedByLogin"  formula="(select x.fst_name || ' ' || x.last_name from sebl_dev.S_CONTACT x where x.ROW_ID=CREATED_BY)" type="String" />

,我想对这个特定名称使用投影,例如

.Add(Projections.Property("CreatedByLogin"), "CreatedByLogin")

,我确实遇到了 Oracle 异常。

有没有办法对使用公式声明的属性进行投影?

我生成的 SQL 是

select
     *
from (
     SELECT
         this_.ROW_ID as y0_,
         this_.CREATED as y1_,
         this_.CREATED_BY as y2_,
         this_.STATUS as y3_,
         this_.PSR_NUM as y4_,
         this_.ORDER_ID as y5_,
         this_.WFM_ID as y6_,
         this_.VIRTUAL_SS_ID as y7_,
         this_.VERIFIED_WITH as y8_,
         this_.REQ_DUE_DATE as y9_,
         this_.TYPE as y10_,
         account1_.NAME as y11_,
         account1_.OU_NUM as y12_,
         this_.DESCRIPTION as y13_,
         account1_.MARKET_TYPE_CD as y14_,
         account1_.X_SALES_CHANNEL as y15_,
         this_.QUOTE_ID as y16_,
         (
            select
                 x.fst_name || ' ' || x.last_name
            from sebl_dev.S_CONTACT x
            where x.ROW_ID=this_.CREATED_BY)
         as y17_,
         (
            select
                 x.fst_name || ' ' || x.last_name
            from sebl_dev.S_CONTACT x
            where x.ROW_ID=this_.LAST_UPD_BY)
         as y18_,
         serviceacc2_.INTEGRATION_ID as y19_,
         serviceacc2_.NAME as y20_,
         accountadd3_.ADDR as y21_,
         accountadd3_.ADDR_LINE_2 as y22_,
         accountadd3_.CITY as y23_,
         accountadd3_.STATE as y24_,
         accountadd3_.ZIPCODE as y25_
        FROM XOOE.XO_SS_HEADER this_
inner join XOOE.XO_SS_DETAILS this_1_
on this_.ROW_ID=this_1_.ROW_ID
 inner join sebl_dev.S_ORG_EXT account1_
on this_.CUST_ACCNT_ID=account1_.ROW_ID
left outer join sebl_dev.S_org_ext_x account1_1_
on account1_.ROW_ID=account1_1_.Row_id
 left outer join sebl_dev.S_ORG_EXT_UTX account1_2_
on account1_.ROW_ID=account1_2_.PAR_ROW_ID
inner join sebl_dev.S_ORG_EXT serviceacc2_ on this_.SERV_ACCNT_ID=serviceacc2_.ROW_ID
 left outer join sebl_dev.S_org_ext_x serviceacc2_1_
 on serviceacc2_.ROW_ID=serviceacc2_1_.Row_id
left outer join sebl_dev.S_ORG_EXT_UTX serviceacc2_2_
 on serviceacc2_.ROW_ID=serviceacc2_2_.PAR_ROW_ID
 inner join sebl_dev.S_ADDR_PER accountadd3_
on serviceacc2_.PR_ADDR_ID=accountadd3_.ROW_ID
        WHERE this_.TYPE = :p0 )

    where rownum <= :p1

,产生的异常是

InnerException    {"ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table"}    System.Exception {Oracle.DataAccess.Client.OracleException}

I have a property in my XML mapping file like

<property name="CreatedByLogin"  formula="(select x.fst_name || ' ' || x.last_name from sebl_dev.S_CONTACT x where x.ROW_ID=CREATED_BY)" type="String" />

and I want to use a projection on this particular name like

.Add(Projections.Property("CreatedByLogin"), "CreatedByLogin")

and I do get Oracle exception.

Is there a way to do projection on a property that is declared using a formula?

My generated SQL is

select
     *
from (
     SELECT
         this_.ROW_ID as y0_,
         this_.CREATED as y1_,
         this_.CREATED_BY as y2_,
         this_.STATUS as y3_,
         this_.PSR_NUM as y4_,
         this_.ORDER_ID as y5_,
         this_.WFM_ID as y6_,
         this_.VIRTUAL_SS_ID as y7_,
         this_.VERIFIED_WITH as y8_,
         this_.REQ_DUE_DATE as y9_,
         this_.TYPE as y10_,
         account1_.NAME as y11_,
         account1_.OU_NUM as y12_,
         this_.DESCRIPTION as y13_,
         account1_.MARKET_TYPE_CD as y14_,
         account1_.X_SALES_CHANNEL as y15_,
         this_.QUOTE_ID as y16_,
         (
            select
                 x.fst_name || ' ' || x.last_name
            from sebl_dev.S_CONTACT x
            where x.ROW_ID=this_.CREATED_BY)
         as y17_,
         (
            select
                 x.fst_name || ' ' || x.last_name
            from sebl_dev.S_CONTACT x
            where x.ROW_ID=this_.LAST_UPD_BY)
         as y18_,
         serviceacc2_.INTEGRATION_ID as y19_,
         serviceacc2_.NAME as y20_,
         accountadd3_.ADDR as y21_,
         accountadd3_.ADDR_LINE_2 as y22_,
         accountadd3_.CITY as y23_,
         accountadd3_.STATE as y24_,
         accountadd3_.ZIPCODE as y25_
        FROM XOOE.XO_SS_HEADER this_
inner join XOOE.XO_SS_DETAILS this_1_
on this_.ROW_ID=this_1_.ROW_ID
 inner join sebl_dev.S_ORG_EXT account1_
on this_.CUST_ACCNT_ID=account1_.ROW_ID
left outer join sebl_dev.S_org_ext_x account1_1_
on account1_.ROW_ID=account1_1_.Row_id
 left outer join sebl_dev.S_ORG_EXT_UTX account1_2_
on account1_.ROW_ID=account1_2_.PAR_ROW_ID
inner join sebl_dev.S_ORG_EXT serviceacc2_ on this_.SERV_ACCNT_ID=serviceacc2_.ROW_ID
 left outer join sebl_dev.S_org_ext_x serviceacc2_1_
 on serviceacc2_.ROW_ID=serviceacc2_1_.Row_id
left outer join sebl_dev.S_ORG_EXT_UTX serviceacc2_2_
 on serviceacc2_.ROW_ID=serviceacc2_2_.PAR_ROW_ID
 inner join sebl_dev.S_ADDR_PER accountadd3_
on serviceacc2_.PR_ADDR_ID=accountadd3_.ROW_ID
        WHERE this_.TYPE = :p0 )

    where rownum <= :p1

and the resulting exception is

InnerException    {"ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table"}    System.Exception {Oracle.DataAccess.Client.OracleException}

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

行至春深 2024-07-28 20:32:42

我最好的猜测:

您正在从没有定义键的表中选择一个键。 您必须弄清楚它是哪个表,并向其中添加主键字段。

My best guess:

you are selecting a key from a table that doesn't have key defined on it. You are going to have to figure out which table it is, and add the primary key field to it.

横笛休吹塞上声 2024-07-28 20:32:42

这是 Oracle 版本 9、10 和 11 早期版本中的一个已知错误。它显然在最新版本中已修复。

该问题是由这些旧版本处理 SQL-92 样式连接的方式中的错误引起的。 因此,如果无法升级,您可以使用旧式 SQL-89 连接来解决这些问题。 例如,INNER JOIN:

SELECT column
FROM table1 t1, table2 t2
WHERE t1.pk = t2.fk

另一个例子,这次是 LEFT JOIN:

SELECT column
FROM table1 t1, table2 t2
WHERE t1.pk = t2.fk(+)

更多信息:

http://awads.net/wp/2007/06/14/when-ansi-sql-join-syntax-does-not-work-in -oracle/

This is a known bug in Oracle versions 9, 10 and earlier versions of 11. It is apparently fixed in the latest version.

The problem is caused by a bug in the way these older versions process SQL-92 style joins. So if it is not possible to upgrade, you can work around them problem by using old style SQL-89 joins. For example, an INNER JOIN:

SELECT column
FROM table1 t1, table2 t2
WHERE t1.pk = t2.fk

Another example, this time for LEFT JOIN:

SELECT column
FROM table1 t1, table2 t2
WHERE t1.pk = t2.fk(+)

More info:

http://awads.net/wp/2007/06/14/when-ansi-sql-join-syntax-does-not-work-in-oracle/

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文