For a while now I am working as a Solution Architect for the SAP Custom Development organisation.

This is the team you’d call when you want custom built solutions that perfectly fit into your standard solution landscape.

My job there is not coding or implementing systems, but rather designing Solutions and turning those designs into commercial offers.

Obviously it seems like a good idea to every now and then go and check that what I remember of doing “real work” is actually still true. and so I try to keep up with our tools and platforms as well as anyone.

While trying out different stuff for something that might become a blog post later on, I came across some things I considered noteworthy, “interesting” or otherwise out of the expected.

All this is based on SAP HANA rev. 101 and SAP HANA Studio 2.1.6.

Change of default statement preparation in SAP HANA Studio

In Issue: MDX Query not executing in Hana Studio 2.0.8 Version and Re: Regarding “Generate Time Data” Option in HANA folks complained about the fact that MDX statements suddenly started to return error messages even though the worked in older SAP HANA Studio versions.

The error message was:

SAP DBTech JDBC: [2]: general error: MDX query doesn’t support prepared statement

I first proposed to circumvent this by creating a little wrapper procedure but this is inconvenient.

Shady Shen provided the correct hint: un-check the check box “Prepare statements before execution”.

16-08-2015 14-25-02.png

This seem to have been set to active by default in the more recent versions of SAP HANA Studio. Once again a change that doesn’t occur in any documentation or what’s-new note… 🙁

At least it’s not necessary to close/re-open the SQL editor to get this changed setting active.

No DEFAULT value specification in INSERT/UPDATE/UPSERT possible

Let’s say you were diligent with your table column specification and not only provided name and data type but also a NOT NULL constraint and a DEFAULT value clause.

Something as extravagant as this:


create table test (id int not null default 0);


Now you want to make use of this and by following SQL standard you use the DEFAULT keyword to specify that you want to have the pre-defined default value:


insert into test (id) values (default);


SAP DBTech JDBC: [260]: invalid column name: DEFAULT: line 1 col 31 (at pos 30)

This is not to say SAP HANA doesn’t support default values.

If you want to use default values, you mustn’t include them in the DML statement at all.

So the following works without problems:


alter table test add (id2 int not null default 1);
insert into test (id) values (1);
select * from test;


ID ID2
1 1

Would be lovely to find such nuances in the documentation.

Limits of date operations

When working with date data types it’s real easy to forget about the limitations of this data type.

One example could be that non-existing dates like the February 30 or dates between 05.10.1582 and 14.10.1582 are rejected or mapped to the correct dates.

Another limitation I actually ran into while generating random garbage data for a date column is the smallest/largest date.

This time the documentation is clear about this: The range of the date value is between 0001-01-01 and 9999-12-31.

Usually I wouldn’t think much about this, but when using expressions like the following to create random dates, I hit an error:


select add_days (date'01.01.1980', rand()*10000000) from dummy;


[303]: invalid DATE, TIME or TIMESTAMP value: internal error in AddDays() at function add_days()

In my example the 10000000 was in fact a sequence number in a rather large data set.

So, the quick fix would be to set an upper limit for the value that is fed into add_days().


select add_days (date'01.01.1980', least(rand()*10000000, 3560) from dummy;


The least() function comes in real handy here at it makes sure that the largest possible value returned will be 3560 (roughly 10 years worth of days).

Together with its sister the greatest() function it’s easy to create upper and lower limits for generated values.

But, what if we don’t want to arbitrarily set the limit below what would be possible with this data type?

Easy too:


select days_between (date'31.12.9999', current_date) from dummy;


DAYS_BETWEEN(31.12.9999,CURRENT_DATE)

-2916233                         

Gives you the number of days between today and the end of times – huh… gotta hurry!

Admittedly this example is super artificial and has no connection to real-life dates.

However, it shows that there are limitations to the date data type in SQL that it’s not too difficult to step into them.

There are of course other examples where a richer semantic for this data type would be useful.

Being able to have a MIN_DATE or MAX_DATE entry that would explicitly show up like this would be

Wandering off…

The date topic side-tracked me at this point and I looked into some other points, too:

I’ve shown the “generator query”-trick (cross join of objects system table to generate millions of rows) before.

This is of course not the only way to create a lot of rows.

Another option is to write SQL Script and this was the first time I used the new SPS 10 capability to run SQL script directly in the SQL console without the need for a wrapper procedure:


create table manydates (dday date);
do
begin
    declare i bigint;
    for i in 0 .. 3652060 do
        insert into manydates (dday) values (add_days(date'01.01.0001', :i) );
        if (mod(i, 10000) = 0 )
        then
            commit;
        end if;
   
    end for;
end


This little gem runs for a while and fills a table with all possible date values.

Statement ‘do begin declare i bigint; for i in 0 .. 3652060 do insert into manydates (dday) values …’

successfully executed in 9:37.694 minutes  (server processing time: 9:37.681 minutes) – Rows Affected: 0

Now, about we claimed that no “no-dates” entries would be created.

And checking for duplicates via


select count(distinct dday) from manydates;


and


select dday, count(*) from manydates
group by dday having count(*) >1;

show that there aren’t any.

But how’s this?

We are looking at how many years worth of days here? 9999 exactly.

So, leaving out leap years we should end up with 365 * 9999 = 3.649.635 separate days.

That number is 2.426 days short of what we actually find in our table.

Alright, so then just taking 366 days per year will help us, maybe?

Nearly: 3.659.634 days result here, which is 7.573 days too many.

Maybe trying to account just for the leap years separately can help here.

Leap years occur roughly every four years, so for 9999/4 = 2.499 years we should add one day.

That gets us to 3.652.134, which is just 73 days off from the actual number of days in this dates table.

We can even check this in SQL again:


select count(distinct year(dday))  from manydates
where mod(to_integer(year(dday)), 4) = 0;

COUNT(DISTINCT YEAR(DDAY) )

2499                   

But what leap years didn’t we cover with our simple arithmetic?


(select year(dday), count(dday)
    from manydates
    where  mod(to_integer(year(dday)), 4) = 0
    group by year(dday))
     
EXCEPT
(select year(dday),  count(dday)
    from manydates
    group by year(dday) having count(dday) > 365
)
order by year(dday);

Fetched 63 row(s) in 21 ms 617 µs

YEAR(DDAY) COUNT(DDAY)
1700    365     
1800    365     
1900    365     
2100    365     
2200    365     
2300    365     
2500    365     
2600    365     
2700    365     
2900    365     
3000    365     
3100    365     
3300    365     
3400    365     
3500    365     
3700    365     
3800    365     
3900    365     
4100    365     
4200    365     
4300    365     
4500    365     
4600    365     
4700    365     
4900    365     
5000    365     
5100    365     
5300    365     
5400    365     
5500    365     
5700    365     
5800    365     
5900    365     
6100    365     
6200    365     
6300    365     
6500    365     
6600    365     
6700    365     
6900    365     
7000    365     
7100    365     
7300    365     
7400    365     
7500    365     
7700    365     
7800    365     
7900    365     
8100    365     
8200    365     
8300    365     
8500    365     
8600    365     
8700    365     
8900    365     
9000    365     
9100    365     
9300    365     
9400    365     
9500    365     
9700    365     
9800    365     
9900    365     

If you followed up to here, you’ll notice, that we were looking for 73 “missing days”.

Comparing the set of years where the number of the year is divisible by 4 without remainder and the set of years where the number of days is larger than 365 revealed 63 days.

So, where are the 10 last days here?

Remember that odd reference on the Gregorian Calendar reformation?

That took ten days away in 1582!


select count(distinct dday) from manydates
where year(dday) = 1582;

COUNT(DISTINCT DDAY)

355

So far, so good.

We found all the “missing records”, but why aren’t these counted as leap years anyhow?

That’s because, I used a too simple rule here.

Leap Year nearly every 4 years explains that a leap year is only counted as such if

  • The year is evenly divisible by 4;
  • If the year can be evenly divided by 100, it is NOT a leap year, unless;
  • The year is also evenly divisible by 400. Then it is a leap year.

Let’s run that against our dates table:


select year(dday) yr, count(dday) day_cnt
from manydates
where
       mod(to_integer(year(dday)), 4) = 0
       and
       not (mod(to_integer(year(dday)), 100) = 0)
      or
        mod(to_integer(year(dday)), 4) = 0
        and
        mod(to_integer(year(dday)), 400) = 0
group by year(dday)
;

Fetched 2424 row(s) in 123 ms 147 µs (server processing time: 1 ms 665 µs)

Alright – the rule gives us 12 years less than what is in our dates table.

Which ones?


select mod_y.yr mod_yr, long_y.yr long_yr
from
  (select year(dday) yr, count(dday) day_cnt
  from manydates
  where
        mod(to_integer(year(dday)), 4) = 0
        and
        not (mod(to_integer(year(dday)), 100) = 0)
       or
         mod(to_integer(year(dday)), 4) = 0
         and
         mod(to_integer(year(dday)), 400) = 0
        group by year(dday)
  ) mod_y
     
FULL OUTER JOIN
  (select year(dday) yr,  count(dday) day_cnt
     from manydates
     group by year(dday) having count(dday) > 365
  ) long_y
  on mod_y.yr = long_y.yr
WHERE
   mod_y.yr is null or long_y.yr is null;

MOD_YR LONG_YR
100 
500 
900 
1300
200 
600 
1000
1400
300 
700 
1100
1500

Can you spot the reason?

All of those records violate the rule of not being divisible by 100 except when they are divisible by 400.

Why had those years been generated with one day too many by our add_days() function?

The Gregorian Calendar Reformation clearly did change history but it didn’t change the past 🙂

Before this regulation got active, the leap years didn’t follow those rules (which called for the Reformation in the first place).

And as we see there are no false leap years after year 1500.

That’s it for today.

Funny things come up when one spends some hours on a rainy Sunday afternoon fiddling with SAP HANA SQL.

There you go; now you know!

– Lars

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply