Please help me to understand in details why we are using Expression like this:
I want to learn the detailed of ths Sysntax so that I can use it in different table for creating Calculated Attribute.
My Second question:
In SQL if I write in creating Table for a field amount with syntax as
SALES_AMOUNT Decimal double
SALES_AMOUNT Decimal (2)
What is the difference between them.
midstr() according to the SQL script guide => return a part of the string starting at arg2, arg3 bytes long. arg2 is counted from 1 (not 0).
So in your e.g.. it returns just the "Year" part from your date.
Your create table statement syntax seems to be wrong. Double data type is basically your double-precision floating point number that can store a much bigger value range.
Let me try to answer the questions:
Use of expression "midstr(string("ERDAT"),strlen(string("ERDAT"))-9,4)" is to derive the YEAR for the date. It will convert the value of the Movement date to string and then using string manipulation functions like midstr and strlen it will compute the values as follows:
1) strlen(string(date) -9 = will return the (length of data value) - 9
2) midstr (<string value>, start position, no of characters) will end up selecting the first 4 characters for the string value.
Hence this expression should return the first 4 characters from the ERDAT value which is the YEAR.
Coming to the second question:
I am not sure if there is DECIMAL DOULBE option. There is Decimal (precision, scale) and Double.
With Decimal(2) there column can contain only 2 digit values, where as double column can contain much larger value.
For the details on DECIMAL and DOUBLE, I'd suggest you refer to the HANA SQL Guide at http://help.sap.com/hana/hana_sql_en.pdf
Hope this helps
null and not null as the name suggests is to simply specify whether that particular column accepts null values or not. By default, every column accepts nulls values unless you explicitly specifyl 2not null".
That aside, your statements:
a. SALES_AMOUNT Double null, will create the column as DOUBLE data type.
b. SALES_AMOUNT Decimal not Null will create the column as a floating decimal type
c. SALES_AMOUNT Decimal (2) will create the column as a fixed decimal type.
To understand the difference between decimal, float and double data types - just google it, it's no different in the HANA database.
As Ravi mentioned above, you could refer to the SQL script guide in http://help.sap.com/hana_appliance/
One more point to add, please use the appropriate data type for your objects based on the actual data. Having a data type larger than the required data might result into more memory being consumed.
Hence it is advisable to use the appropriate data type which can fulfill the requirements without un-necessary memory consumption.