Skip to Content

HANA Column table add column of type DATE with dynamic values

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

Add comment
10|10000 characters needed characters exceeded

  • 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.)

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Nov 25, 2016 at 01:35 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Anubhav Jain

      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