对公式有一点乐趣，这让我发疯。 我们有一些代码通过公式获取实体，数据库中没有链接，需要在运行时完成，因此@Formula用法。 以下是该类的外观：
@Entity @Table(name="T_EMPLOYEE_COMPANY_ASSIGNMENT" ) public class EmployeeCompanyAssignment implements java.io.Serializable { public EmployeeCompanyAssignment() { } @EmbeddedId @AttributeOverrides( { @AttributeOverride(name="employeeId", column=@Column(name="EMPLOYEE_ID", nullable=false, precision=10, scale=0) ), @AttributeOverride(name="companyId", column=@Column(name="COMPANY_ID", nullable=false, precision=10, scale=0) ) } ) public EmployeeCompanyAssignmentId getId() { return this.id; } @ManyToOne(fetch=FetchType.LAZY) @JoinColumn(name="EMPLOYEE_ID", nullable=false, insertable=false, updatable=false) public Employee getEmployee() { return this.employee; } public void setEmployee(Employee employee) { this.employee = employee; } @ManyToOne(fetch=FetchType.LAZY) @JoinColumn(name="COMPANY_ID", nullable=false, insertable=false, updatable=false) public Company getCompany() { return this.company; } public void setCompany(Company company){ this.company = company; } @Column(name="DEFAULT_WBS_SAP_CODE", length=24) public String getDefaultWbsSapCode() { return this.defaultWbsSapCode; } public void setDefaultWbsSapCode(String defaultWbsSapCode){ this.defaultWbsSapCode = defaultWbsSapCode; } @Column(name="DEFAULT_COST_CENTER_SAP_CODE", length=10) public String getDefaultCostCenterSapCode() { return this.defaultCostCenterSapCode; } public void setDefaultCostCenterSapCode(String defaultCostCenterSapCode){ this.defaultCostCenterSapCode = defaultCostCenterSapCode; } .... }公式以这种方式设置：
@Formula(value = "(SELECT * FROM(" + "Select *" + " from T_COST_CENTER cc" + " where cc.is_blocked_in_sap = 0" + " and cc.is_deleted_in_sap = 0" + " and cc.company_id in" + " (SELECT TECA.COMPANY_ID" + " FROM T_EMPLOYEE_COMPANY_ASSIGNMENT TECA" + " WHERE TECA.EMPLOYEE_ID = EMPLOYEE_ID)" + " and LTRIM(" + "(SELECT *" + " FROM (select TECA.DEFAULT_COST_CENTER_SAP_CODE" + " from T_EMPLOYEE_COMPANY_ASSIGNMENT TECA" + " WHERE TECA.EMPLOYEE_ID = EMPLOYEE_ID)" + " where ROWNUM = 1)" + ", '0') = LTRIM(cc.sap_code, '0')" + " order by cc.sap_code DESC" + ")" + " WHERE ROWNUM = 1" + ")") public CostCenter getDefaultCostCenter() { return defaultCostCenter; } public void setDefaultCostCenter(CostCenter defaultCostCenter ) { this.defaultCostCenter = defaultCostCenter ; } @Formula(value="(" + "SELECT *" + " FROM (Select wbs.*" + " from T_WORK_BREAKDOWN_STRUCTURE wbs" + " LEFT OUTER JOIN T_Company c" + " on wbs.company_id = c.id" + " where wbs.sap_code in (select TECA.DEFAULT_WBS_SAP_CODE" + //We shouldn't have to do this this, but whenever I try to call the field DEFAULT_WBS_SAP_CODE direct in the formula, it complains it can't find it " from T_EMPLOYEE_COMPANY_ASSIGNMENT TECA" + " where TECA.EMPLOYEE_ID = EMPLOYEE_ID)" + " and wbs.is_blocked_in_sap = 0" + " and wbs.is_deleted_in_sap = 0" + " order by wbs.sap_code DESC)" + " WHERE ROWNUM = 1"+ ")") public WorkBreakdownStructure getDefaultWbs() { return defaultWbs; } public void setDefaultWbs(WorkBreakdownStructure defaultWbs) { this.defaultWbs = defaultWbs; }现在我最初直接使用列DEFAULT_COST_CENTER_SAP_CODE和DEFAULT_WBS_SAP_CODE而没有内部选择，但是在这两列上都得到了错误无效的标识符，所以我想我会尝试使用ID代替引用，但是这也提出来了不合法的识别符。 这是hibernate生成的SQL代码：
2016-02-29 09：07：49,810 DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger.logStatement（SqlStatementLogger.java:104）[rw] - 选择employeeco0_.EMPLOYEE_ID为EMPLOYEE2_33_1_，employeeco0_.COMPANY_ID为COMPANY1_34_1_，employeeco0_。 EMPLOYEE_ID作为EMPLOYEE2_34_1_，employeeco0_.COMPANY_ID如COMPANY1_34_0_，employeeco0_.EMPLOYEE_ID如EMPLOYEE2_34_0_，employeeco0_.DEFAULT_COST_CENTER_SAP_CODE如DEFAULT7_34_0_，employeeco0_.DEFAULT_WBS_SAP_CODE如DEFAULT8_34_0_，（SELECT * FROM（SELECT * FROM T_COST_CENTER立方厘米其中cc.is_blocked_in_sap = 0和cc.is_deleted_in_sap = 0和cc.company_id在（SELECT TECA.COMPANY_ID FROM T_EMPLOYEE_COMPANY_ASSIGNMENT TECA WHERE TECA.EMPLOYEE_ID = employeeco0_.EMPLOYEE_ID）和LTRIM（（SELECT * FROM（从T_EMPLOYEE_COMPANY_ASSIGNMENT TECA WHERE TECA.EMPLOYEE_ID = employeeco0_.EMPLOYEE_ID中选择TECA.DEFAULT_COST_CENTER_SAP_CODE），其中ROWNUM = 1 ），'0'）= LTRIM（cc.sap_code，'0'）按cc.sap_code DESC排序）WHERE ROWNUM = 1）as formula2_0_，（SELECT * FROM（Sel） ect wbs。*来自T_WORK_BREAKDOWN_STRUCTURE wbs LEFT OUTER JOIN T_Company c on wbs.company_id = c.id其中wbs.sap_code in（从T_EMPLOYEE_COMPANY_ASSIGNMENT TECA中选择TECA.DEFAULT_WBS_SAP_CODE，其中TECA.EMPLOYEE_ID = employeeco0_.EMPLOYEE_ID）和wbs.is_blocked_in_sap = 0和wbs .is_deleted_in_sap = 0顺序wbs.sap_code DESC）WHERE ROWNUM = 1）as formula3_0_ from T_EMPLOYEE_COMPANY_ASSIGNMENT employeeco0_ where（employeeco0_.COMPANY_ID in（select t.id from T_COMPANY c where c.is_deleted_in_sap = 0））and employeeco0_.EMPLOYEE_ID =？ 按employeeco0_.COMPANY_ID排序
2016-02-29 09：07：49,811 TRACE org.hibernate.type.descriptor.sql.BasicBinder.bind（BasicBinder.java:83）[rw] - 绑定参数[1]为[BIGINT] - 28250
2016-02-29 09：07：49,811 TRACE org.hibernate.type.descriptor.sql.BasicBinder.bind（BasicBinder.java:83）[rw] - 绑定参数[1]为[BIGINT] - 28250
2016-02-29 09：07：49,811 TRACE org.hibernate.type.descriptor.sql.BasicBinder.bind（BasicBinder.java:83）[rw] - 绑定参数[1]为[BIGINT] - 28250
2016-02-29 09：07：49,817 WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions（SqlExceptionHelper.java:143）[rw] - SQL错误：904，SQLState：42000
2016-02-29 09：07：49,817 WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions（SqlExceptionHelper.java:143）[rw] - SQL错误：904，SQLState：42000
2016-02-29 09：07：49,817错误org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions（SqlExceptionHelper.java:144）[rw] - ORA-00904：“EMPLOYEECO0 _”。“EMPLOYEE_ID”：标识符无效
2016-02-29 09：07：49,817错误org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions（SqlExceptionHelper.java:144）[rw] - ORA-00904：“EMPLOYEECO0 _”。“EMPLOYEE_ID”：标识符无效
任何人都可以看到问题的来源吗？ 这对我来说似乎很奇怪......
这是在Oracle SQL环境中使用IntelliJ完成的。 没有公式，这个类工作正常
编辑
我已经更新了一个函数的公式，并且已经测试了一点，看看我能做些什么，我已经得到了以下工作：
@OneToOne @JoinColumnsOrFormulas({ @JoinColumnOrFormula(formula=@JoinFormula("(SELECT * FROM (" + "SELECT WBS.ID FROM T_WORK_BREAKDOWN_STRUCTURE WBS" + " LEFT OUTER JOIN T_COMPANY c" + " ON WBS.COMPANY_ID = C.ID" + " WHERE"+ " WBS.IS_BLOCKED_IN_SAP = 0" + " AND WBS.IS_DELETED_IN_SAP = 0" + " ORDER BY WBS.SAP_CODE DESC)" + "WHERE ROWNUM=1)")) }) public WorkBreakdownStructure getDefaultWbs() { return defaultWbs; }但是，一旦我将此行添加到where子句：
WBS.SAP_CODE IN (SELECT TECA.DEFAULT_WBS_SAP_CODE FROM T_EMPLOYEE_COMPANY_ASSIGNMENT TECA WHERE TECA.EMPLOYEE_ID = EMPLOYEE_ID)或这个：
WBS.SAP_CODE = DEFAULT_WBS_SAP_CODE它失败并带有无效的标识符（分别在EMPLOYEE_ID和DEFAULT_WBS_SAP_CODE上），但这些字段在它为其填充的属性生成的SQL代码中使用。直接测试数据库中生成的SQL。 生成的工作SQL代码如下所示：
select employeeco0_.COMPANY_ID as COMPANY1_34_, employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_, employeeco0_.DEFAULT_COST_CENTER_SAP_CODE as DEFAULT7_34_, employeeco0_.DEFAULT_WBS_SAP_CODE as DEFAULT8_34_, (SELECT * FROM (SELECT WBS.ID FROM T_WORK_BREAKDOWN_STRUCTURE WBS LEFT OUTER JOIN T_COMPANY c ON WBS.COMPANY_ID = C.ID WHERE WBS.IS_BLOCKED_IN_SAP = 0 AND WBS.IS_DELETED_IN_SAP = 0 ORDER BY WBS.SAP_CODE DESC) WHERE ROWNUM=1 ) as formula9_ from T_EMPLOYEE_COMPANY_ASSIGNMENT employeeco0_失败的那个看起来像这样：
select employeeco0_.COMPANY_ID as COMPANY1_34_, employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_, employeeco0_.DEFAULT_COST_CENTER_SAP_CODE as DEFAULT7_34_, employeeco0_.DEFAULT_WBS_SAP_CODE as DEFAULT8_34_, (SELECT * FROM (SELECT WBS.ID FROM T_WORK_BREAKDOWN_STRUCTURE WBS LEFT OUTER JOIN T_COMPANY c ON WBS.COMPANY_ID = C.ID WHERE WBS.SAP_CODE IN ( SELECT TECA.DEFAULT_WBS_SAP_CODE FROM T_EMPLOYEE_COMPANY_ASSIGNMENT TECA WHERE TECA.EMPLOYEE_ID = employeeco0_.EMPLOYEE_ID ) AND WBS.IS_BLOCKED_IN_SAP = 0 AND WBS.IS_DELETED_IN_SAP = 0 ORDER BY WBS.SAP_CODE DESC) WHERE ROWNUM=1) as formula9_ from T_EMPLOYEE_COMPANY_ASSIGNMENT employeeco0_或者它看起来像这样：
select employeeco0_.COMPANY_ID as COMPANY1_34_, employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_, employeeco0_.DEFAULT_COST_CENTER_SAP_CODE as DEFAULT7_34_, employeeco0_.DEFAULT_WBS_SAP_CODE as DEFAULT8_34_, (SELECT * FROM (SELECT WBS.ID FROM T_WORK_BREAKDOWN_STRUCTURE WBS LEFT OUTER JOIN T_COMPANY c ON WBS.COMPANY_ID = C.ID WHERE WBS.SAP_CODE = employeeco0_.DEFAULT_WBS_SAP_CODE AND WBS.IS_BLOCKED_IN_SAP = 0 AND WBS.IS_DELETED_IN_SAP = 0 ORDER BY WBS.SAP_CODE DESC) WHERE ROWNUM=1 ) as formula9_ from T_EMPLOYEE_COMPANY_ASSIGNMENT employeeco0_然而，当在数据库中完成时（当您使用有效ID替换employeeco0_.EMPLOYEE_ID或DEFAULT_WBS_SAP_CODE时）。 希望这个更新有所帮助
having a bit of fun with formulas and this is driving me crazy. We have a bit of code to get the entity via a formula, there is no link in the database and it needs to be done at runtime, hence the @Formula usage. Here is how the class looks:
@Entity @Table(name="T_EMPLOYEE_COMPANY_ASSIGNMENT" ) public class EmployeeCompanyAssignment implements java.io.Serializable { public EmployeeCompanyAssignment() { } @EmbeddedId @AttributeOverrides( { @AttributeOverride(name="employeeId", column=@Column(name="EMPLOYEE_ID", nullable=false, precision=10, scale=0) ), @AttributeOverride(name="companyId", column=@Column(name="COMPANY_ID", nullable=false, precision=10, scale=0) ) } ) public EmployeeCompanyAssignmentId getId() { return this.id; } @ManyToOne(fetch=FetchType.LAZY) @JoinColumn(name="EMPLOYEE_ID", nullable=false, insertable=false, updatable=false) public Employee getEmployee() { return this.employee; } public void setEmployee(Employee employee) { this.employee = employee; } @ManyToOne(fetch=FetchType.LAZY) @JoinColumn(name="COMPANY_ID", nullable=false, insertable=false, updatable=false) public Company getCompany() { return this.company; } public void setCompany(Company company){ this.company = company; } @Column(name="DEFAULT_WBS_SAP_CODE", length=24) public String getDefaultWbsSapCode() { return this.defaultWbsSapCode; } public void setDefaultWbsSapCode(String defaultWbsSapCode){ this.defaultWbsSapCode = defaultWbsSapCode; } @Column(name="DEFAULT_COST_CENTER_SAP_CODE", length=10) public String getDefaultCostCenterSapCode() { return this.defaultCostCenterSapCode; } public void setDefaultCostCenterSapCode(String defaultCostCenterSapCode){ this.defaultCostCenterSapCode = defaultCostCenterSapCode; } .... }And the formulas are set up this way:
@Formula(value = "(SELECT * FROM(" + "Select *" + " from T_COST_CENTER cc" + " where cc.is_blocked_in_sap = 0" + " and cc.is_deleted_in_sap = 0" + " and cc.company_id in" + " (SELECT TECA.COMPANY_ID" + " FROM T_EMPLOYEE_COMPANY_ASSIGNMENT TECA" + " WHERE TECA.EMPLOYEE_ID = EMPLOYEE_ID)" + " and LTRIM(" + "(SELECT *" + " FROM (select TECA.DEFAULT_COST_CENTER_SAP_CODE" + " from T_EMPLOYEE_COMPANY_ASSIGNMENT TECA" + " WHERE TECA.EMPLOYEE_ID = EMPLOYEE_ID)" + " where ROWNUM = 1)" + ", '0') = LTRIM(cc.sap_code, '0')" + " order by cc.sap_code DESC" + ")" + " WHERE ROWNUM = 1" + ")") public CostCenter getDefaultCostCenter() { return defaultCostCenter; } public void setDefaultCostCenter(CostCenter defaultCostCenter ) { this.defaultCostCenter = defaultCostCenter ; } @Formula(value="(" + "SELECT *" + " FROM (Select wbs.*" + " from T_WORK_BREAKDOWN_STRUCTURE wbs" + " LEFT OUTER JOIN T_Company c" + " on wbs.company_id = c.id" + " where wbs.sap_code in (select TECA.DEFAULT_WBS_SAP_CODE" + //We shouldn't have to do this this, but whenever I try to call the field DEFAULT_WBS_SAP_CODE direct in the formula, it complains it can't find it " from T_EMPLOYEE_COMPANY_ASSIGNMENT TECA" + " where TECA.EMPLOYEE_ID = EMPLOYEE_ID)" + " and wbs.is_blocked_in_sap = 0" + " and wbs.is_deleted_in_sap = 0" + " order by wbs.sap_code DESC)" + " WHERE ROWNUM = 1"+ ")") public WorkBreakdownStructure getDefaultWbs() { return defaultWbs; } public void setDefaultWbs(WorkBreakdownStructure defaultWbs) { this.defaultWbs = defaultWbs; }Now originally I was using the columns DEFAULT_COST_CENTER_SAP_CODE and DEFAULT_WBS_SAP_CODE directly without the internal select, but was getting the error invalid identifier on both of those columns, so I thought I'd try and use the IDs instead to reference, however that also comes up with invalid identifier. Here is the SQL code that hibernate produces:
2016-02-29 09:07:49,810 DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger.logStatement(SqlStatementLogger.java:104) [rw] - select employeeco0_.EMPLOYEE_ID as EMPLOYEE2_33_1_, employeeco0_.COMPANY_ID as COMPANY1_34_1_, employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_1_, employeeco0_.COMPANY_ID as COMPANY1_34_0_, employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_0_, employeeco0_.DEFAULT_COST_CENTER_SAP_CODE as DEFAULT7_34_0_, employeeco0_.DEFAULT_WBS_SAP_CODE as DEFAULT8_34_0_, (SELECT * FROM(Select * from T_COST_CENTER cc where cc.is_blocked_in_sap = 0 and cc.is_deleted_in_sap = 0 and cc.company_id in (SELECT TECA.COMPANY_ID FROM T_EMPLOYEE_COMPANY_ASSIGNMENT TECA WHERE TECA.EMPLOYEE_ID = employeeco0_.EMPLOYEE_ID) and LTRIM((SELECT * FROM (select TECA.DEFAULT_COST_CENTER_SAP_CODE from T_EMPLOYEE_COMPANY_ASSIGNMENT TECA WHERE TECA.EMPLOYEE_ID = employeeco0_.EMPLOYEE_ID) where ROWNUM = 1), '0') = LTRIM(cc.sap_code, '0') order by cc.sap_code DESC) WHERE ROWNUM = 1) as formula2_0_, (SELECT * FROM (Select wbs.* from T_WORK_BREAKDOWN_STRUCTURE wbs LEFT OUTER JOIN T_Company c on wbs.company_id = c.id where wbs.sap_code in (select TECA.DEFAULT_WBS_SAP_CODE from T_EMPLOYEE_COMPANY_ASSIGNMENT TECA where TECA.EMPLOYEE_ID = employeeco0_.EMPLOYEE_ID) and wbs.is_blocked_in_sap = 0 and wbs.is_deleted_in_sap = 0 order by wbs.sap_code DESC) WHERE ROWNUM = 1) as formula3_0_ from T_EMPLOYEE_COMPANY_ASSIGNMENT employeeco0_ where ( employeeco0_.COMPANY_ID in (select c.id from T_COMPANY c where c.is_deleted_in_sap =0)) and employeeco0_.EMPLOYEE_ID=? order by employeeco0_.COMPANY_ID
2016-02-29 09:07:49,811 TRACE org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:83) [rw] - binding parameter [1] as [BIGINT] - 28250
2016-02-29 09:07:49,811 TRACE org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:83) [rw] - binding parameter [1] as [BIGINT] - 28250
2016-02-29 09:07:49,811 TRACE org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:83) [rw] - binding parameter [1] as [BIGINT] - 28250
2016-02-29 09:07:49,817 WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:143) [rw] - SQL Error: 904, SQLState: 42000
2016-02-29 09:07:49,817 WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:143) [rw] - SQL Error: 904, SQLState: 42000
2016-02-29 09:07:49,817 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:144) [rw] - ORA-00904: "EMPLOYEECO0_"."EMPLOYEE_ID": invalid identifier
2016-02-29 09:07:49,817 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:144) [rw] - ORA-00904: "EMPLOYEECO0_"."EMPLOYEE_ID": invalid identifier
Can anyone see where the issue is coming from? It all seems rather peculiar to me...
This is being done on an Oracle SQL enviroment and using IntelliJ. Without the formulas, this class works fine
EDIT
I've updated the formula for one function and been testing a little more to see what I can do, I've got it so the following works:
@OneToOne @JoinColumnsOrFormulas({ @JoinColumnOrFormula(formula=@JoinFormula("(SELECT * FROM (" + "SELECT WBS.ID FROM T_WORK_BREAKDOWN_STRUCTURE WBS" + " LEFT OUTER JOIN T_COMPANY c" + " ON WBS.COMPANY_ID = C.ID" + " WHERE"+ " WBS.IS_BLOCKED_IN_SAP = 0" + " AND WBS.IS_DELETED_IN_SAP = 0" + " ORDER BY WBS.SAP_CODE DESC)" + "WHERE ROWNUM=1)")) }) public WorkBreakdownStructure getDefaultWbs() { return defaultWbs; }However, once I add this line to the where clause:
WBS.SAP_CODE IN (SELECT TECA.DEFAULT_WBS_SAP_CODE FROM T_EMPLOYEE_COMPANY_ASSIGNMENT TECA WHERE TECA.EMPLOYEE_ID = EMPLOYEE_ID)Or this:
WBS.SAP_CODE = DEFAULT_WBS_SAP_CODEIt fails with the invalid identifier (On the EMPLOYEE_ID and DEFAULT_WBS_SAP_CODE respectively), yet these fields are used in the SQL code that it generates for the properties that it fills in. Testing the SQL generated in the database directly works. The generated working SQL code looks like this:
select employeeco0_.COMPANY_ID as COMPANY1_34_, employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_, employeeco0_.DEFAULT_COST_CENTER_SAP_CODE as DEFAULT7_34_, employeeco0_.DEFAULT_WBS_SAP_CODE as DEFAULT8_34_, (SELECT * FROM (SELECT WBS.ID FROM T_WORK_BREAKDOWN_STRUCTURE WBS LEFT OUTER JOIN T_COMPANY c ON WBS.COMPANY_ID = C.ID WHERE WBS.IS_BLOCKED_IN_SAP = 0 AND WBS.IS_DELETED_IN_SAP = 0 ORDER BY WBS.SAP_CODE DESC) WHERE ROWNUM=1 ) as formula9_ from T_EMPLOYEE_COMPANY_ASSIGNMENT employeeco0_And the one that fails looks like this:
select employeeco0_.COMPANY_ID as COMPANY1_34_, employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_, employeeco0_.DEFAULT_COST_CENTER_SAP_CODE as DEFAULT7_34_, employeeco0_.DEFAULT_WBS_SAP_CODE as DEFAULT8_34_, (SELECT * FROM (SELECT WBS.ID FROM T_WORK_BREAKDOWN_STRUCTURE WBS LEFT OUTER JOIN T_COMPANY c ON WBS.COMPANY_ID = C.ID WHERE WBS.SAP_CODE IN ( SELECT TECA.DEFAULT_WBS_SAP_CODE FROM T_EMPLOYEE_COMPANY_ASSIGNMENT TECA WHERE TECA.EMPLOYEE_ID = employeeco0_.EMPLOYEE_ID ) AND WBS.IS_BLOCKED_IN_SAP = 0 AND WBS.IS_DELETED_IN_SAP = 0 ORDER BY WBS.SAP_CODE DESC) WHERE ROWNUM=1) as formula9_ from T_EMPLOYEE_COMPANY_ASSIGNMENT employeeco0_Or it looks like this:
select employeeco0_.COMPANY_ID as COMPANY1_34_, employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_, employeeco0_.DEFAULT_COST_CENTER_SAP_CODE as DEFAULT7_34_, employeeco0_.DEFAULT_WBS_SAP_CODE as DEFAULT8_34_, (SELECT * FROM (SELECT WBS.ID FROM T_WORK_BREAKDOWN_STRUCTURE WBS LEFT OUTER JOIN T_COMPANY c ON WBS.COMPANY_ID = C.ID WHERE WBS.SAP_CODE = employeeco0_.DEFAULT_WBS_SAP_CODE AND WBS.IS_BLOCKED_IN_SAP = 0 AND WBS.IS_DELETED_IN_SAP = 0 ORDER BY WBS.SAP_CODE DESC) WHERE ROWNUM=1 ) as formula9_ from T_EMPLOYEE_COMPANY_ASSIGNMENT employeeco0_Yet that works when done in the database (When you replace employeeco0_.EMPLOYEE_ID or DEFAULT_WBS_SAP_CODE with a valid ID). Hope this update helps a bit
最满意答案
在使用Formula和hibernate之前，我会尝试直接在数据库中运行查询，以了解查询的哪个部分是错误的。
看看你的@Formula，我只看到“select *”：你不能使用公式来返回完全托管的实体，就像使用@ManyToOne一样。
您必须将@Formula与String或Interger一起使用。 @Formula中的Select必须有一列
此外，公式是通过实体获取的，由于查询的复杂性，它可能会严重影响性能，并且在90％的时间内不会使用它。 也许在你的情况下，不使用Formula可能是值得的
The issue was being caused by the fact Oracle SQL can't reference a column declared in the outer query on the internal query. I have to rethink and redo the query so that I don't need to reference the field DEFAULT_WBS_SAP_CODE or EMPLOYEE_ID inside the internal query
更多推荐
DEFAULT_WBS_SAP_CODE,EMPLOYEE_ID,SQL,public,电脑培训,计算机培训,IT培训"/> <m
发布评论