Hi!
Can anybody help me?
I'm trying migrate views from Teradata to Vertica 7.1:
In Teradata:
Select PER_ID, CONTACT_ID, START_DT, PREV_CONTACT_ID,
ROW_NUMBER() Over (Partition By PER_ID Order By START_DT RESET WHEN PREV_CONTACT_ID Is Null) As CONTACT_SERIES
FROM ....
Where ....
But in Vertica does not exists: 'RESET WHEN condition'. How can I write similar condition in Vertica?
Thanks,
Heiki
Teradata to Vertica Migration: problem with RESET WHEN
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Teradata to Vertica Migration: problem with RESET WHEN
Hi,
I am not vary familiar with Teradata syntax, but looking at the docs I think you are looking to reset the counter when a NULL is encountered in the PREVIOUS_CONTACT_ID column.
If I have this data:
Maybe you could try something like this to reset the row counter?
I am not vary familiar with Teradata syntax, but looking at the docs I think you are looking to reset the counter when a NULL is encountered in the PREVIOUS_CONTACT_ID column.
If I have this data:
Code: Select all
dbadmin=> select * from test;
per_id | contact_id | start_dt | previous_contact_id
--------+------------+------------+---------------------
1 | 10 | 2015-03-02 | 100
1 | 11 | 2015-03-03 | 101
1 | 12 | 2015-03-04 | 102
1 | 13 | 2015-03-05 |
2 | 20 | 2015-03-02 | 200
2 | 21 | 2015-03-01 | 201
2 | 22 | 2015-02-28 | 202
2 | 23 | 2015-02-27 | 203
2 | 24 | 2015-02-26 |
(9 rows)
Code: Select all
dbadmin=> select per_id, contact_id, start_dt, previous_contact_id,
dbadmin-> row_number() over (partition by nvl2(previous_contact_id, per_id, per_id*100) order by start_dt) as contact_series
dbadmin-> from test
dbadmin-> order by per_id, start_dt;
per_id | contact_id | start_dt | previous_contact_id | contact_series
--------+------------+------------+---------------------+----------------
1 | 10 | 2015-03-02 | 100 | 1
1 | 11 | 2015-03-03 | 101 | 2
1 | 12 | 2015-03-04 | 102 | 3
1 | 13 | 2015-03-05 | | 1
2 | 24 | 2015-02-26 | | 1
2 | 23 | 2015-02-27 | 203 | 1
2 | 22 | 2015-02-28 | 202 | 2
2 | 21 | 2015-03-01 | 201 | 3
2 | 20 | 2015-03-02 | 200 | 4
(9 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.