Teradata to Vertica Migration: problem with RESET WHEN

Moderator: NorbertKrupa

Post Reply
heikikr
Newbie
Newbie
Posts: 1
Joined: Sat Feb 28, 2015 7:41 pm

Teradata to Vertica Migration: problem with RESET WHEN

Post by heikikr » Sat Feb 28, 2015 8:17 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Teradata to Vertica Migration: problem with RESET WHEN

Post by JimKnicely » Mon Mar 02, 2015 1:18 pm

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:

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)
Maybe you could try something like this to reset the row counter?

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

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Migration”