Tabular Forms on Complex Views - using INSTEAD OF Triggers
Creating tabular forms in Apex is relative straight forward. One gets fully generated, instant insert/update/delete functionality. Unfortunately (for the form developer), tables are usually part of a more or less complex data model, which is optimized for data storage, rather then for form-layout. Well, Apex offers some possibilities to the developer to enable tabular forms beyond 1:1 table based.
This collection-approach basically pre-fetches data into a (PL/SQL) collection from a query/cursor. The “collected” data will be displayed in the form. An on-submit page process will store the data submitted from the form into the collection. After this you can perform some validation before submitting the data to table(s) in an on-submit page process.
Views can be based on several tables or other views. Usually views are used to query data only, but in my case, I would like to perform all DML through an Apex form based on a view. As long as my view is “key-preserving” for the joined table I would like to perform the DML on, this is not a problem.
Key preserved means, the row from the base table will appear AT MOST ONCE in the output view on that table.
When my SQL becomes more complex, using outer joints, aggregations or sub-queries, the resulting view will probably not be updateable anymore without the use of INSTEAD OF Triggers. The INSTEAD OF trigger(s) will handle all the DML processing and hide the processing complexity from APEX.
Applying this view-Approach, you can keep the UI layer (APEX) separated from the data model layer (database).
Let`s go through this step-by-step:
I started creating a new workspace and used the EMP table as starting point. I added some table definitions to hold project-, assignment- and timesheet information.
You can download the complete DDL scripts for this example here.
Table DEMO_PROJECTS defines project with start- and end-date:
Table DEMO_TIMESCHEETS holds the time spend on an assignment:
Now lets join there tables:
The timesheet table does not contain any data yet. Even if there would be some timesheet data, there is no guaranty there are no gaps in the dates. I would like to have a result with dense dates for all project assignments:
Now we got a dense dataset, let's build the pivot statement. Well, I could use the 11g pivot function, but to make this statement more generic, I'll use the standard pre-11g way to pivot my timesheet data:
As shown in my lay-out example for the actual form I want to implement, the result of the query should show one row with all the days of a month containing the time entered for a certain project per user. Here it is:
OK, still no time data, but exactly the lay-out I was looking for.
Editing timesheet data will only effect the table DEMO_TIMESHEETS. For users there will be no difference between insert, update or delete as all fields in the pivot/matrix form will always be visible.
To handle a DML for a single row of DEMO_TIMESHEETS, I will provide a procedure which will:
I used the upper pivot SQL-statement to create the view DEMO_TIMESHEET_V. To be able to use this view as basis for my form, I have to make it updateable. The view DEMO_TIMESHEET_V is not inherently updateable. It requires an INSTEAD OF trigger. With an INSTEAD OF trigger, you can write normal UPDATE, INSERT, and DELETE
statements against the view, and the INSTEAD OF trigger works invisibly in the background to make the right actions take place. In this trigger I will call the procedure write_to_timesheet to perform the DML on the underlying table DEMO_TIMESHEETS. In fact, the procedure write_to_timesheet will be called a maximum of 31 times, depending on the month presented in the timesheet. Well, at this point I have to admit, this is not exactly the sexiest code I have ever written, but it does the job and it's pretty easy to understand (and I wonder, why I didn't choose an example with a weekly timesheet form).
Now I can issue updates against the view:
And the data will be written into the time sheet table (except the 31-Feb time, so you can't fool your manager):
So far I didn't have to code anything in Apex. In the next step I will build the actual form.
Assuming you already have created an application or you are using the demo application, the first step will be to create a new page:
Create Page:
The Create Tabular Form wizard appears:
Step "Table / View Owner":
Step "Table / View Name":
Step "Displayed Columns":
Step "Primary Key":
For those folks, who wondered why I created the year_month column: the reason is, I only can assign two columns as PK columns in the Apex wizard (I remember Oracle Portal had the same restriction. I know there was a workaround in Portal to add additional columns; does anybody know such a "backdoor" in Apex?). To overcome the limitation, you might have to combine two or more columns into one, depending on the complexity of your view.
Important: you won't get any error message while creating the form, but when submitting changes to the form without the correct key, Apex will throw an error:
Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20001: ORA-20001: Current version of data in database has changed since user initiated update process. <checksum information>, <your MRU statement>.
Step "Primary Key Source":
Step "Updateable Columns":
Step "Page and Region Attributes":
Step "Tab":
Step "Button Labels":
Step "Branching":
Step "Confirm":
The lay-out is not ideal yet. After changing the width of columns D1 ... D31 to "1" and, modifying the headers and hiding some of the ID columns my timesheet form look like this:
Now I will add two parameters (Year and Month) and modify the query, so only the assignments of the current Apex user will be shown. I'm not going to describe this in detail, but eventually I will use these parameters (select lists) in the source query of the form region:
With as result:
One last thing to do: prevent the columns of days 29/30/31 from being displayed when not valid for a month. I just place conditions on the Column Attributes of column D29 - D31:
PS: if you are getting a "ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table", read this post by Tobias Arnold.
Collections
One alternative is to create your tabular form using the Apex Collection API. There is a good article on Martin Giffy D'Souza’s blog describing this technique.This collection-approach basically pre-fetches data into a (PL/SQL) collection from a query/cursor. The “collected” data will be displayed in the form. An on-submit page process will store the data submitted from the form into the collection. After this you can perform some validation before submitting the data to table(s) in an on-submit page process.
Database Views
Well, I favor an approach that keeps the data-logic in the database, which is (in my opinion) more accessible to developers, more likely to be re-used and less fragmented (code-wise), thus easer to maintain: Database Views.Views can be based on several tables or other views. Usually views are used to query data only, but in my case, I would like to perform all DML through an Apex form based on a view. As long as my view is “key-preserving” for the joined table I would like to perform the DML on, this is not a problem.
Key preserved means, the row from the base table will appear AT MOST ONCE in the output view on that table.
When my SQL becomes more complex, using outer joints, aggregations or sub-queries, the resulting view will probably not be updateable anymore without the use of INSTEAD OF Triggers. The INSTEAD OF trigger(s) will handle all the DML processing and hide the processing complexity from APEX.
Applying this view-Approach, you can keep the UI layer (APEX) separated from the data model layer (database).
Let`s go through this step-by-step:
- create a view which will return all the data you would like to be available in your tabular form
- implement the DML processing using an INSTEAD OF trigger on this view
- build the tabular form UI in APEX based on this using standard APEX processing logic
Example
This example will implement a simple timesheet in APEX. The timesheet should show all projects of the (APEX-) user for a given month. The user must be able to enter "times spend" for every project assigned to him, for every day. The form should only contain one submit button (no add or remove rows button):Project Name | 1 | 2 | . . . | 30 | 31 |
project 1 | |||||
project 2 | |||||
… | |||||
project n |
You can download the complete DDL scripts for this example here.
Preparations
- You'll need a workspace associated to a database schema containing the Apex demo objects (DEMO_CUSTOMERS, DEMO_ORDERS, DEMO_STATES, DEMO_USERS, …)
- run the timesheet_demo.sql script to create the additional table objects (including some demo data)
Step 1: Create the Pivot View
Have a look at these tables and data I added to the demo application's model:Table DEMO_PROJECTS defines project with start- and end-date:
select * from demo_projects; PROJECT_ID PROJECT_NAME START_DAT END_DATE ---------- ------------------------------ --------- --------- 0 Marketing Campaign Computers 01-OKT-09 31-DEC-10 1 Marketing Campaign Software 01-JUL-09 31-JUL-10 2 rows selected.
Table DEMO_TIMESCHEETS holds the time spend on an assignment:
select * from demo_timesheets; no rows selected.
Now lets join there tables:
SELECT pj.project_id, pj.project_name, am.assignment_id, us.user_id, us.user_name, ts.timesheet_date, ts.hours FROM demo_projects pj, demo_assignments am, demo_users us, demo_timesheets ts WHERE pj.project_id = am.project_id AND am.user_id = us.user_id AND am.assignment_id = ts.assignment_id(+); PROJECT_ID PROJECT_NAME ASSIGNMENT_ID USER_ID USER_NAME TIMESHEET HOURS ---------- ------------------------------ ------------- ---------- ---------- --------- ---------- 0 Marketing Campaign Computers 3 22 SCOTT 1 Marketing Campaign Software 5 22 SCOTT 1 Marketing Campaign Software 6 23 FORD 3 rows selected.
The timesheet table does not contain any data yet. Even if there would be some timesheet data, there is no guaranty there are no gaps in the dates. I would like to have a result with dense dates for all project assignments:
SELECT prj.*, ts.hours FROM (SELECT d.thedate, pj.project_id, pj.project_name, am.assignment_id, us.user_id, us.user_name FROM ( SELECT mindat + LEVEL - 1 thedate FROM (SELECT MIN (start_date) mindat, MAX (end_date) maxdat FROM demo_projects) CONNECT BY LEVEL <= maxdat - mindat + 1) d, demo_projects pj, demo_assignments am, demo_users us WHERE pj.project_id = am.project_id AND am.user_id = us.user_id) prj, demo_timesheets ts WHERE prj.assignment_id = ts.assignment_id(+) AND prj.thedate = ts.timesheet_date(+); THEDATE PROJECT_ID PROJECT_NAME ASSIGNMENT_ID USER_ID USER_NAME HOURS --------- ---------- ------------------------------ ------------- ---------- ---------- ---------- 01-JUL-09 0 Marketing Campaign Computers 3 22 SCOTT 01-JUL-09 1 Marketing Campaign Software 6 23 FORD 01-JUL-09 1 Marketing Campaign Software 5 22 SCOTT 02-JUL-09 0 Marketing Campaign Computers 3 22 SCOTT 02-JUL-09 1 Marketing Campaign Software 6 23 FORD 02-JUL-09 1 Marketing Campaign Software 5 22 SCOTT 03-JUL-09 0 Marketing Campaign Computers 3 22 SCOTT 03-JUL-09 1 Marketing Campaign Software 6 23 FORD 03-JUL-09 1 Marketing Campaign Software 5 22 SCOTT 04-JUL-09 0 Marketing Campaign Computers 3 22 SCOTT . . . 28-DEC-10 1 Marketing Campaign Software 5 22 SCOTT 29-DEC-10 0 Marketing Campaign Computers 3 22 SCOTT 29-DEC-10 1 Marketing Campaign Software 5 22 SCOTT 29-DEC-10 1 Marketing Campaign Software 6 23 FORD 30-DEC-10 0 Marketing Campaign Computers 3 22 SCOTT 30-DEC-10 1 Marketing Campaign Software 5 22 SCOTT 30-DEC-10 1 Marketing Campaign Software 6 23 FORD 31-DEC-10 0 Marketing Campaign Computers 3 22 SCOTT 31-DEC-10 1 Marketing Campaign Software 5 22 SCOTT 31-DEC-10 1 Marketing Campaign Software 6 23 FORD 1647 rows selected.
Now we got a dense dataset, let's build the pivot statement. Well, I could use the 11g pivot function, but to make this statement more generic, I'll use the standard pre-11g way to pivot my timesheet data:
As shown in my lay-out example for the actual form I want to implement, the result of the query should show one row with all the days of a month containing the time entered for a certain project per user. Here it is:
SELECT theyear || '-' || themonth year_month, theyear, themonth, project_id, project_name, assignment_id, user_id, user_name, SUM (d1) d1, SUM (d2) d2, SUM (d3) d3, . . . SUM (d28) d28, SUM (d29) d29, SUM (d30) d30, SUM (d31) d31 FROM (SELECT EXTRACT (YEAR FROM thedate) theyear, EXTRACT (MONTH FROM thedate) themonth, project_id, project_name, assignment_id, user_id, user_name, CASE WHEN day_in_month = 1 THEN hours ELSE NULL END d1, CASE WHEN day_in_month = 2 THEN hours ELSE NULL END d2, CASE WHEN day_in_month = 3 THEN hours ELSE NULL END d3, . . . CASE WHEN day_in_month = 28 THEN hours ELSE NULL END d28, CASE WHEN day_in_month = 29 THEN hours ELSE NULL END d29, CASE WHEN day_in_month = 30 THEN hours ELSE NULL END d30, CASE WHEN day_in_month = 31 THEN hours ELSE NULL END d31 FROM (SELECT prj.*, ts.hours, EXTRACT (DAY FROM prj.thedate) day_in_month FROM (SELECT d.thedate, pj.project_id, pj.project_name, am.assignment_id, us.user_id, us.user_name FROM ( SELECT mindat + LEVEL - 1 thedate FROM (SELECT TRUNC(MIN(start_date)) mindat, TRUNC ( MAX (end_date) ) maxdat FROM demo_projects) CONNECT BY LEVEL <= maxdat - mindat + 1) d, demo_projects pj, demo_assignments am, demo_users us WHERE pj.project_id = am.project_id AND am.user_id = us.user_id) prj, demo_timesheets ts WHERE prj.assignment_id = ts.assignment_id(+) AND prj.thedate = ts.timesheet_date(+))) GROUP BY theyear, themonth, project_id, project_name, assignment_id, user_id, user_name; YEAR_MO THEYEAR THEMONTH PROJECT_ID PROJECT_NAME ASSIGNMENT_ID USER_ID USER_NAME D1 D2 D3 … D29 D30 D31 ------- ------- -------- ---------- ---------------------------- ------------- ------- --------- --- --- --- … --- --- --- 2009-10 2009 10 1 Marketing Campaign Software 6 23 FORD 2009-10 2009 10 0 Marketing Campaign Computers 3 22 SCOTT 2009-10 2009 10 1 Marketing Campaign Software 5 22 SCOTT 2009-11 2009 11 1 Marketing Campaign Software 6 23 FORD 2009-11 2009 11 0 Marketing Campaign Computers 3 22 SCOTT 2009-11 2009 11 1 Marketing Campaign Software 5 22 SCOTT 2009-12 2009 12 1 Marketing Campaign Software 6 23 FORD 2009-12 2009 12 0 Marketing Campaign Computers 3 22 SCOTT 2009-12 2009 12 1 Marketing Campaign Software 5 22 SCOTT 2009-7 2009 7 1 Marketing Campaign Software 6 23 FORD 2009-7 2009 7 0 Marketing Campaign Computers 3 22 SCOTT . . . 2010-8 2010 8 0 Marketing Campaign Computers 3 22 SCOTT 2010-8 2010 8 1 Marketing Campaign Software 5 22 SCOTT 2010-9 2010 9 1 Marketing Campaign Software 6 23 FORD 2010-9 2010 9 0 Marketing Campaign Computers 3 22 SCOTT 2010-9 2010 9 1 Marketing Campaign Software 5 22 SCOTT 54 rows selected.
OK, still no time data, but exactly the lay-out I was looking for.
Step 2: INSTEAD OF view trigger
Editing timesheet data will only effect the table DEMO_TIMESHEETS. For users there will be no difference between insert, update or delete as all fields in the pivot/matrix form will always be visible.
To handle a DML for a single row of DEMO_TIMESHEETS, I will provide a procedure which will:
- insert a row if the combination of assignment/date does not exist AND hours is not NULL/0
- update a row if combination of assignment/day does exist AND hours is not NULL/0
- delete a row if combination of assignment/day does exist AND hours is NULL/0
CREATE OR REPLACE PROCEDURE write_to_timesheet (p_asgnmt_id IN number, p_date IN date, p_hours IN number) AS BEGIN IF p_hours IS NOT NULL AND p_hours <> 0 THEN UPDATE demo_timesheets SET hours = p_hours WHERE assignment_id = p_asgnmt_id AND TRUNC (timesheet_date) = TRUNC (p_date); IF sql%ROWCOUNT = 0 THEN INSERT INTO demo_timesheets (assignment_id, timesheet_date, hours) VALUES (p_asgnmt_id, TRUNC (p_date), p_hours); END IF; ELSE DELETE FROM demo_timesheets WHERE assignment_id = p_asgnmt_id AND TRUNC (timesheet_date) = TRUNC (p_date); END IF; EXCEPTION WHEN OTHERS THEN raise_application_error (-20001, 'Error updating the timesheet!!!'); END; /
I used the upper pivot SQL-statement to create the view DEMO_TIMESHEET_V. To be able to use this view as basis for my form, I have to make it updateable. The view DEMO_TIMESHEET_V is not inherently updateable. It requires an INSTEAD OF trigger. With an INSTEAD OF trigger, you can write normal UPDATE, INSERT, and DELETE
statements against the view, and the INSTEAD OF trigger works invisibly in the background to make the right actions take place. In this trigger I will call the procedure write_to_timesheet to perform the DML on the underlying table DEMO_TIMESHEETS. In fact, the procedure write_to_timesheet will be called a maximum of 31 times, depending on the month presented in the timesheet. Well, at this point I have to admit, this is not exactly the sexiest code I have ever written, but it does the job and it's pretty easy to understand (and I wonder, why I didn't choose an example with a weekly timesheet form).
CREATE OR REPLACE TRIGGER demo_timesheet_v_io_trg INSTEAD OF UPDATE ON demo_timesheet_v REFERENCING NEW AS new OLD AS old FOR EACH ROW DECLARE v_first_day date; BEGIN v_first_day := TO_DATE ('01' || '-' || :new.themonth || '-' || :new.theyear, 'DD-MM-YYYY'); write_to_timesheet (:new.assignment_id, v_first_day + 0, :new.d1); write_to_timesheet (:new.assignment_id, v_first_day + 1, :new.d2); write_to_timesheet (:new.assignment_id, v_first_day + 2, :new.d3); . . . write_to_timesheet (:new.assignment_id, v_first_day + 25, :new.d26); write_to_timesheet (:new.assignment_id, v_first_day + 26, :new.d27); write_to_timesheet (:new.assignment_id, v_first_day + 27, :new.d28); -- special treatment for days not valid for some months IF EXTRACT (DAY FROM LAST_DAY (v_first_day)) >= 29 THEN write_to_timesheet (:new.assignment_id, v_first_day + 28, :new.d29); END IF; IF EXTRACT (DAY FROM LAST_DAY (v_first_day)) >= 30 THEN write_to_timesheet (:new.assignment_id, v_first_day + 29, :new.d30); END IF; IF EXTRACT (DAY FROM LAST_DAY (v_first_day)) >= 31 THEN write_to_timesheet (:new.assignment_id, v_first_day + 30, :new.d31); END IF; EXCEPTION WHEN OTHERS THEN -- Please, do some error handling and allow me -- to skip this part for this time... RAISE; END demo_timesheet_v_io_trg; /
Now I can issue updates against the view:
UPDATE demo_timesheet_v SET d1 = 4 , d10 = 5 , d28 = 6 , d31 = 7 WHERE year_month = '2010-2' AND assignment_id = 5 ;
And the data will be written into the time sheet table (except the 31-Feb time, so you can't fool your manager):
ASSIGNMENT_ID TIMESHEET HOURS ------------- --------- ---------- 5 01-FEB-10 4 5 10-FEB-10 5 5 28-FEB-10 6 3 rows selected.
So far I didn't have to code anything in Apex. In the next step I will build the actual form.
Step 3: build the tabular form in APEX
I promised, that the timesheet form will be completely based on standard Apex functionality (strictly spoken, using the collection approach is standard Apex functionality too), without any additional coding in Apex. Although it's mostly just clicking through the wizards and property screens, I will walk through it step by step:Assuming you already have created an application or you are using the demo application, the first step will be to create a new page:
Create Page:
- page type: Form
- now choose: Tabular Form
The Create Tabular Form wizard appears:
Step "Table / View Owner":
- select the schema owning the demo objects
- For "Allowed Operations" choose "Update Only". Remember, the pivot view contains all the rows. There is no need to insert or delete rows.
Step "Table / View Name":
- select the view DEMO_TIMESHEET_V from the list
Step "Displayed Columns":
- just select all the columns
Step "Primary Key":
- Each row of the view can be uniquely identified by the column values of year_month and assignment_id. Select these from the lists.
For those folks, who wondered why I created the year_month column: the reason is, I only can assign two columns as PK columns in the Apex wizard (I remember Oracle Portal had the same restriction. I know there was a workaround in Portal to add additional columns; does anybody know such a "backdoor" in Apex?). To overcome the limitation, you might have to combine two or more columns into one, depending on the complexity of your view.
Important: you won't get any error message while creating the form, but when submitting changes to the form without the correct key, Apex will throw an error:
Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20001: ORA-20001: Current version of data in database has changed since user initiated update process. <checksum information>, <your MRU statement>.
Step "Primary Key Source":
- Choose "Existing Trigger" for both primary key columns.
Step "Updateable Columns":
- Updateable columns will be D1 . . . D31.
Step "Page and Region Attributes":
- Give the page an appropriate name (like "Timesheet") and accept the defaults for the rest of the properties.
Step "Tab":
- just accept the default
Step "Button Labels":
- just accept the default
Step "Branching":
- just accept the default
Step "Confirm":
- Click "Finish" and run the page.
The lay-out is not ideal yet. After changing the width of columns D1 ... D31 to "1" and, modifying the headers and hiding some of the ID columns my timesheet form look like this:
Now I will add two parameters (Year and Month) and modify the query, so only the assignments of the current Apex user will be shown. I'm not going to describe this in detail, but eventually I will use these parameters (select lists) in the source query of the form region:
With as result:
One last thing to do: prevent the columns of days 29/30/31 from being displayed when not valid for a month. I just place conditions on the Column Attributes of column D29 - D31:
Conclusion
What I wanted to show is: when developing with Apex, keep in mind that you are running Apex on a very powerful environment: the Oracle database. Preferably use Apex to build your user interface and standard transaction form handling. Complex data logic should preferably be coded in the database, not in the Apex Page processing. No, it's not black & white, eventually you have to find a balance between choosing the database or the Apex approach in individual cases.PS: if you are getting a "ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table", read this post by Tobias Arnold.
Hi,
ReplyDeleteThis doesn't seem to work for me in APEX 4.1. Unlike the example you mentioned where there is one foreign key column in timesheets table I have many foreign keys. so my page will have searchable LOV for other foreign keys and my query looks something like this.
select PK_ID
,FK1
,NVL(FK2,:P2_FK2)
,NVL(FK3,:P2_FK3)
FROM VIEW_NAME
Tables xyz(PK_ID NUMBER,
FK1 NUMBER,--references to a parent table
FK2 NUMBER, --References parent table
FK3 NUMBER
).
I am still getting the error you mentioned. Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20001: ORA-20001: Current version of data in database has changed since user initiated update process
Appreciate your help.
Regards,
Abdul
Abdul,
ReplyDeleteFirst I would like to mention that the tabular-form mechanism in APEX has been changed with version 4.0. With the introduction of ROWID based (also you might not want to use it with views) tabular-forms the described approach only will work if the view (you define the instead-of trigger on) will have a key-preserved table joined.
So, if you have such a view (with key-preserved table) that enables APEX to uniquely identify a row, you still should be able to use this approach.
Regards,
Christian
Hello Christian Rokitta.
ReplyDeleteThank you very much for your helpful article.
Unfortunately i had not success applying your idea in my case.
I use apex 4.1. And last step I finished whith success is updateing the view using sql. But after running tabular form, builded in apex's master I get following error message: "failed to parse SQL query: ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc."
Some notes about my view: it uses subquery, each row of the view can be uniquely identified by column value "id" (so in form creating master on step primary key i have checked primary key and chosen this column "id").
Please, help me find the reason of this error.
Kseniya,
DeleteCan I have a look at the view's complete SQL?
Cheers,
Christian
Oh, thanks a lot for such quickly reacting.
DeleteHere it is:
CREATE OR REPLACE FORCE VIEW "SQ_VIEW" ("ID", "TEXT", "CNT1", "CNT2", "CNT3", "SUM_ITEMS_CNT") AS
select t.id as id,
n.text as text,
t.cnt1 as cnt1,
t.cnt2 as cnt2,
t.cnt3 as cnt3,
sum(ti.cnt) as sum_items_cnt
from test t, name n, test_items ti
where t.s_name = n.idz (+)
and ti.s_test (+) = t.id
group by t.id, n.text, t.cnt1, t.cnt2, t.cnt3
and here is INSTEAD OF trigger for view:
create or replace TRIGGER update_test_on_sq_test_view
INSTEAD OF UPDATE
ON sq_view
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
DECLARE
v_first_day date;
BEGIN
update test
set cnt3 = :new.cnt3
where id = :new.id;
END update_test_on_sq_test_view;
Regards,
Kseniya
Kseniya,
DeleteAs the error message states: the GROUP BY may be the problem here. Please try the following rewrite of the query:
CREATE OR REPLACE FORCE VIEW "SQ_VIEW"
(
"ID",
"TEXT",
"CNT1",
"CNT2",
"CNT3",
"SUM_ITEMS_CNT"
)
AS
SELECT t.id AS id,
n.text AS text,
t.cnt1 AS cnt1,
t.cnt2 AS cnt2,
t.cnt3 AS cnt3,
(SELECT SUM (ti.cnt)
FROM test_items ti
WHERE ti.s_test = t.id)
AS sum_items_cnt
FROM test t, name n
WHERE t.s_name = n.idz(+);
I replaced the group by by an inline query. Without having tested this, I pretty sure it comes up with the same result and should work with APEX now. Just don´t forget the INSTEAD OF trigger when re-creating the view ;-).
Let me know if this works.
Cheers,
Christian
Christian,
DeleteThank you for advice. But if table "test_items" has several rows whith same "s_test", the view with such select statement will contain dublicate rows. And view's column "id" will not be able to uniquely identify row.
Regards,
Kseniya
Ksenia,
DeleteThe rows in the inline select will be aggregated by the SUM function and at most one row will be returned. If more than one row would be returned, the SQL will come up with an error.
So, did you test te statement?
There might be a problem, if table NAME contains more than one row for a row in TEST.
Regards,
Christian
Hi,
Deleteim getting the same problem in apex, do you know of any way to get around it with this query:
WITH APP AS (
SELECT STUDENTID
FROM APPLICATION
WHERE APPLICATIONSTATUSID = 7
GROUP BY STUDENTID
)
select STUDENTS.ROWID,
STUDENTS.STUDENTID,
FORENAME,
SURNAME,
DOB,
TERMADDRESS,
TERMPOSTCODE,
HOMEADDRESS,
HOMEPOSTCODE,
CONTACT,
EMAIL,
PLACEMENTPREFS,
CVAPPROVED,
CVAPPROVALDATE,
PROGRAMME.PROGRAMMENAME
from STUDENTS
JOIN APP
ON STUDENTS.STUDENTID = APP.STUDENTID
JOIN PROGRAMME
ON PROGRAMME.PROGRAMMEID = STUDENTS.PROGRAMMEID
thanks.
Hi,
DeleteMaybe rewriting the query, removing the joins, helps. This rewrite only works if the STUDENTS / PROGRAMME relation has one programme record for each student:
WITH app
AS (SELECT studentid
FROM application
WHERE applicationstatusid = 7
GROUP BY studentid)
SELECT students.ROWID
, students.studentid
, forename
, surname
, dob
, termaddress
, termpostcode
, homeaddress
, homepostcode
, contact
, email
, placementprefs
, cvapproved
, cvapprovaldate
, (SELECT programme.programmename
FROM programme
WHERE programme.programmeid = students.programmeid)
programmename
FROM students
WHERE EXISTS
(SELECT 1
FROM app
WHERE app.studentid = students.studentid)
Christian, I'm sory very much, my previous post was a result of my mistake. I have done all over again with your version of select for view and it works!!! Heartly thanks for your time and advice!
ReplyDeleteBest wishes,
Kseniya.
hi christian,
ReplyDeleteyour ddl scripts and timesheet_demo.sql are not more available.
best regards
jean marc
Hi Jean Marc,
DeleteSorry about that. I fixed the links. Note: the above example was developed on APEX 3.2. If you are installing the script with APEX 4.0 or newer, you need a key-preserving view to make this work.
Regards,
Christian
Hi Christan..
ReplyDeleteI am using apex 4.1. I followed the steps according to your instructions but still i am getting the error "failed to parse SQL query:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
".
My view and instead of trigger as as following
CREATE OR REPLACE FORCE VIEW VU_TEST ("ID", agtid,"RMCID", "NO_OF_ROOMS", "TOT_ROOMS", "START_DATE", "END_DATE") AS
select ID||ordinal||agtid ID,
agtid ,
rmcid ,
no_of_rooms ,
no_of_rooms tot_rooms ,
startd start_date,
endd end_Date
from table1 t, Materialized_View v
where t.rmcid=v.catid
the ID column uniquely identifies each row.
CREATE OR REPLACE TRIGGER vu_test_trg
INSTEAD OF UPDATE
ON VU_TEST
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
update RMCPA set no_of_rooms=:new.no_of_rooms where trim(id)=trim(:old.id);
EXCEPTION
WHEN OTHERS THEN
raise_application_error (-20001, sqlerrm);
END vu_RMCPA_trg;
in continuation with my previous post the table in trigger to be updated is basically "table1" used in view.
ReplyDeleteRegards
Saj
Hi Saj,
ReplyDeleteAPEX (4+) is either expecting a ROWID (which you obviously do not have using a view) or a key column that originates from ONE of the joined tables in order to be able to associate each row of your view with the ONE key column of that table.
In your case you are using an ID column that is a) calculated and b) constructed from columns from different tables, I assume. You didn’t use aliases in the view statement which makes it difficult to read and understand entirely.
Cheers,
Christian