RCV2SIMPLE_NUM

This procedure transforms a row/column/value table to a "simple" matrix table. Input can be in the form of a table or a matrix.

Usage

The RCV2SIMPLE_NUM stored procedure has the following syntax:
RCV2SIMPLE_NUM(paramString,intable,colprefix,inmatrix,outtable)
Parameters
paramString
The input parameters specification.
Type: TEXT
intable
This parameter is used when the input is in table form.
Type: NVARCHAR(ANY)
colprefix
The prefix of the column names for the new table.
Type: NVARCHAR(ANY)
inmatrix
This parameter is used when the input is in matrix form.
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 attribute 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_NUM('intable=RCV1,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_NUM
----------------
 3
(1 row)

 ID |  COL1  |  COL2  |  COL3
----+--------+--------+--------
  2 | 200001 | 200002 | 200003
  3 | 300001 | 300002 | 300003
  1 | 100001 | 100002 | 100003
(3 rows)

 ROW | COL | VALUE
-----+-----+--------
    1|    1| 100001
    1|    2| 100002
    1|    3| 100003
    2|    1| 200001
    2|    2| 200002
    2|    3| 200003
    3|    1| 300001
    3|    2| 300002
    3|    3| 300003
(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..RCV2SIMPLE_NUM('intable=RCV1, outtable=SIMPLE3,
 colprefix=column');
SELECT * FROM SIMPLE3;
SELECT * FROM RCV1;
SELECT * FROM RCV_META1;
DROPTABLE SIMPLE1;
DROP TABLE SIMPLE3;
DROP TABLE RCV1;
DROP TABLE RCV_META1;

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

 RCV2SIMPLE_NUM
----------------
 3
(1 row)

 ID | COLUMN1 | COLUMN2 | COLUMN3
----+---------+---------+---------
  2 |  200001 |  200002 |  200003
  3 |  300001 |  300002 |  300003
  1 |  100001 |  100002 |  100003
(3 rows)

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

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