Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    8

    Question Unanswered: SQL20058 428EC Reason Code = 10

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by michael.mest View Post

    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.
    Last edited by n_i; 08-25-11 at 08:52. Reason: No reason

  3. #3
    Join Date
    Jun 2009
    Posts
    8
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •