HANA Column table add column of type DATE with dynamic values

[former_member184111]

Dear Experts,

I want to add a new column to a table and this column should have value based on a simple logic as below...

row 1 - column value = current date

row 2 - column value = current date + 30 days

row 3 - column value = current date + 30 days.... and so on till the last row

Do I need to use a scalar function while using ALTER TABLE to add column?

What should be the syntax:

altertable SCHEMA_NAME.TABLE_NAME add
( created_on date default <how to populate values as mentioned above?> ) ; 

Thanks,

Anubhav

[VolkerBarth]

You have chosen the "SAP SQL Anywhere" tag, and "SAP SQL Anywhere" is a particular DBMS - as you seem to ask about HANA, I would suggest that you re-tag the question accordingly so the fitting community members notice your question...

(And sorry, I don't know the answer.)

[lucas_oliveira · Accepted solution]

Hi Anubhav,

You're not making clear if you need to update the entire rowset and then add the calculated column or exclusively want only one.

In fact having the business case fully described and what you've done so far would be great.

Meanwhile I'll do the usual guesswork:

If you're considering just an update on the whole table then row_number window function would help you to get the dates.

I did a small test in that sense. Here's what I got:

drop table TESTGEN;
create column table TESTGEN (col1 varchar(40) primary key, col2 date);

-- col2 is null so far and col1 would have the pkey
insert into TESTGEN (col1) select hash_md5(to_varbinary(rownum)) col1 from (select top 10 row_number() over() as rownum from objects);

select * from testgen order by COL1;

/* results
COL1                            ;COL2
1679091C5A880FAF6FB5E6087EB1B2DC;?   
45C48CCE2E2D7FBDEA1AFC51C7C6AD26;?   
8F14E45FCEEA167A5A36DEDD4BEA2543;?   
A87FF679A2F3E71D9181A67B7542122C;?   
C4CA4238A0B923820DCC509A6F75849B;?   
C81E728D9D4C2F636F067F89CC14862C;?   
C9F0F895FB98AB9159F51FD0297E236D;?   
D3D9446802A44259755D38E6D163E820;?   
E4DA3B7FBBCE2345D7772B0674A318D5;?   
ECCBC87E4B5CE2FE28308FD9F2A7BAF3;?    
*/

-- generating calculated date ***considering ordering by pkey asc***
select col1, add_days(current_date, (mult-1)*30) as thedate from 
 (select col1, row_number() over(order by col1) as mult from testgen)
 
/* results
COL1                            ;THEDATE   
1679091C5A880FAF6FB5E6087EB1B2DC;2016-11-24
45C48CCE2E2D7FBDEA1AFC51C7C6AD26;2016-12-24
8F14E45FCEEA167A5A36DEDD4BEA2543;2017-01-23
A87FF679A2F3E71D9181A67B7542122C;2017-02-22
C4CA4238A0B923820DCC509A6F75849B;2017-03-24
C81E728D9D4C2F636F067F89CC14862C;2017-04-23
C9F0F895FB98AB9159F51FD0297E236D;2017-05-23
D3D9446802A44259755D38E6D163E820;2017-06-22
E4DA3B7FBBCE2345D7772B0674A318D5;2017-07-22
ECCBC87E4B5CE2FE28308FD9F2A7BAF3;2017-08-21
*/

-- batch updating based on the value generated
update testgen set col2 = thedate from testgen, (select col1, add_days(current_date, (mult-1)*30) as thedate from 
 (select col1, row_number() over(order by col1) as mult from testgen)) as T where testgen.col1 = T.col1
 
select * from testgen order by COL1;

/* results
COL1                            ;COL2      
1679091C5A880FAF6FB5E6087EB1B2DC;2016-11-24
45C48CCE2E2D7FBDEA1AFC51C7C6AD26;2016-12-24
8F14E45FCEEA167A5A36DEDD4BEA2543;2017-01-23
A87FF679A2F3E71D9181A67B7542122C;2017-02-22
C4CA4238A0B923820DCC509A6F75849B;2017-03-24
C81E728D9D4C2F636F067F89CC14862C;2017-04-23
C9F0F895FB98AB9159F51FD0297E236D;2017-05-23
D3D9446802A44259755D38E6D163E820;2017-06-22
E4DA3B7FBBCE2345D7772B0674A318D5;2017-07-22
ECCBC87E4B5CE2FE28308FD9F2A7BAF3;2017-08-21
*/

Of course, here I used a specific ordering to generate the row numbers (order by col1 asc). If that fits your scenario or not its entirely up to you to check and adapt to your scenario (if this approach fits in of course). Btw: no clue about execution times when the table is considerably big so I cannot comment on that.

For the auto update thing my first guess would be to set the logic on the application by getting the last date available on the table and adding 30 to that. At the same time, you might have an issue with optimistic locks here...

BRs,

Lucas de Oliveira

[former_member184111 · Accepted solution]

Hi Lucas,

Thanks, your code clarifies a lot.

This is a column table with 5 field, last one is date for e.g. delivery date, this should contain dates as mentioned above.

Based on the value of delivery date I need to add one more date column and populate it with last day of the month for e.g. in your sample COL2 value is '2016-11-24' next date field should contain '2016-11-30'.

I tried using LAST_DAY but it throws a syntax error of unsupported function.

Thank you,

Anubhav

[lucas_oliveira · Accepted solution]

Last day of the month and +30 days are 100% different my friend. If you need the first then you got it already (LAST_DAY).

You seem to add another question/requirement here so please be as specific as you can and provide table definition, sample data and desired result.

Most importantly: explain what you tried to do that didn't work (just saying that LAST_DAY throws an unsupported function error does not mean much if we can't see how're you using and which HANA version you're using). So again: be clear and provide details.

BRs,

Lucas de Oliveira