Using Projections on Formula Properties

Question

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}

Answer 1

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.