Hi all, I have several tables which represent different sources (sensors) of data. I want to find out which sensors picked up the signal first, and thus I decided to join these tables together, rank on common criteria and then select the first ones. However, that fails with: TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors Any ideas how to fix this? Cheers, Nikola Data: CREATE TABLE taba ( aday DATE, key1 varchar(15), msid int, sid int, eid bigint, r bigint ); CREATE TABLE tabb ( aday DATE, key1 varchar(15), msid int, sid int, aid bigint, eid bigint, r bigint ); CREATE TABLE tabc ( aday DATE, key1 varchar(15), msid int, sid int, aid bigint, m bigint, r bigint ); COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|200|100 2016-05-02|inst1|675|10002|500|111 2016-05-02|inst1|789|10001|800|135 COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|77|200|88 2016-05-02|inst1|675|10002|100|500|112 2016-05-02|inst1|789|10001|112|800|134 COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|77|200|100 2016-05-02|inst1|675|10002|100|500|111 2016-05-02|inst1|789|10001|112|800|130 Query: SELECT aday, key1, msid, sid, aid, eid, src, r, rank FROM ( SELECT * ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank FROM ( (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) UNION (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) UNION (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) ) AS dummy1 ) AS dummy2 WHERE rank = 1 ; Results: sql>CREATE TABLE taba ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>eid bigint, more>r bigint more>); operation successful (4.102ms) sql>CREATE TABLE tabb ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>aid bigint, more>eid bigint, more>r bigint more>); operation successful (3.172ms) sql>CREATE TABLE tabc ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>aid bigint, more>m bigint, more>r bigint more>); operation successful (3.326ms) sql> sql>COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|200|100 more>2016-05-02|inst1|675|10002|500|111 more>2016-05-02|inst1|789|10001|800|135 3 affected rows (88.249ms) sql> sql>COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|77|200|88 more>2016-05-02|inst1|675|10002|100|500|112 more>2016-05-02|inst1|789|10001|112|800|134 3 affected rows (59.917ms) sql> sql>COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|77|200|100 more>2016-05-02|inst1|675|10002|100|500|111 more>2016-05-02|inst1|789|10001|112|800|130 3 affected rows (89.804ms) sql> sql>select * from taba; +------------+-------+------+-------+------+------+ | aday | key1 | msid | sid | eid | r | +============+=======+======+=======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 200 | 100 | | 2016-05-02 | inst1 | 675 | 10002 | 500 | 111 | | 2016-05-02 | inst1 | 789 | 10001 | 800 | 135 | +------------+-------+------+-------+------+------+ 3 tuples (4.340ms) sql>select * from tabb; +------------+-------+------+-------+------+------+------+ | aday | key1 | msid | sid | aid | eid | r | +============+=======+======+=======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | 88 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | 112 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | 134 | +------------+-------+------+-------+------+------+------+ 3 tuples (3.484ms) sql>select * from tabc; +------------+-------+------+-------+------+------+------+ | aday | key1 | msid | sid | aid | eid | m | +============+=======+======+=======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | 100 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | 111 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | 130 | +------------+-------+------+-------+------+------+------+ 3 tuples (3.215ms) sql>SELECT * more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2; +------------+-------+------+-------+------+------+------+------+------+ | aday | key1 | msid | sid | aid | eid | src | r | rank | +============+=======+======+=======+======+======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | null | 200 | TABA | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | null | 500 | TABA | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | null | 800 | TABA | 135 | 3 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABB | 88 | 1 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABB | 112 | 2 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABB | 134 | 2 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABC | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABC | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABC | 130 | 1 | +------------+-------+------+-------+------+------+------+------+------+ 9 tuples (13.527ms) sql>SELECT aday, key1, msid, sid, aid, eid, src, r, rank more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2 more>WHERE rank = 1 more>; TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors