Home » Developer & Programmer » Forms » View with MAX( DECODE( returns no rows, 9iDS
View with MAX( DECODE( returns no rows, 9iDS [message #83976] Mon, 12 January 2004 11:21 Go to next message
Douglas Hersh
Messages: 3
Registered: January 2004
Junior Member
The code below creates table: salg_pivot and view: salg_xtab derived from scott.salgrade, to demonstrate a problem I have in Forms.

The view can be queried in SQL*Plus, but produces an ORA-01403: no data found when I attempt to query a wizard-based data block created from this view in Forms. Interestingly, the Query..Count Hits menu item return the correct number of rows. The form is just unable to display them.

When attempting to query records in the form I get a FRM-40505 Oracle error: Unable to perform query. Pressing Ctl+Shift+E I see that Forms is trying to execute:

   SELECT GRADE,LOSAL,HISAL FROM SALG_XTAB

and returns ORA-0143: No data found.

I suspect that there is some data block property I have not set properly. The defaults clearly do not work, nor does setting the block database properties (insert allowed, update allowed, delete allowed) to NO.

It appears to be the DECODE that is causing the problem, because a view using MAX without the DECODE works just fine.

I have confirmed that this is NOT a problem when using Forms 6.0.

Is this a bug, or are my form properties incorrect?

I am using: Forms [[32 Bit]] Version 9.0.2.7.0 (Production)
Oracle9i Enterprise Edition Release 9.2.0.4.0
cannot query read-only view

/* Pivot low and high salaries to separate rows */
CREATE TABLE salg_pivot AS
SELECT grade,
       'LOW' AS range,
       losal AS salary
  FROM scott.salgrade
 UNION ALL
SELECT grade,
       'HIGH' AS range,
       hisal AS salary
  FROM scott.salgrade;

/* Cross-tabulation of salg_pivot to reproduce salgrade table */
DROP view salg_xtab;
CREATE VIEW salg_xtab AS
SELECT grade,
       MAX(DECODE(range, 'LOW', salary, NULL)) 
         AS losal,
       MAX(DECODE(range, 'HIGH', salary, NULL))
          AS hisal
  FROM salg_pivot
 GROUP BY grade;
Re: View with MAX( DECODE( returns no rows, 9iDS [message #84032 is a reply to message #83976] Tue, 20 January 2004 13:54 Go to previous messageGo to next message
Douglas Hersh
Messages: 3
Registered: January 2004
Junior Member
A workaround to this problem comes from Gregor in Slovenia via the Ask Tom website.

According to Gregor:

"There is a problem with forms when using MAX function
that returns NULL. The only workaround I was able to
find was not to return NULL value with MAX function.

For example :
max( decode( hour, 0, flag, '0' ) ) "0", ...

This should solve your problems in form. "

I have tested this in Forms Version 9.0.2.7.0 against the
Oracle9i Enterprise Edition Release 9.2.0.4.0 and confirm that this works.

This solution is not satisfactory if the intent is to return a NULL for cases where there is no match on the decoded column. I have not found a bug report for this although it must be a very common problem.
Re: View with MAX( DECODE( returns no rows, 9iDS [message #84053 is a reply to message #83976] Thu, 22 January 2004 07:00 Go to previous message
Douglas Hersh
Messages: 3
Registered: January 2004
Junior Member
My DBA inquired about this problem with Oracle Support. According to Oracle Support, the problem with using the MAX( DECODE(... construction is a Forms limitation. They say that it is not unusual for complicated views or SQL statements to cause problems in Forms. They say further that Forms triggers are the standard practice to accommodate the discrepancies between Forms and full SQL*Plus functionality. In this case, Oracle support recommends trying "When-validate trigger".

Since I am very new to Oracle Forms I do not yet know what such a when-validate trigger would entail. I imagine it involves trapping and then ignoring the "ORA-01403 no data found" error.

That being the case, I have found a satisfactory server-side-only work-around, which I show below. In my example I create a test table t and then create 3 views derived from this table. The third view is the charm and demonstrates how to get around the Forms 9i limitation without using a when-validate trigger.

SQL> CREATE TABLE t (
2 id NUMBER,
3 property VARCHAR2(2),
4 val NUMBER
5 );

Table created.

SQL>
SQL> DESCRIBE t
Name Null? Type
---- -------- -----------
ID NUMBER
PROPERTY VARCHAR2(2)
VAL NUMBER

SQL>
SQL> INSERT INTO t VALUES ( 1, 'A', 10 );

1 row created.

SQL> INSERT INTO t VALUES ( 1, 'B', 11 );

1 row created.

SQL> INSERT INTO t VALUES ( 2, 'A', 12 );

1 row created.

SQL> INSERT INTO t VALUES ( 2, 'B', 13 );

1 row created.

SQL> INSERT INTO t VALUES ( 3, 'A', 14 );

1 row created.

SQL>
SQL> SELECT * FROM t;

ID PR VAL
-- -- ---------
1 A 10
1 B 11
2 A 12
2 B 13
3 A 14

SQL>
SQL> /*
DOC>|| This view produces an ORA-01403 no data found error
DOC>|| in Oracle Forms 9i. The property fields are pivoted
DOC>|| into separate columns.
DOC>*/
SQL> CREATE OR REPLACE VIEW t_v1
2 AS
3 SELECT id,
4 MAX( DECODE( property, 'A', val, NULL ) ) AS a_val,
5 MAX( DECODE( property, 'B', val, NULL ) ) AS b_val
6 FROM t
7 GROUP BY id;

View created.

SQL>
SQL> SELECT * FROM t_v1;

ID A_VAL B_VAL
---------- ---------- ----------
1 10 11
2 12 13
3 14

SQL>
SQL> /*
DOC>|| This view eliminates the ORA-01403 error in Forms,
DOC>|| but the -999 placeholder value is an undesirable side-effect.
DOC>|| Any valid non-null number can be used to replace
DOC>|| the NULL in the DECODE statement. It is the use of NULL
DOC>|| that causes the Forms error. The placeholder number must be
DOC>|| some value that would never occur in the data set.
DOC>*/
SQL> CREATE OR REPLACE VIEW t_v2
2 AS
3 SELECT id,
4 MAX( DECODE( property, 'A', val, -999 ) ) AS a_val,
5 MAX( DECODE( property, 'B', val, -999 ) ) AS b_val
6 FROM t
7 GROUP BY id;

View created.

SQL> SELECT * FROM t_v2;

ID A_VAL B_VAL
---------- ---------- ----------
1 10 11
2 12 13
3 14 -999

SQL>
SQL> /*
DOC>|| This view does NOT cause the ORA-01403 error in Forms.
DOC>|| It uses DECODE to replace the -999 placeholder value
DOC>|| in the inline view with NULL.
DOC>||
DOC>|| The use of TO_NUMBER is required to guarantee
DOC>|| that the datatype of the pivoted columns is numeric in the
DOC>|| view.
DOC>||
DOC>|| The results are equivalent to t_v1, but without the Forms
DOC>|| error.
DOC>*/
SQL> CREATE OR REPLACE VIEW t_v3
2 AS
3 SELECT id,
4 TO_NUMBER( DECODE( a_val, -999, NULL, a_val ) ) AS a_val,
5 TO_NUMBER( DECODE( b_val, -999, NULL, b_val ) ) AS b_val
6 FROM ( SELECT id,
7 MAX( DECODE( property, 'A', val, -999 ) ) AS a_val,
8 MAX( DECODE( property, 'B', val, -999 ) ) AS b_val
9 FROM t
10 GROUP BY id );

View created.

SQL> SELECT * FROM t_v3;

ID A_VAL B_VAL
---------- ---------- ----------
1 10 11
2 12 13
3 14
Previous Topic: How we can use animated GIF in runtime form6i
Next Topic: trigger to call sequence on his primary key column
Goto Forum:
  


Current Time: Thu Mar 28 23:51:58 CDT 2024