Changing query to expert mode evolves more working to work without problems.
Let's see:
1- Query Wrapping Problem
Queries are wrapped when dynamic WHERE and ORDER BY are applied at run-time and may cause error or unexpected results.
Example of wrapped Query that cause error "ORA-00904: "SALARY": invalid identifier :
select * from (
select last_name , sum(salary) over ( partition by null) from employees
) qrslt where salary < 3000
Example of wrapped Query that cause unexpected result:
You want to display sum of salaries for each department individually, but you got sum of all departments:
select * from (
select department_id , last_name , salary , sum(salary) over ( partition by null) from employees
) qrslt where department_id = 10
Solution for Query Wrapping Problem
At the constructor of ViewObjectImpl class (Ex. "EmployeesImpl" class ) write:
super.setNestedSelectForFullSql(false);Now the part "select * from ( ) qrslt" is removed from the query and now:
changing department_id --> displays the sum of the salary for this specific department only .
2- Failed to load value at index ATTRIBUTE_ORDER
"Add attribute from entity" or changing in the sql expression not reflected in the SQL statement and cause:
JBO-27022: Failed to load value at index 7 with java object of type java.lang.String due to java.sql.SQLException.
Solution
If you want to "Add Attribute From Entity" and you are in expert mode you can follow three steps:
1-At "Attributes" tab of view object Click on "Add Attribute From Entity" and choose the target attribute you want to add.
2-At the "Query" tab, add that attribute to the select query statement ( manually or use the "Query Builder" ).
3- Click on "Update Mappings" button, which puts the changes on the xml ( optional if error exist).
If the attribute you want to add is calculated, follow steps 2,3 ,then you must sure that you can find the query changes at the xml source file of view object.
3- Wrong mapping problem
If the mapping of attributes is incorrect and you try to update attribute value and commit, error appear if the convert type is correct:
"Another user has changed the row with primary key", OR another error appear if conversion failed:
"Failed to load value at index 4 with java object of type java.lang.Long due to java.sql.SQLException.: Fail to convert to internal representation".
Solution
Make sure that attribute mappings is correct and the order of attributes is the same as the attributes order in the query and in future don't change the order of attributes manually and use the button "set source order".
At last:
In general you have to make sure that
1- attributes in the Attributes tab exists in the query and in the same order.
2-Attributes Mappings is correct.
3- XML have the same query that is in the Query tab.
http://jdeveloperfaq.blogspot.com/2010/02/faq-13-how-to-avoid-common-pitfalls.htmlQuery
