RCV2SIMPLE

This procedure transforms a table in Row/Column?Value format to the "simple" matrix table. Input can be in the form of a table or a matrix.

Usage

The RCV2SIMPLE stored procedure has the following syntax:
RCV2SIMPLE(paramString,intable,inmatrix,inmeta,outtable)
Parameters
paramString
The input parameters specification.
Type: TEXT
intable
This parameter is used when the input is in table form.
Type: NVARCHAR(ANY)
inmatrix
This parameter is used when the input is in matrix form.
Type: NVARCHAR(ANY)
inmeta
The name of the input metadata table created by SIMPLE2RCV_ADV.
Type: NVARCHAR(ANY)
outtable
The name of the output data table in simple format.
Type: NVARCHAR(ANY)
Returns
INTEGER The number of rows in the output table.

Details

A "simple" matrix table is a database table where each table row contains a row index value and the matrix element values of the corresponding matrix row. This procedure supports nominal at-tribute value composition, transforming 0/1 dummy variables back to the original values recorded in the dictionary tables listed in the metadata table. The number of matrix columns must be less than 1600. Input can be in the form of a table or a matrix.

Examples

CREATE TABLE SIMPLE1 (ID INTEGER, V1 DOUBLE, V2 DOUBLE,
 V3 DOUBLE);
