APPLY_SIMPLE2RCV_ADV

Transforms a table to row/column/value representation with nominal attributes decomposition based on previous decomposition. For each factor of a nominal attribute creates a separate column.

Usage

The APPLY_SIMPLE2RCV_ADV stored procedure has the following syntax:

APPLY_SIMPLE2RCV_ADV(NVARCHAR(ANY) paramString)

Parameters

paramString: The input parameters specification.; Type: NVARCHAR(ANY)
outtable: The name of the output RCV table.; Type: NVARCHAR(ANY)
inmeta: The name of the input meta.; Type: NVARCHAR(ANY)
intable: The name of the input table.; Type: NVARCHAR(ANY)
id: The name of the column with unique values representing the ID.; Type: NVARCHAR(ANY)

Returns

INTEGER

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); 
-- Use columns V1, V2, and V3
CALL NZM..SIMPLE2RCV_ADV('outtable=RCV1, 
outmeta=RCV_META1, intable=SIMPLE1, 
incolumnlist=V1;V2;V3, id=ID');
CALL NZM..APPLY_SIMPLE2RCV_ADV('outtable=RCV2, 
inmeta=RCV_META1, intable=SIMPLE1, id=ID');
SELECT 'SIMPLE1',* FROM SIMPLE1 ORDER BY ID;
SELECT 'RCV1',* FROM RCV1 ORDER BY ROW, COL;
SELECT 'RCV_META1',* FROM RCV_META1 ORDER BY COLID;
SELECT 'RCV2',* FROM RCV2 ORDER BY ROW, COL;
DROP TABLE SIMPLE1;
DROP TABLE RCV1;
DROP TABLE RCV_META1;
DROP TABLE RCV2;

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

 APPLY_SIMPLE2RCV_ADV
----------------------
                    3
(1 row)

 ?COLUMN? | ID |   V1   |   V2   |   V3
----------+----+--------+--------+--------
 SIMPLE1  |  1 | 100001 | 100002 | 100003
 SIMPLE1  |  4 | 200001 | 200002 | 200003
 SIMPLE1  |  9 | 300001 | 300002 | 300003
(3 rows)

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

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

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