SQL20058 428EC Reason Code = 10

michael.mest · #1 (**permalink**) 08-25-11, 04:06

Hi,

I try to create an MQT with staging table, so that I can Refresh it incemental.

The Statement for the MQT looks like this:

Code:

CREATE TABLE 
	mqt.sb_kasse_umsatz 
AS(
	SELECT
		t1.id
		, t2.kontonr
		, t3.kundnr
		, t4.fid_key AS primanota
		, SUBSTR(LPAD(TRIM(CHAR(INTEGER(t4.fid_key))),4,'0'),2,2) AS bs
		, t5.fid_key AS textschlussel_erg
		, t6.fid_key AS textschluessel
		, t7.fid_key AS textschluessel_3
		, t1.buchungsdatum
		, t1.valuta
		, t1.waehrung
		, t1.betrag
	FROM
		umsatz.konto AS t1
	INNER JOIN
		konto.stamm AS t2
		ON
			t1.konto = t2.id
	INNER JOIN
		kunde.stamm AS t3
		ON
			t2.kunde = t3.id
	INNER JOIN
		umsatz.primanota AS t4
		ON
			t1.primanota = t4.id
	INNER JOIN
		umsatz.textschluessel_erg AS t5
		ON
			t1.textschluessel = t5.id
	INNER JOIN
		umsatz.textschluessel AS t6
		ON
			t5.textschluessel = t6.id
	INNER JOIN
		umsatz.textschluessel_3 AS t7
		ON
			t1.textschluessel_3 = t7.id
	WHERE
		t1.buchhalt_relev = 'J'
	AND
	(
		(
			t4.fid_key BETWEEN 1000 AND 1999
			AND
			t6.fid_key IN(41,80,81,91)
		)
		OR
		(
			t4.fid_key BETWEEN 3000 AND 3999
			AND
			t6.fid_key IN(43,91)
		)
	)
)
DATA INITIALLY DEFERRED 
REFRESH DEFERRED
IN tsdata

The Statement for the Stagingtable looks like this:

Code:

CREATE TABLE 
	mqt.stg_sb_kasse_umsatz 
FOR 
	mqt.sb_kasse_umsatz
PROPAGATE IMMEDIATE

When I execute the second statement, I get the error "SQL20058 428EC Reason Code = 10" which means, that I have to create the MQT with REFRESH DEFERRED or to use INNER JOIN without explicit use of INNER JOIN Syntax.

As u can see I am creating the MQT with REFRESH DEFERRED.
I also tried to user "JOIN" instead of "INNER JOIN" but the result is the same as above.

Can anyone give me a hint?

I cannot create it without a staging table, because I really need the benefit of an incremental refresh.

My DB/2 Version is 9.7 FP2 on Intel.

Thanks for your help

n_i · #2 (**permalink**) 08-25-11, 07:52

Quote:

Originally Posted by michael.mest

Can anyone give me a hint?

Read the fine manual, as always.

Quote:

Originally Posted by the fine manual

If the materialized query table is created with the intention of providing it with an associated staging table in a later statement, the fullselect of the materialized query table must follow the same restrictions and rules as a fullselect used to create a materialized query table with the REFRESH IMMEDIATE option.

michael.mest · #3 (**permalink**) 08-25-11, 09:52

Ok, thanks.

I did read it, but it seems that I didn't understand. ;-)

I've solved it now by "not using explicit INNER JOIN" and in addition to this, I had to include the Primary Key of each table in the result set.

So the statement looks like this now:

Code:

CREATE TABLE 
	mqt.sb_kasse_umsatz 
AS(
	SELECT
		t1.id AS umsatz_id
		, t2.id AS konto_id
		, t3.id AS kunden_id
		, t4.id AS pn_id
		, t5.id AS txtsl_erg_id
		, t6.id AS txtsl_id
		, t7.id AS txtsl_3_id
		, t2.kontonr
		, t3.kundnr
		, t4.fid_key AS primanota
		, SUBSTR(LPAD(TRIM(CHAR(INTEGER(t4.fid_key))),4,'0'),2,2) AS bs
		, t5.fid_key AS textschlussel_erg
		, t6.fid_key AS textschluessel
		, t7.fid_key AS textschluessel_3
		, t1.buchungsdatum
		, t1.valuta
		, t1.waehrung
		, t1.betrag
	FROM
		umsatz.konto AS t1
	,konto.stamm AS t2
	,kunde.stamm AS t3
	,umsatz.primanota AS t4
	,umsatz.textschluessel_erg AS t5
	,umsatz.textschluessel AS t6
	,umsatz.textschluessel_3 AS t7		
	WHERE
		t1.buchhalt_relev = 'J'
	AND
	(
			t1.konto = t2.id
		AND
			t2.kunde = t3.id
		AND
			t1.primanota = t4.id
		AND
			t1.textschluessel = t5.id
		AND
			t5.textschluessel = t6.id
		AND
			t1.textschluessel_3 = t7.id
	)
	AND
	(
		(
			t4.fid_key BETWEEN 1000 AND 1999
			AND
			t6.fid_key IN(41,80,81,91)
		)
		OR
		(
			t4.fid_key BETWEEN 3000 AND 3999
			AND
			t6.fid_key IN(43,91)
		)
	)
)
DATA INITIALLY DEFERRED 
REFRESH DEFERRED
IN tsdata

Internet Services

Web Development

Digital Marketing

Consumer Tech