Results 1 to 4 of 4

Thread: MQT with JOIN

  1. #1
    Join Date
    Oct 2011
    Posts
    8

    Angry Unanswered: MQT with JOIN

    I'm trying to create a MQT which needs to be up to date at any time.

    SQL:

    Code:
    CREATE TABLE TABLE_1 (
            TABLE_1_ID INTEGER NOT NULL PRIMARY KEY, 
            TABLE_2_ID INTEGER); 
    
    CREATE TABLE TABLE_2 (
             TABLE_2_ID INTEGER NOT NULL PRIMARY KEY, 
             TABLE_2_NAME VARCHAR(32) NOT NULL);   
    
    CREATE TABLE TABLE_MQT AS (SELECT 
          A.TABLE_1_ID, 
          A.TABLE_2_ID,
          B.TABLE_2_NAME
          FROM  TABLE_1 A, TABLE_2 B           
          WHERE  A.TABLE_2_ID = B.TABLE_2_ID)
    DATA INITIALLY DEFERRED REFRESH IMMEDIATE
    ENABLE QUERY OPTIMIZATION
    MAINTAINED BY SYSTEM;

    This does not work:
    Code:
    SQL20058N  Der für die MQT "ROOT.TABLE_MQT" angegebene Fullselect ist nicht 
    gültig. Ursachencode: "7".  SQLSTATE=428EC
    
    SQL20058N  Der für die MQT "ROOT.TABLE_MQT" angegebene Fullselect ist nicht gültig. Ursachencode: "7

    I tried REFRESH DEFERRED but the creation of a related stating table also failed.

    Code:
    CREATE TABLE TABLE_STG FOR TABLE_MQT PROPAGATE IMMEDIATE;
    
    DB21034E  Der Befehl wurde als SQL-Anweisung verarbeitet, da es sich um keinen 
    gültigen Befehl des Befehlszeilenprozessors handelte.  Während der 
    SQL-Verarbeitung wurde Folgendes ausgegeben:
    SQL20058N  Der für die MQT "ROOT.TABLE_MQT" angegebene Fullselect ist nicht 
    gültig. Ursachencode: "7".  SQLSTATE=428EC
    
    SQL20058N  Der für die MQT "ROOT.TABLE_MQT" angegebene Fullselect ist nicht gültig. Ursachencode: "7
    Any Idea why this simple JOIN does not work?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Have you tried reading the explanation for SQL20058N, specifically reason code 7?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2011
    Posts
    8
    Thanks a lot!

    The last point was the solution: All primary keys of the relation tables must occur in the select statement:

    Code:
    CREATE TABLE TABLE_MQT AS (SELECT 
          A.TABLE_1_ID, 
          B.TABLE_2_ID,
          B.TABLE_2_NAME
          FROM  TABLE_1 A, TABLE_2 B           
          WHERE  A.TABLE_2_ID = B.TABLE_2_ID)
    DATA INITIALLY DEFERRED REFRESH IMMEDIATE
    ENABLE QUERY OPTIMIZATION
    MAINTAINED BY SYSTEM;

  4. #4
    Join Date
    Oct 2011
    Posts
    8
    Another question in this case:

    Is there any way to replace a left join-condition to use in a mqt?

    Code:
    CREATE TABLE TABLE1
       COLUMN1 INTEGER NOT NULL PRIMARY KEY,
       COLUMN2 INTEGER; --NULLABLE
    
    CREATE TABLE TABLE2
       COLUMN1 INTEGER NOT NULL PRIMARY KEY,
       COLUMN2 NVARCHAR;
    
    CREATE VIEW LEFT_JOIN AS (SELECT
       A.COLUMN1,
       B.COLUMN1,
       B.COLUMN2
       FROM TABLE1 A LEFT JOIN TABLE2 B ON (A.COLUMN2 = B.COLUMN1));
    This does not work:

    Code:
    CREATE VIEW WITHOUT_LEFT_JOIN AS (SELECT
       A.COLUMN1,
       B.COLUMN1,
       B.COLUMN2
       FROM TABLE1 A, TABLE2 B WHERE A.COLUMN2 IS NULL OR A.COLUMN2 = B.COLUMN1);

Tags for this Thread

Posting Permissions

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