| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-25-11, 14:07
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
|
Try to simulate MQT with REFRESH IMMEDIATE and OUTER JOIN
|
|
I tried to simulate MQT with REFRESH IMMEDIATE and OUTER JOIN.
It failed to create MQT with REFRESH IMMEDIATE as Example 1).
Because the select includes OUTER JOIN.
So, I tried Example 2) which made same result as Example 1),
if a row with deptno = 'A00' in (inner)table department was exists.
But, MQT with REFRESH IMMEDIATE as Example 2) also failed with message...
SQL20058N The fullselect specified for the materialized query table
"DB2ADMIN.EMP_MANAGE_DEPT" is not valid. Reason code = "7". SQLSTATE=428EC
After many trial and error,
I think that I succeeded in the attempt on the assumption that a row with deptno = 'A00' in (inner)table department was guaranteed.
I will post the attempt in the next post in this thread.
Example 1)
Code:
------------------------------ Commands Entered ------------------------------
SELECT e.empno
, e.firstnme || RTRIM(' ' || e.midinit)|| ' ' || e.lastname AS fullname
, e.workdept
, d.deptno
, d.admrdept
, d.deptname
FROM employee e
LEFT OUTER JOIN
department d
ON d.mgrno = e.empno
;
------------------------------------------------------------------------------
EMPNO FULLNAME WORKDEPT DEPTNO ADMRDEPT DEPTNAME
------ ------------------------------ -------- ------ -------- ------------------------------------
000010 CHRISTINE I HAAS A00 A00 A00 SPIFFY COMPUTER SERVICE DIV.
000020 MICHAEL L THOMPSON B01 B01 A00 PLANNING
000030 SALLY A KWAN C01 C01 A00 INFORMATION CENTER
000050 JOHN B GEYER E01 E01 A00 SUPPORT SERVICES
000060 IRVING F STERN D11 D11 D01 MANUFACTURING SYSTEMS
000070 EVA D PULASKI D21 D21 D01 ADMINISTRATION SYSTEMS
000090 EILEEN W HENDERSON E11 E11 E01 OPERATIONS
000100 THEODORE Q SPENSER E21 E21 E01 SOFTWARE SUPPORT
000110 VINCENZO G LUCCHESSI A00 - - -
000120 SEAN O'CONNELL A00 - - -
.....
.....
000330 WING LEE E21 - - -
000340 JASON R GOUNOT E21 - - -
32 record(s) selected.
Example 2)
Code:
SELECT e.empno
, e.firstnme || RTRIM(' ' || e.midinit)|| ' ' || e.lastname AS fullname
, e.workdept
, CASE d.mgrno
WHEN e.empno THEN
d.deptno
END AS deptno
, CASE d.mgrno
WHEN e.empno THEN
d.admrdept
END AS admrdept
, CASE d.mgrno
WHEN e.empno THEN
d.deptname
END AS deptname
FROM employee e
, department d
WHERE d.mgrno = e.empno
OR d.deptno = 'A00'
AND NOT EXISTS
(SELECT 0
FROM department de
WHERE de.mgrno = e.empno
)
;
|
Last edited by tonkuma; 01-25-11 at 15:28.
|
01-25-11, 14:59
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
The steps to make the MQT(and a view).
Step 1) Create MQT emp_manage_dept of which results include encoded deptno, admrdept and deptname.
Step 2) Issue Set integrity.
Step 3) Create function nbr_to_char which returns original character string from encoded number.
Step 4) Create view emp_manage_dept which makes same result of Example 1) in original post
by decoding deptno, admrdept and deptname using expressions and function nbr_to_char.
Example 3) Query the view.
Step 1) Create MQT.
Code:
------------------------------ Commands Entered ------------------------------
CREATE TABLE emp_manage_dept
AS (
SELECT e.empno
, e.firstnme || RTRIM(' ' || e.midinit)|| ' ' || e.lastname AS fullname
, e.workdept
, COUNT(*) AS count_row
, SUM (
CASE d.mgrno
WHEN e.empno THEN
SMALLINT( TRANSLATE(d.deptno , '123456789' , 'ABCDEFGHI') )
END
) AS deptno_sum
, COUNT(
CASE d.mgrno
WHEN e.empno THEN
SMALLINT( TRANSLATE(d.deptno , '123456789' , 'ABCDEFGHI') )
END
) AS deptno_cnt
, SUM (
CASE d.mgrno
WHEN e.empno THEN
SMALLINT( TRANSLATE(d.admrdept , '123456789' , 'ABCDEFGHI') )
END
) AS admrdept_sum
, COUNT(
CASE d.mgrno
WHEN e.empno THEN
SMALLINT( TRANSLATE(d.admrdept , '123456789' , 'ABCDEFGHI') )
END
) AS admrdept_cnt
, SUM (
CASE d.mgrno
WHEN e.empno THEN
CAST( TRANSLATE( 'apbqcrdsetfugvhwixjykzl0m1n2o3'
, TRANSLATE( SUBSTR(d.deptname , 1 , 15)
, '000000000111111111122222222223333333333444'
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
)
||
TRANSLATE( SUBSTR(d.deptname , 1 , 15)
, '123456789012345678901234567890123456789012'
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
)
, 'abcdefghijklmnopqrstuvwxyz0123'
)
AS DEC(30 , 0)
)
END
) AS deptname_1_15_sum
, COUNT(
CASE d.mgrno
WHEN e.empno THEN
CAST( TRANSLATE( 'apbqcrdsetfugvhwixjykzl0m1n2o3'
, TRANSLATE( SUBSTR(d.deptname , 1 , 15)
, '000000000111111111122222222223333333333444'
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
)
||
TRANSLATE( SUBSTR(d.deptname , 1 , 15)
, '123456789012345678901234567890123456789012'
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
)
, 'abcdefghijklmnopqrstuvwxyz0123'
)
AS DEC(30 , 0)
)
END
) AS deptname_1_15_cnt
, SUM (
CASE d.mgrno
WHEN e.empno THEN
CAST( TRANSLATE( 'apbqcrdsetfugvhwixjykzl0m1n2o3'
, TRANSLATE( SUBSTR(d.deptname , 16 , 15)
, '000000000111111111122222222223333333333444'
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
)
||
TRANSLATE( SUBSTR(d.deptname , 16 , 15)
, '123456789012345678901234567890123456789012'
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
)
, 'abcdefghijklmnopqrstuvwxyz0123'
)
AS DEC(30 , 0)
)
END
) AS deptname_16_30_sum
, COUNT(
CASE d.mgrno
WHEN e.empno THEN
CAST( TRANSLATE( 'apbqcrdsetfugvhwixjykzl0m1n2o3'
, TRANSLATE( SUBSTR(d.deptname , 16 , 15)
, '000000000111111111122222222223333333333444'
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
)
||
TRANSLATE( SUBSTR(d.deptname , 16 , 15)
, '123456789012345678901234567890123456789012'
, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
)
, 'abcdefghijklmnopqrstuvwxyz0123'
)
AS DEC(30 , 0)
)
END
) AS deptname_16_30_cnt
FROM employee e
, department d
WHERE d.mgrno = e.empno
OR d.deptno = 'A00'
GROUP BY
e.empno
, e.firstnme || RTRIM(' ' || e.midinit)|| ' ' || e.lastname
, e.workdept
)
DATA INITIALLY DEFERRED REFRESH IMMEDIATE
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
Step 2) Issue SET INTEGRITY.
Code:
------------------------------ Commands Entered ------------------------------
SET INTEGRITY FOR emp_manage_dept IMMEDIATE CHECKED;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
Step 3) Create a function.
Code:
------------------------------ Commands Entered ------------------------------
CREATE OR REPLACE
FUNCTION nbr_to_char( in_nbr DEC(30 , 0) )
RETURNS VARCHAR(15)
LANGUAGE SQL
READS SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
SELECT XMLCAST(
XMLGROUP(
SUBSTR( '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ''., &@'
, SUBSTR( DIGITS(in_nbr) , k , 2 )
, 1 ) AS d
ORDER BY k )
AS VARCHAR(15)
) AS result_str
FROM (VALUES 1,3,5,7,9,11,13,15,17,19,21,23,25,27,29) k(k)
WHERE 29 - LOG10(in_nbr) <= k
GROUP BY in_nbr
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
Step 4) Create a view
Code:
------------------------------ Commands Entered ------------------------------
CREATE OR REPLACE
VIEW emp_manage_dept_view AS
SELECT empno
, fullname
, workdept
, SUBSTR('ABCDEFGHI' , deptno_sum / 100 , 1)
||
SUBSTR( DIGITS(deptno_sum) , 9 , 2 ) AS deptno
, SUBSTR('ABCDEFGHI' , admrdept_sum / 100 , 1)
||
SUBSTR( DIGITS(admrdept_sum) , 9 , 2 ) AS admrdept
, nbr_to_char(deptname_1_15_sum)
||
nbr_to_char(deptname_16_30_sum) AS deptname
FROM emp_manage_dept
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
Example 3) Qery the view.
Code:
------------------------------ Commands Entered ------------------------------
SELECT *
FROM emp_manage_dept_view
;
------------------------------------------------------------------------------
EMPNO FULLNAME WORKDEPT DEPTNO ADMRDEPT DEPTNAME
------ ------------------------------ -------- ------ -------- ------------------------------
000010 CHRISTINE I HAAS A00 A00 A00 SPIFFY COMPUTER SERVICE DIV.
000020 MICHAEL L THOMPSON B01 B01 A00 PLANNING
000030 SALLY A KWAN C01 C01 A00 INFORMATION CENTER
000050 JOHN B GEYER E01 E01 A00 SUPPORT SERVICES
000060 IRVING F STERN D11 D11 D01 MANUFACTURING SYSTEMS
000070 EVA D PULASKI D21 D21 D01 ADMINISTRATION SYSTEMS
000090 EILEEN W HENDERSON E11 E11 E01 OPERATIONS
000100 THEODORE Q SPENSER E21 E21 E01 SOFTWARE SUPPORT
000110 VINCENZO G LUCCHESSI A00 - - -
000120 SEAN O'CONNELL A00 - - -
.....
.....
000330 WING LEE E21 - - -
000340 JASON R GOUNOT E21 - - -
32 record(s) selected.
|
Last edited by tonkuma; 01-25-11 at 15:38.
|
01-25-11, 15:40
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
|
|
Example 4) Update a base table.
Example 5) MQT was updated immediately.
Example 4) Update table department.
Code:
------------------------------ Commands Entered ------------------------------
UPDATE department
SET (mgrno , deptname)
= (CASE
WHEN deptno = 'E11' THEN
'000290'
ELSE mgrno
END
,CASE
WHEN deptno = 'B01' THEN
'PLAN AND REVIEW'
ELSE deptname
END
)
WHERE deptno IN ('E11' , 'B01')
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
Example 5) MQT was updated immediately.
Code:
------------------------------ Commands Entered ------------------------------
SELECT *
FROM emp_manage_dept_view
;
------------------------------------------------------------------------------
EMPNO FULLNAME WORKDEPT DEPTNO ADMRDEPT DEPTNAME
------ ------------------------------ -------- ------ -------- ------------------------------
000010 CHRISTINE I HAAS A00 A00 A00 SPIFFY COMPUTER SERVICE DIV.
000020 MICHAEL L THOMPSON B01 B01 A00 PLAN AND REVIEW
000030 SALLY A KWAN C01 C01 A00 INFORMATION CENTER
000050 JOHN B GEYER E01 E01 A00 SUPPORT SERVICES
000060 IRVING F STERN D11 D11 D01 MANUFACTURING SYSTEMS
000070 EVA D PULASKI D21 D21 D01 ADMINISTRATION SYSTEMS
000090 EILEEN W HENDERSON E11 - - -
000100 THEODORE Q SPENSER E21 E21 E01 SOFTWARE SUPPORT
000110 VINCENZO G LUCCHESSI A00 - - -
000120 SEAN O'CONNELL A00 - - -
000130 DELORES M QUINTANA C01 - - -
000140 HEATHER A NICHOLLS C01 - - -
000150 BRUCE ADAMSON D11 - - -
000160 ELIZABETH R PIANKA D11 - - -
000170 MASATOSHI J YOSHIMURA D11 - - -
000180 MARILYN S SCOUTTEN D11 - - -
000190 JAMES H WALKER D11 - - -
000200 DAVID BROWN D11 - - -
000210 WILLIAM T JONES D11 - - -
000220 JENNIFER K LUTZ D11 - - -
000230 JAMES J JEFFERSON D21 - - -
000240 SALVATORE M MARINO D21 - - -
000250 DANIEL S SMITH D21 - - -
000260 SYBIL P JOHNSON D21 - - -
000270 MARIA L PEREZ D21 - - -
000280 ETHEL R SCHNEIDER E11 - - -
000290 JOHN R PARKER E11 E11 E01 OPERATIONS
000300 PHILIP X SMITH E11 - - -
000310 MAUDE F SETRIGHT E11 - - -
000320 RAMLAL V MEHTA E21 - - -
000330 WING LEE E21 - - -
000340 JASON R GOUNOT E21 - - -
32 record(s) selected.
|
|
10-07-11, 07:12
|
|
Registered User
|
|
Join Date: Oct 2011
Posts: 5
|
|
Hallo tonkuma,
thanks for your post.
One question: What's the reason why the MQT in Step 1 was created successfully? I just want to join two tables like (simplified):
create table myMQT as (select
a.column01,
b.column02
from table01 a, table02 b where a.column01= b.column02
)
data initially deferred refresh immediate;
It will not work with 'refresh immediate': The fullselect specified for the materialized query table xxx is not valid
Can you help me?
|
|
10-07-11, 10:31
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
I guessed the reson might be violated this rule...
Quote:
When REFRESH IMMEDIATE is specified:
...
...
•The materialized query table must not contain duplicate rows, and the following restrictions specific to this uniqueness requirement apply, depending upon whether or not a GROUP BY clause is specified.
...
...
◦When a GROUP BY clause is not specified, the following uniqueness-related restrictions apply:
■The materialized query table's uniqueness requirement is achieved by deriving a unique key for the materialized view from one of the unique key constraints defined in each of the underlying tables. Therefore, the underlying tables must have at least one unique key constraint defined on them, and the columns of these keys must appear in the select list of the materialized query table definition.
|
CREATE TABLE - IBM DB2 9.7 for Linux, UNIX, and Windows
|
Last edited by tonkuma; 10-07-11 at 19:38.
Reason: Add "When REFRESH IMMEDIATE is specified:" to quoted text.
|
10-08-11, 03:13
|
|
Registered User
|
|
Join Date: Oct 2011
Posts: 5
|
|
A primary key is an unique key. In my example, column01 is primary key in table01 and column02 is primary key in table02 (foreign key constraint between these tables).
So I think the condition you wrote is fullfilled. But it does not work 
|
|
10-08-11, 05:00
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Meharas,
What are your DB2 version/release and platform OS?
What are your exact DDLs of tables?
Here is my trial on DB2 9.7 for Windows.
Code:
------------------------------ Commands Entered ------------------------------
CREATE TABLE table01
( column01 INTEGER NOT NULL PRIMARY KEY
, column11 VARCHAR(20)
)
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
CREATE TABLE table02
( column02 INTEGER NOT NULL PRIMARY KEY
, column21 VARCHAR(20)
)
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
create table myMQT as (select
a.column01,
b.column02
from table01 a, table02 b where a.column01= b.column02
)
data initially deferred refresh immediate
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
|
|
10-08-11, 07:37
|
|
Registered User
|
|
Join Date: Oct 2011
Posts: 5
|
|
Tonkuma,
detailled system information i will send you on monday. I will try your examble.
Here is the (shortened) definition of the tables out of my mind:
CREATE TABLE TABLE_A
COLUMN_A1 INTEGER NOT NULL,
COLUMN_A2 VARCHAR(64) NOT NULL,
CONSTRAINT PK_TABLE_A PRIMARY KEY (COLUMN_A1);
CREATE TABLE TABLE_B
COLUMN_B1 INTEGER NOT NULL,
COLUMN_A1 INTEGER NOT NULL,
COLUMN_B2 VARCHAR(64),
...
CONSTRAINT PK_TABLE_B PRIMARY KEY (COLUMN_B1)
CONSTRAINT FK_TABLE_B FOREIGN KEY (COLUMN_A1)
REFERENCES TABLE_A (COLUMN_A1);
Thanks for your help so far, have a nice weekend
|
|
10-08-11, 20:40
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Here are my results.
Both of primary keys(column_a1 and column_b1) should be included in the select-list of the MQT.
What are your create statements of MQT and the resulting error messages?
Base tables:
Code:
------------------------------ Commands Entered ------------------------------
CREATE TABLE TABLE_A
( COLUMN_A1 INTEGER NOT NULL,
COLUMN_A2 VARCHAR(64) NOT NULL,
CONSTRAINT PK_TABLE_A PRIMARY KEY (COLUMN_A1)
)
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
CREATE TABLE TABLE_B
( COLUMN_B1 INTEGER NOT NULL,
COLUMN_A1 INTEGER NOT NULL,
COLUMN_B2 VARCHAR(64),
CONSTRAINT PK_TABLE_B PRIMARY KEY (COLUMN_B1)
, CONSTRAINT FK_TABLE_B FOREIGN KEY (COLUMN_A1)
REFERENCES TABLE_A (COLUMN_A1)
)
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
MQT: Successful
Code:
------------------------------ Commands Entered ------------------------------
create table myMQT_x as (select
a.column_A1,
b.column_B1
from table_A a, table_B b where a.column_A1 = b.column_A1
)
data initially deferred refresh immediate
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
create table myMQT_y as (select
a.column_A1,
b.column_B1
from table_A a, table_B b where a.column_A1 = b.column_B1
)
data initially deferred refresh immediate
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
MQT: Error
Code:
------------------------------ Commands Entered ------------------------------
create table myMQT_z1 as (select
a.column_A1,
b.column_A1 AS b_col_a1
from table_A a, table_B b where a.column_A1 = b.column_A1
)
data initially deferred refresh immediate
;
------------------------------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20058N The fullselect specified for the materialized query table
"DB2ADMIN.MYMQT_Z1" is not valid. Reason code = "7". SQLSTATE=428EC
------------------------------ Commands Entered ------------------------------
create table myMQT_z2 as (select
a.column_A1,
b.column_B2
from table_A a, table_B b where a.column_A1 = b.column_A1
)
data initially deferred refresh immediate
;
------------------------------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20058N The fullselect specified for the materialized query table
"DB2ADMIN.MYMQT_Z2" is not valid. Reason code = "7". SQLSTATE=428EC
|
Last edited by tonkuma; 10-08-11 at 21:06.
|
10-08-11, 21:04
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
SQL20058N ... Reason code 7
Code:
...
The statement cannot be processed because it violates a restriction as
indicated by the following reason code:
...
7
When REFRESH IMMEDIATE is specified:
* the materialized query table must not contain duplicate rows
* when a GROUP BY clause is specified, all GROUP BY items must
be included in the select list
* when a GROUP BY clause is specified which contains GROUPING
SETS, CUBE, or ROLLUP, then no grouping sets can be
repeated, and if C is a nullable GROUP BY item that appears
within GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must
appear in the select list
* when no GROUP BY clause is present, then the underlying
tables must each have at least one unique key defined, and
all columns of these keys must appear in the select list of
the materialized query table definition
...
|
|
10-09-11, 06:39
|
|
Registered User
|
|
Join Date: Oct 2011
Posts: 5
|
|
I'm quite sure my definition contains both primary key but I'll know tomorrow.
Thank you anyway, have a great sunday 
|
|
10-09-11, 08:09
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Meharas,
First of all,
please let us know the error message(code and full text) you got
and (if there was) a reason code.
My last three or four posts were based on the assumption that the error message you got was "SQL20058N ... Reason code 7".
If the assumption was wrong, all these my posts would be in vain.
|
|
10-10-11, 04:21
|
|
Registered User
|
|
Join Date: Oct 2011
Posts: 5
|
|
tonkuma,
i was able to create a mqt with inner join based on your help. An primary key column was missing in the mqt selection list. But it does not work with outer join:
Code:
CREATE TABLE TABLE_A
( COLUMN_A1 INTEGER NOT NULL,
COLUMN_A2 VARCHAR(64) NOT NULL,
CONSTRAINT PK_TABLE_A PRIMARY KEY (COLUMN_A1)
);
0 record(s) affected
[Executed: 10.10.11 08:05:20 GMT ] [Execution: 13203/ms]
CREATE TABLE MQT_TABLE AS (SELECT
A.COLUMN_A1,
A.COLUMN_A2,
B.COLUMN_B1
FROM TABLE_A A LEFT JOIN TABLE_B B ON (A.COLUMN_A1=B.COLUMN_A1))
DATA INITIALLY DEFERRED REFRESH IMMEDIATE;
0 record(s) affected
[Executed: 10.10.11 08:06:46 GMT ] [Execution: 43313/ms]
CREATE TABLE MQT_TABLE AS (SELECT
A.COLUMN_B1,
B.COLUMN_A1,
B.COLUMN_A2
FROM TABLE_B A LEFT JOIN TABLE_A B ON (A.COLUMN_A1=B.COLUMN_A1))
DATA INITIALLY DEFERRED REFRESH IMMEDIATE;
DB2 SQL error: SQLCODE: -20058, SQLSTATE: 428EC, SQLERRMC: M001.MQT_TABLE;10
Message: The fullselect specified for the materialized query table "M001.MQT_TABLE" is not valid. Reason code: "10".
Any ideas? Both primary key columns are included in the mqt selection list.
|
|
10-10-11, 08:37
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
SQLCODE: -20058, SQLSTATE: 428EC,
Reason code: "10"
Quote:
10
When REFRESH IMMEDIATE is specified and the FROM clause references more than one table, only an inner join, without using the explicit INNER JOIN syntax, is supported.
|
There are more restrictions for "REFRESH IMMEDIATE".
So, I tried to simulate OUTER JOIN and REFRESH IMMEDIATE by rather complex/lengthy Steps on this thread...
Quote:
Step 1) Create MQT emp_manage_dept of which results include encoded deptno, admrdept and deptname.
Step 2) Issue Set integrity.
Step 3) Create function nbr_to_char which returns original character string from encoded number.
Step 4) Create view emp_manage_dept which makes same result of Example 1) in original post
by decoding deptno, admrdept and deptname using expressions and function nbr_to_char.
|
Some reasons of Steps 1) to 4) are...
a) SQLCODE: -20058, Reason code: 10
b) Use of subqueries are very restricted.
c) Only COUNT, COUNT_BIG, and SUM(No MAX, MIN, AVG) aggregate functions are allowed.
d) No OLAP specifications are allowed.
so on...
I don't want to show the places of documentations, because it's too troublesome for me now.
Most restrictions are documented in Description of CREATE TABLE statement.
CREATE TABLE - IBM DB2 9.7 for Linux, UNIX, and Windows
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Try to simulate MQT with REFRESH IMMEDIATE and OUTER JOIN
