SIMPLE2RCV_ADV
This procedure transforms a table to row/column/value representation.
Usage
The SIMPLE2RCV_ADV stored procedure has the following syntax:
- SIMPLE2RCV(paramString,outtable,outmeta,intable,id,incolumnlist,nomcolumnlist,colPropertiesTable)
- Parameters
- paramString
- The input parameters specification.
- outtable
- The name of the output row/column/value table.
- outmeta
- The name of the output metadata table.
- intable
- The name of the input table.
- id
- The name of the column containing unique ID values.
- incolumnlist
- The list of names of the input columns. Column names are separated by semicolons. A dot matches all columns. A dash followed by a column name excludes the named column.
- nomcolumnlist
- The list of names of the input columns representing nominal attributes to be decomposed.
- colPropertiesTable
- The name of the table where the column properties definitions are stored.
Details
A metadata table is produced to record the mapping of input columns to output columns. Nominal attributes are supported. Dictionary tables referenced in the metadata table list the unique values for each nominal attribute. Decomposition of nominal factor values into separate columns is also supported.
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');
SELECT 'SIMPLE1',* FROMSIMPLE1 ORDER BY 1,2,3;
SELECT 'RCV_META1',* FROMRCV_META1 ORDER BY 1,2,3;
SELECT 'RCV1',* FROMRCV1 ORDER BY 1,2,3;
DROP TABLE SIMPLE1;DROP TABLE RCV1;
DROP TABLE RCV_META1;
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? | 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
----------+-----+-----+--------
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)
CREATE TABLE SIMPLE1 (ID INTEGER, V1 DOUBLE, V2 DOUBLE, V3DOUBLE);
INSERT INTO SIMPLE1VALUES(1, 100001, 100002, 100003);
INSERT INTO SIMPLE1VALUES(4, 200001, 200002, 200003);
INSERT INTO SIMPLE1VALUES(9, 300001, 300002, 300003);
-- Use all columns except V2
CALL NZM..SIMPLE2RCV_ADV('outtable=RCV1, outmeta=RCV_META1,
intable=SIMPLE1,incolumnlist=.;-V2, id=ID');
SELECT 'SIMPLE1',* FROMSIMPLE1 ORDER BY 1,2,3;
SELECT 'RCV_META1',* FROM RCV_META1 ORDER BY 1,2,3;
SELECT 'RCV1',* FROMRCV1 ORDER BY 1,2,3;
DROP TABLE SIMPLE1;
DROP TABLE RCV1;
DROP TABLE RCV_META1;
SIMPLE2RCV_ADV
----------------
2
(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? | COLID | COLNAME | COLDICT | OUTCOLBEG | OUTCOLEND
-----------+-------+---------+---------+-----------+-----------
RCV_META1 | 1 | V1 | | 1 | 1
RCV_META1 | 2 | V3 | | 2 | 2
(2 rows)
?COLUMN? | ROW | COL | VALUE
----------+-----+-----+--------
RCV1 | 1 | 1 | 100001
RCV1 | 1 | 2 | 100003
RCV1 | 2 | 1 | 200001
RCV1 | 2 | 2 | 200003
RCV1 | 3 | 1 | 300001
RCV1 | 3 | 2 | 300003
(6 rows)
CREATE TABLE SIMPLE1 (ID INTEGER, V1 DOUBLE, V2 DOUBLE,
V3 DOUBLE, N1 VARCHAR(5), N2 NVARCHAR(5));
INSERT INTO SIMPLE1 VALUES(1, 100001, 100002, 100003, 'one','jeden');
INSERT INTO SIMPLE1 VALUES(4, 200001, 200002, 200003, 'two','dwa');
INSERT INTO SIMPLE1 VALUES(9, 300001, 300002, 300003,'three','trzy');
-- Treat N1 and N2 as nominal attributes
CALL NZM..SIMPLE2RCV_ADV('outtable=RCV1, outmeta=RCV_META1,
intable=SIMPLE1, incolumnlist=., nomcolumnlist=N1;N2, id=ID');
SELECT 'SIMPLE1',* FROMSIMPLE1 ORDER BY 1,2,3;
SELECT 'RCV_META1',COLID, COLNAME, OUTCOLBEG, OUTCOLEND
FROMRCV_META1 ORDER BY 1,2,3;
SELECT 'RCV1',* FROM RCV1 ORDERBY 1,2,3;
DROP TABLE SIMPLE1;
DROP TABLE RCV1;
DROP TABLE RCV_META1;
SIMPLE2RCV_ADV
----------------
5
(1 row)
?COLUMN? | ID | V1 | V2 | V3 | N1 | N2
----------+----+--------+--------+--------+-------+-------
SIMPLE1 | 1 | 100001 | 100002 | 100003 | one | jeden
SIMPLE1 | 4 | 200001 | 200002 | 200003 | two | dwa
SIMPLE1 | 9 | 300001 | 300002 | 300003 | three | trzy
(3 rows)
?COLUMN? | COLID | COLNAME | OUTCOLBEG | OUTCOLEND
-----------+-------+---------+-----------+-----------
RCV_META1 | 1 | V1 | 1 | 1
RCV_META1 | 2 | V2 | 2 | 2
RCV_META1 | 3 | V3 | 3 | 3
RCV_META1 | 4 | N1 | 4 | 6
RCV_META1 | 5 | N2 | 7 | 9
(5 rows)
?COLUMN? | ROW | COL |VALUE
----------+-----+-----+--------
RCV1 | 1 | 1 | 100001
RCV1 | 1 | 2 | 100002
RCV1 | 1 | 3 | 100003
RCV1 | 1 | 4 | 1
RCV1 | 1 | 5 | 0
RCV1 | 1 | 6 | 0
RCV1 | 1 | 7 | 0
RCV1 | 1 | 8 | 1
RCV1 | 1 | 9 | 0
RCV1 | 2 | 1 | 200001
RCV1 | 2 | 2 | 200002
RCV1 | 2 | 3 | 200003
RCV1 | 2 | 4 | 0
RCV1 | 2 | 5 | 0
RCV1 | 2 | 6 | 1
RCV1 | 2 | 7 | 1
RCV1 | 2 | 8 | 0
RCV1 | 2 | 9 | 0
RCV1 | 3 | 1 | 300001
RCV1 | 3 | 2 | 300002
RCV1 | 3 | 3 | 300003
RCV1 | 3 | 4 | 0
RCV1 | 3 | 5 | 1
RCV1 | 3 | 6 | 0
RCV1 | 3 | 7 | 0
RCV1 | 3 | 8 | 0
RCV1 | 3 | 9 | 1
(27 rows)
CREATE TABLE SIMPLE1 (ID INTEGER, V1 DOUBLE, V2 DOUBLE,
V3 DOUBLE, N1 VARCHAR(5), N2 NVARCHAR(5));
INSERT INTO SIMPLE1 VALUES(1, 100001, 100002, 100003, 'one','jeden');
INSERT INTO SIMPLE1 VALUES(4, 200001, 200002, 200003, 'two','dwa');
INSERT INTO SIMPLE1 VALUES(9, 300001, 300002, 300003, 'three','trzy');
CALL nza..COLUMN_PROPERTIES('intable=SIMPLE1,outtable=CPT1');
CALL nza..SET_COLUMN_PROPERTIES('intable=SIMPLE1 ,
colPropertiesTable=CPT1,incolumn=ID:id');
-- Treat N1 and N2 as nominal attributes
CALL NZM..SIMPLE2RCV_ADV('outtable=RCV1, outmeta=RCV_META1,
intable=SIMPLE1,colPropertiesTable=CPT1');
SELECT 'SIMPLE1',* FROMSIMPLE1 ORDER BY 1,2,3;
SELECT 'RCV_META1', COLID, COLNAME, OUTCOLBEG,
OUTCOLENDFROMRCV_META1 ORDER BY 1,2,3;
SELECT 'CPT1',* FROMCPT1 ORDER BY 1,2,3;
SELECT 'RCV1',* FROMRCV1 ORDER BY 1,2,3;
DROP TABLE SIMPLE1;DROP TABLE RCV1;
DROP TABLE RCV_META1;
DROP TABLE CPT1;
COLUMN_PROPERTIES
-------------------
6
(1 row)
SET_COLUMN_PROPERTIES
-----------------------
t
(1 row)
SIMPLE2RCV_ADV
----------------
5
(1 row)
?COLUMN? | ID | V1 | V2 | V3 | N1 | N2
----------+----+--------+--------+--------+-------+-------
SIMPLE1 | 1 | 100001 | 100002 | 100003 | one | jeden
SIMPLE1 | 4 | 200001 | 200002 | 200003 | two | dwa
SIMPLE1 | 9 | 300001 | 300002 | 300003 | three | trzy
(3 rows)
?COLUMN? | COLID | COLNAME | OUTCOLBEG | OUTCOLEND
-----------+-------+---------+-----------+-----------
RCV_META1 | 1 | V1 | 1 | 1
RCV_META1 | 2 | V2 | 2 | 2
RCV_META1 | 3 | V3 | 3 | 3
RCV_META1 | 4 | N1 | 4 | 6
RCV_META1 | 5 | N2 | 7 | 9
(5 rows)
?COLUMN? | IDCOL | COLNAME | COLDATATYPE | COLTYPE | COLROLE |COLWEIGHT
----------+-------+---------+----------------------------+---------+---------+---------
CPT1 | 1 | ID | INTEGER | cont | id | 1
CPT1 | 2 | V1 | DOUBLE PRECISION | cont | input | 1
CPT1 | 3 | V2 | DOUBLE PRECISION | cont | input | 1
CPT1 | 4 | V3 | DOUBLE PRECISION | cont | input | 1
CPT1 | 5 | N1 | CHARACTER VARYING(5)| nom | input | 1
CPT1 | 6 | N2 |NATIONAL CHARACTERVARYING(5)| nom | input | 1
(6 rows)
?COLUMN? | ROW | COL |VALUE
----------+-----+-----+--------
RCV1 | 1 | 1 | 100001
RCV1 | 1 | 2 | 100002
RCV1 | 1 | 3 | 100003
RCV1 | 1 | 4 | 1
RCV1 | 1 | 5 | 0
RCV1 | 1 | 6 | 0
RCV1 | 1 | 7 | 0
RCV1 | 1 | 8 | 1
RCV1 | 1 | 9 | 0
RCV1 | 2 | 1 | 200001
RCV1 | 2 | 2 | 200002
RCV1 | 2 | 3 | 200003
RCV1 | 2 | 4 | 0
RCV1 | 2 | 5 | 0
RCV1 | 2 | 6 | 1
RCV1 | 2 | 7 | 1
RCV1 | 2 | 8 | 0
RCV1 | 2 | 9 | 0
RCV1 | 3 | 1 | 300001
RCV1 | 3 | 2 | 300002
RCV1 | 3 | 3 | 300003
RCV1 | 3 | 4 | 0
RCV1 | 3 | 5 | 1
RCV1 | 3 | 6 | 0
RCV1 | 3 | 7 | 0
RCV1 | 3 | 8 | 0
RCV1 | 3 | 9 | 1
(27 rows)
(27 rows)