INSERT INTO SIMPLE1 VALUES(1, 100001, 100002, 100003); 
INSERT INTO SIMPLE1 VALUES(4, 200001, 200002, 200003); 
INSERT INTO SIMPLE1 VALUES(9, 300001, 300002, 300003); 
CALL NZM..SIMPLE2RCV_ADV('outtable=RCV1, outmeta=RCV_META1, 
 intable=SIMPLE1, incolumnlist=., id=ID');
CALL NZM..RCV2SIMPLE('intable=RCV1, inmeta=RCV_META1,
 outtable=SIMPLE2');
SELECT * FROM SIMPLE2;
SELECT * FROM RCV1;
SELECT * FROM RCV_META1;
DROP TABLE SIMPLE1;
DROP TABLE SIMPLE2;
DROP TABLE RCV1;
DROP TABLE RCV_META1;

 SIMPLE2RCV_ADV
----------------
 3
(1 row)

 RCV2SIMPLE
------------
 3
(1 row)

 ID |     V1 |   V2   |   V3
----+--------+--------+--------
  2 | 200001 | 200002 | 200003
  1 | 100001 | 100002 | 100003
  3 | 300001 | 300002 | 300003
(3 rows)

 ROW | COL | VALUE
-----+-----+--------
   1 |   1 | 100001
   1 |   2 | 100002
   1 |   3 | 100003
   3 |   1 | 300001
   3 |   2 | 300002
   3 |   3 | 300003
   2 |   1 | 200001
   2 |   2 | 200002
   2 |   3 | 200003
(9 rows)

 COLID | COLNAME | COLDICT | OUTCOLBEG | OUTCOLEND
-------+---------+---------+-----------+-----------
      2| V2      |         |          2|         2
      1| V1      |         |          1|         1
      3| V3      |         |          3|         3
(3 rows)

CREATE TABLE SIMPLE1 (ID INTEGER, V1 DOUBLE, V2 DOUBLE,
 V3 DOUBLE);
INSERT INTO SIMPLE1 VALUES(1, 100001, 100002, 100003); 
INSERT INTO SIMPLE1 VALUES(4, 200001, 200002, 200003); 
INSERT INTO SIMPLE1 VALUES(9, 300001, 300002, 300003); 
CALL NZM..SIMPLE2RCV_ADV('outtable=RCV1, outmeta=RCV_META1,
 intable=SIMPLE1, incolumnlist=., id=ID');
CALL NZM..CREATE_MATRIX_FROM_TABLE('RCV1', 'MATRIX1', 3, 3);
 -- Input the matrix name, rather than the table name 
CALL NZM..RCV2SIMPLE('inmatrix=MATRIX1, inmeta=RCV_META1,
 outtable=SIMPLE2');
SELECT * FROM SIMPLE2;
SELECT * FROM RCV1;
SELECT * FROM RCV_META1;
DROP TABLE SIMPLE1;
DROP TABLE SIMPLE2;
DROP TABLE RCV1;
DROP TABLE RCV_META1;
CALL nzm..DELETE_MATRIX('MATRIX1');

 SIMPLE2RCV_ADV
----------------
 3
(1 row)

 CREATE_MATRIX_FROM_TABLE
--------------------------
 t
(1 row)

 RCV2SIMPLE
------------
 3
(1 row)

 ID |    V1  |    V2  |   V3
----+--------+--------+--------
  2 | 200001 | 200002 | 200003
  3 | 300001 | 300002 | 300003
  1 | 100001 | 100002 | 100003
(3 rows)

 ROW | COL |VALUE
-----+-----+--------
   2 |   1 | 200001
   2 |   2 | 200002
   2 |   3 | 200003
   1 |   1 | 100001
   1 |   2 | 100002
   1 |   3 | 100003
   3 |   1 | 300001
   3 |   2 | 300002
   3 |   3 | 300003
(9 rows)

 COLID | COLNAME | COLDICT | OUTCOLBEG | OUTCOLEND
-------+---------+---------+-----------+-----------
      1| V1      |         |          1|         1
      3| V3      |         |          3|         3
      2| V2      |         |          2|         2
(3 rows)

 DELETE_MATRIX
---------------
 t
(1 row)

CREATE TABLE SIMPLE1 (ID INTEGER, V1 DOUBLE, V2 DOUBLE, V3 DOUBLE);
INSERT  INTO  SIMPLE1  VALUES(1,  100001,  100002,  100003); 
INSERT  INTO  SIMPLE1  VALUES(4,  200001,  200002,  200003); 
INSERT INTO SIMPLE1 VALUES(9, 300001, 300002, 300003); 
 -- Treat V1 and V3 as nominal attributes
CALL NZM..SIMPLE2RCV_ADV('outtable=RCV1, outmeta=RCV_META1,
 intable=SIMPLE1, incolumnlist=., nomcolumnlist=V1;V3, id=ID');
CALL NZM..RCV2SIMPLE('intable=RCV1, inmeta=RCV_META1,
 outtable=SIMPLE2');
SELECT * FROM SIMPLE2;
SELECT * FROM RCV1;
SELECT COLID, COLNAME, OUTCOLBEG, OUTCOLEND FROM RCV_META1;
DROP TABLE SIMPLE1;
DROP TABLE SIMPLE2;
DROP TABLE RCV1;
DROP TABLE RCV_META1;

 SIMPLE2RCV_ADV
----------------
 3
(1 row)
 
 RCV2SIMPLE
------------
 3
(1 row)

 ID |     V1 |     V2 |   V3
----+--------+--------+--------
  1 | 100001 | 100002 | 100003
  3 | 300001 | 300002 | 300003 
  2 | 200001 | 200002 | 200003
(3 rows)

 ROW | COL |VALUE
-----+-----+--------
   1 |   1 |      1
   1 |   2 |      0
   1 |   3 |      0
   1 |   4 | 100002
   1 |   5 |      1
   1 |   6 |      0
   1 |   7 |      0
   2 |   1 |      0
   2 |   2 |      1
   2 |   3 |      0
   2 |   4 | 200002 
   2 |   5 |      0
   2 |   6 |      1
   2 |   7 |      0
   3 |   1 |      0
   3 |   2 |      0
   3 |   3 |      1
   3 |   4 | 300002
   3 |   5 |      0
   3 |   6 |      0
   3 |   7 |      1
(21 rows)

 COLID | COLNAME | OUTCOLBEG | OUTCOLEND
-------+---------+-----------+-----------
      2| V2      |          4|         4
      1| V1      |          1|         3 
      3| V3      |          5|         7
(3 rows)