在公式属性上使用投影
我的 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我最好的猜测:
您正在从没有定义键的表中选择一个键。 您必须弄清楚它是哪个表,并向其中添加主键字段。
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.
这是 Oracle 版本 9、10 和 11 早期版本中的一个已知错误。它显然在最新版本中已修复。
该问题是由这些旧版本处理 SQL-92 样式连接的方式中的错误引起的。 因此,如果无法升级,您可以使用旧式 SQL-89 连接来解决这些问题。 例如,INNER JOIN:
另一个例子,这次是 LEFT JOIN:
更多信息:
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:
Another example, this time for LEFT JOIN:
More info:
http://awads.net/wp/2007/06/14/when-ansi-sql-join-syntax-does-not-work-in-oracle/