1 Reply Latest reply: Jan 17, 2013 4:06 AM by APC RSS

    cursor with arrays

    user10636796
      i am trying to do matrix multiplication and i want to write the 2 arrays and create a table in a cursor. My database version is 11.1.0.6 could anybody please help?

      CREATE TABLE TESTMAT_A(
      nr INTEGER NOT NULL,
      v1 INTEGER NOT NULL,
      v2 INTEGER NOT NULL,
      v3 INTEGER NOT NULL,
      v4 INTEGER NOT NULL,
      v5 INTEGER NOT NULL,
      v6 INTEGER NOT NULL,
      v7 INTEGER NOT NULL,
      v8 INTEGER NOT NULL
      );


      INSERT INTO TESTMAT_A VALUES(1, -3, 5, -2, 8, 6, 8, 9, 0);
      INSERT INTO TESTMAT_A VALUES(2, 3, 8, 6, 4, 9, 3, 6, -7);
      INSERT INTO TESTMAT_A VALUES(3, 2, 8, 9, 3, 4, 3, 7, 9);
      INSERT INTO TESTMAT_A VALUES(4, 1, -4, 4, 5, 7, 9, 9, 5);
      INSERT INTO TESTMAT_A VALUES(5, 7, 6, 2, -9, 7, 9, 9, 6);
      INSERT INTO TESTMAT_A VALUES(6, 5, 2, 9, 6, 5, 8, 6, 8);
      INSERT INTO TESTMAT_A VALUES(7, 4, 0, 2, 5, 9, 5, 8, 3);

      --==============================================

      CREATE TABLE TESTMAT_B(
      nr INTEGER NOT NULL,
      v1 INTEGER NOT NULL,
      v2 INTEGER NOT NULL,
      v3 INTEGER NOT NULL,
      v4 INTEGER NOT NULL,
      v5 INTEGER NOT NULL,
      v6 INTEGER NOT NULL,
      v7 INTEGER NOT NULL,
      v8 INTEGER NOT NULL,
      v9 INTEGER NOT NULL
      );

      INSERT INTO TESTMAT_B VALUES(1, 18, 5, 12, 8, 0, 8, 9, -2, 1);
      INSERT INTO TESTMAT_B VALUES(2, 0, 8, -6, 4, -2, 3, 6, 5, 6);
      INSERT INTO TESTMAT_B VALUES(3, 2 , 8, 9, 3, 4, 3, 7, 4, 9);
      INSERT INTO TESTMAT_B VALUES(4, 1, 9, 0, 5, 7, 9, -9, 5, 3);
      INSERT INTO TESTMAT_B VALUES(5, 7, 6, 2, 9, 7, 9, 9, 6, 8);
      INSERT INTO TESTMAT_B VALUES(6, 5, 0, 9, 6, 5, 8, 6, 8, 9);
      INSERT INTO TESTMAT_B VALUES(7, 0, 4, 2, 5, 9, 0, 8, 6, 1);
      INSERT INTO TESTMAT_B VALUES(8, 5, 9, 5, 3, 7, -3, 0, 2, 6);



      WITH arr_A AS
      (SELECT nr, decode(mod(rownum,8),0,8,mod(rownum,8)) Col, Val FROM TESTMAT_A UNPIVOT (Val FOR Col IN (v1,v2,v3,v4,v5,v6,v7,v8)) unpvt ),

      --===== Transform matrix B into array B ======
      arr_B AS
      (SELECT nr, decode(mod(rownum,9),0,9,mod(rownum,9)) Col, Val FROM TESTMAT_B UNPIVOT (Val FOR Col IN (v1,v2,v3,v4,v5,v6,v7,v8,v9)) unpvt),


      --===== Calculate Product A*B =================
      product AS (SELECT rowA as Rw, colB as Col, sum(product) Val FROM
      (SELECT arr_A.nr rowA, arr_A.col colA,
      arr_B.nr rowB, arr_B.col colB,
      arr_A.val * arr_B.val as product
      FROM arr_A INNER JOIN arr_B
      ON arr_A.col = arr_B.nr) t1
      GROUP BY colB, rowA
      )


      the values of (Val FOR Col IN (v1,v2,v3,v4,v5,v6,v7,v8)) this need to come from a lookup table.


      Thanks in advance
        • 1. Re: cursor with arrays
          APC
          198 is a lot of posts. Enough for you to have learned the importance of formatting your code. But apparently you haven't learned this.

          You want us to help you? Please have the courtesy to make your post readable.

          So bracket your code with the {noformat}
          {noformat} tags.  This is even more important when posting a lot of code and data.
          
          Your future co-operation is appreciated.
          
          Cheers, APC