Bizoneness: Common SQL and HANA types, Operators, and Functions

Bizoneness: Common SQL and HANA types, operators, and Functions

#Bizoneness #HANA

Two weeks ago, I wrote the first of several articles on HANA. In this week's newsletter, I'm going to expand that to common data types in HANA, as well as Microsoft SQL Server, and some of the functions you can use to work with that data.

Both are the platforms for SAP Business One. A survey of types can be helpful for those users with a HANA or SQL system or consultants with clients using both. For more information on SQL Server, check out my SAP Business One Reporting and Customization course in the LinkedIn Learning library. I'll be sticking to the features necessary for reporting in HANA and SQL and will not cover data manipulation features of create, update, and delete.

Data Types: A history

Let's look back to the history of computing to understand the different types of data.

Boolean

Virtually all computers today have a Boolean data type as their root. Indeed, boolean logic is wired into their integrated circuits. There are only two values in Boolean data: true and false. Logic takes one or two boolean values and changes them to another value. We call this an operation, and the symbol for doing an operation is an operator. For example, the operator AND might take a value of true AND true to produce true , but true AND false and produce false.

Numbers

Combining several boolean values representing 1 for true and 0 for false in a sequence can produce numbers. Integers are numbers without a fractional part, such as 5, 42, and 255. Unsigned Integers are numbers that use that sequence of boolean values, referred to as bits, to make a positive number based on the powers of two. A four-bit sequence like 0101 could be made into the number 5 like this:

 0 * 2^3 = 0
 1 * 2^2 = 4
 0 * 2^1 = 0 
 1 * 2^0 = 1

0+4+0+1 = 5        

A four-bit sequence could represent the numbers 0 through 15. an eight-bit sequence could represent 0 through 255.

To get both positive and negative numbers, referred to as a signed integer, some bits are sacrificed for the sign. In modern systems running 64 bits, this is rarely a problem. All these integers are fast to compute, so math operations on them tend to be the quickest.

Slightly slower computation happens when fractional amounts come into play. Fixed decimals fix the decimal place and are rarely used as rounding errors can accumulate in larger calculations. Most systems use floating point number systems, often the 32-Bit Single precision and 64-bit double-precision number representations.

All numbers use the familiar operations from math classes of our youth: Addition, subtraction, multiplication, division, and remainder(modulo). All but unsigned integers will also use negation.

There is also binary for the raw representation of a number. Binary is challenging to read and takes up a lot of space to read a simple number. For example, Here's a number in Binary

 1010 0101 0111 1111         

Two systems represent this number. Of course, we can represent this as the unsigned integer 42,367 in decimal. For uses where the binary has more meaning than a decimal number, the hexadecimal system shortens the number for easier reading. This base-16 system uses digits of four-bit numbers like this:

Thus, we can represent the binary number as 0xA58F, where 0x tells you it is a hexadecimal number. Sometimes, you'll see a six-digit hexadecimal number such as #ff803f using a hash symbol as the hexadecimal indicator, which is a color code. 0xff803f is an orange-yellow, where the first two digits represent the amount of red, the second two the amount of green, and the last two the amount of blue.

Characters and Strings

I bring up hexadecimal because there is another representation with numbers, often in hexadecimal: written characters. Using only 7 bits to represent a character in the earliest computers, this system contained 95 printable and 33 control characters. This system, known as ASCII, was common for decades but presented two problems: It was only usable for Latin alphabet languages and did not have special symbols for technical articles.

The folks working on non-latin character language representation at Xerox PARC and Apple in the 1980s started a group that would change that. As graphic interfaces expanded, this group's influence would grow to an international consortium known as Unicode, with standards agencies following suit with their coding system. Unicode was a far more sophisticated system for handling not only characters from multiple languages but also diacritical marks, ancient languages, and emoji.

Some Strings use an extended ASCII 8-bit set, often referred to as LATIN-1, and some use Unicode. I'll use string to refer to both.

Date and Time

There are many ways to represent dates and times. Two of the most common are as intervals and as components. Date components representation was an early and easy way to store dates. The system would store the day, month, and year as integers. However, it has some substantial drawbacks. First, it is difficult to localize for different time representations, as there are many formats for dates internationally. Secondly, if done improperly, it can cause problems with time, most notably the Y2K Problem of the 1990s, where a two-digit year could cause calculation problems. The second representation gets around these problems with a time interval, the number of seconds from a standard reference date. Localizations are mere additions and subtractions from this date and time. There are standards for the time interval and accurate clocks to measure time, which updates the local clocks in systems.

While a long introduction, this will help you understand what both Microsoft and SAP did with these data types in their database systems and query languages.

Using Types

Numbers

Numbers used in database systems are fixed point, double precision floating point, or signed integers. SQL and HANA have other types, but these stand out for practical use.

Integers are very often primary keys in tables. DocEntry in any table is often an integer. Enumerations, codes used to represent something else, are often integers, such as the ObjType column.

Floating point numbers are numbers such as currency, quantities, and measurements. SAP B1 Tables often use a fixed point number of nineteen digits with six digits behind the decimal point. Fixed point types are NUMERIC or DECIMAL in SQL and DECIMAL in HANA. In both SQL and HANA, floating point types include REAL, DOUBLE, AND FLOAT. FLOAT(53) is same a DOUBLE and FLOAT(24) is the same as the singe precision REAL. Many operations and functions in SQL and HANA will return floating-point results.

All numbers use basic mathematical operators such as

addition       40 + 2  
subtraction    44 - 2 
multiplication 6 * 7 
and division   84 / 2 
        

all with a result of 42.

One operator, modulus, has the operator % in SQL such as

1042 % 100 
        

but it is a function in HANA

MOD(1042,100)
        

again with a result of 42. Modulus is the remainder of a division operation, most often used with integers.

There are also the numeric operators for negation placed before a value, which changes from positive to negative and from negative to positive values.

-42 
        

There is also an order of operation with priorities set for different operators.

Fisr in parentheses.

(42)
        

Parentheses change the order of operation, making the expression inside the highest priority. With nested parentheses, the innermost has the highest priority.

After parentheses is negation, then multiplication and division, addition and subtraction, and then some operators we'll talk about later: concatenation, comparison, then the Boolean operators NOT, AND, and OR in that order.

Functions

While there are many numeric functions, for business users, there are a few functions to focus on

Rounding functions - While rounding can and should happen in formatting, on occasion, you'll want to round before that. Generally, this is used to round numbers to be integers. In both SQL and HANA

ROUND(3.14159) 
        

results in 3. However, if you want to round to a decimal place, you can include a position

ROUND(3.14159,2)
        

results in 3.14. ROUND defaults to rounding up so

ROUND(3.14559,2)
        

is 3.15. In Microsoft SQL, you can truncate by adding a 1 as the last parameter.

ROUND(3.14159,0,1)
        

is 3.

ROUND(3.14159,3,1)        

is 3.141.

In HANA for SAP B1, the digits only display two decimal places.

FLOOR - For rounding to integers, there is also the FLOOR function, which will always round to the lesser integer. This affects negative numbers as

FLOOR(3.14) 
FLOOR(3.17)
        

both return 3, but

FLOOR(-3.14)
FLOOR(-3.17)  
        

both equal -4.

ABS - when you want the number to be a positive number, ABS() will remove any negative numbers.

 ABS(-3)
        

and

 ABS(3) 
        

both return 3.

Strings

There are two types of strings available in both SQL and HANA. VARCHAR is a string of the 8-bit extended ASCII, known as Latin-1. NVARCHAR is a string of Unicode characters. You'll want NVARCHAR if you use a non-Latin script. If using Latin characters, use VARCHAR. As most SAP B1 tables are older than Unicode, VARCHAR is the type for string columns.

There are limitations for Unicode. While emojis are represented in the standard, you cannot use them in NVARCHAR. Also, the representation shown is dependent on your font choice. Not all fonts have all characters.

Operators

For operators, there is only one, though the symbol changes. In SQL, use a plus sign. 'My' + 'Pizza' resolves to 'MyPizza.' In HANA, the double pipe is used instead to avoid confusion with addition.

'My' || 'Pizza' 
        

resolves to 'MyPizza'.

Functions

CONCAT(string1,string2) - Both SQL and HANA also have a CONCAT function, which helps with self-documentation of your code.

CONCAT('My', 'Pizza') 
        

resolves to 'MyPizza'.

LENGTH(string) - it is often nice to know how many characters there are in a string, and LENGTH will tell you.

LENGTH('Pizza') for Example results in 5  
        

LEFT(string,integer) - Returns the string that starts on the left and returns the number of characters specified.

LEFT('Hello, Pizza',5)  
        

returns 'Hello'.

RIGHT(string,position) - returns the string that starts at the rightmost part of the string and returns the number of characters specified.

RIGHT('Hello,Pizza',5) 
        

returns 'Pizza'

SUBSTRING(string,position,length)* - returns the string that starts at position for the given length.

SUBSTRING('Hello, Pizza',5,4) 
        

returns' o, P'

UPPER(string)/UCASE(string) - Changes the string to all uppercase

UCASE('Shouting') 
        

becomes 'SHOUTING'.

INITCAP(string) - Changes the string to capitalize the first word in a string and puts everything else in lowercase.

 INITCAP('SAP B1 essential training') 
        

returns' Sap B1 Essential Training.'

I didnt Intend to add this to the functions, but RPAD in HANA and REPEAT in SQL made a nice title image, so I'll use it here. I wrote an entire Bizoneness on this, but these repeat characters, often used to pad a string to a certain width, but for a quick and dirty bar chart you can do this In HANA, where the 5000 is a scaling factor.

RPAD("","T0.DocTotal"/5000,NCHAR(0x2593))         

DateTime

Time has three major types. In SQL, you'll find DATE, TIME, and DATETIMEcombining the two. IN HANA, these are DATE, TIME, AND TIMESTAMPrespectively.

The current time and date - If you want the current date, use one of these functions. In SQL, GETDATE() and CURRENT_TIMESTAMP() will return the current date and time. In HANA, we'll see a pattern of a prefix, in this case, CURRENT_, and the type in functions. CURRENT_DATE() will return the date, CURRENT_TIME the time and CURRENT_TIMESTAMP both date and time. NOW() is a shortened version of CURRENT_TIMESTAMP

GETDATE() and NOW() are often nested in other date functions.

DATEADD(dateComponent,value,date) - For SQL, you can add date components with the DATEADD Function. Use a component specifier, a value, and the date to get a new date. For example, a year from now would be

DATEADD(year,1,GETDATE())
        

Common specifiers are year, month, day, and week. If you want a date in the past use a negative number.

DATEADD(month,-3,GETDATE())
        

It would be 3 months ago.

With HANA, Date-component adding has multiple Functions. ADD_DAYS will add days, ADD_MONTHS will add months and ADD_YEARS adds years. The parameters reverse from SQL, with the date and then the value to add. For example

ADD_YEARS(NOW(),1) 
        

is one year from now, and

ADD_MONTHS(NOW(),-3)
        

is three months ago.

DATEDIFF() - In SQL, this function gives the difference in the specified unit. It takes the date part specifier and the start and end dates. For example,

DATEDIFF(day,DATEADD(month,-3,GETDATE()), GETDATE()) 
        

returns the number of days between three months ago and today.

IN HANA, once again, you get individual functions of DAYS_BETWEEN, MONTHS_BETWEEN, YEARS_BETWEEN, etc.

You specify the start and end date for all of these. for example:

DAYS_BETWEEN(ADD_MONTHS(NOW(),-3),NOW()) 
        

again returns the number of days between three months ago and today.

Date components - if you need a date component, we'll follow a similar pattern. SQL uses a date component specified in the DATEPART function DATEPART(year,GETDATE()) gives today's year. In HANA, the component is the function DAYOFMONTH, YEAR,and MONTH

  YEAR(NOW())
        

gives today's year.

Bool

There is no Boolean type in SQL; only boolean results of operators and special functions known as Predicates. For recording Boolean types in SQL, we use a single character string of Y for true or N for false. HANA, on the other hand, has a boolean type.

There are two sets of operators for booleans: one that provides a boolean value by comparing other types and one that provides values by operating on Boolean types.

AS SQL does not have a boolean type to display, you'll have to place it in a CASE WHEN expression like this when you want to show the result: CASE WHEN <Boolean Expression> THEN 'Y' ELSE 'N'

Or, if you want to get sneaky with characters for filled and unfilled boxes

CASE WHEN <Boolean Expression> THEN NCHAR(0x25a0) ELSE NCHAR(0x25a1) END
        

Comparison Operators

The comparison operators work on various types depending on the operator.

The equals operator creates a true value when two items are the same.

1 = 1
        

is true, as is the string

'Pizza' = 'Pizza' 
        

while

 1 = 42
        

and

'Pepperoni' = 'Cheese'
        

is false.

The not equals operator creates a true value when the two items are different. There are two operators <> and != for not equals.

1 <> 1
        

is false. So is

'Pizza' != 'Pizza'
        

while

1 != 42
        

and

'Pepperoni' <> 'Cheese'
        

is true.

Less than and Greater than - the less than and greater than operators will change meaning depending on the type. For numbers, this will be the value of the number, so

1 > 5
        

is false,

1 < 5

        

is true,

42 > 5
        

is true and

42 < 5
        

is false.

Equal values are always false, so both of these are false:

1 > 1
1 < 1 
        

For strings, it is similar to case-insensitive alphabetical order, but the numbers have a lower value than the alphabet. these are true:

'A' < 'B'
'A'> '0B'
        

while

'A'> 'B'
'A' < '0B' 
        

are false.

Inclusive less than and greater than- there will be cases where the a = b case should be true in the comparison. Use the = and <= Operators for those cases. They work the same as above, but where

'Pizza'> 'Pizza'
5 <= 5
        

are both false

'Pizza'>= 'Pizza'
5 <= 5
        

are both true

Logical Operators

You may have several conditions you are testing to get a Boolean value. To combine those conditions, use the logical operators.

AND - if both conditions are true, the result is true, or in reverse, if any condition is false the result is false. For a HANA example,

"ORDR"."DocDate" = DAYS(Now()) AND "ORDR"."DocTotal" >= 10000
        

or its SQL equivalent

ORDR.DocDate = DAYS(GETDATE()) AND ORDR.DocTotal >= 10000
        

would be true for any order from today's day component that is over 10,000 in sales. Note there is a little bug in this query we'll come back to later.

OR - If both conditions are false, the result is false, or in reverse; if any condition is true, the result is true. I could write out

3 <= 5
        

as

3 < 5 OR 3  = 5
        

which would be true.

NOT - This operator changes true to false and false to true. Sometimes, this is good documentation. For example,

NOT OITM.onHand > OITM.isCommited   -- Not enough inventory
        

is true when our inventory is behind our orders. While we could write it another way,

OITM.onHand < OITM.isCommited   -- Not enough inventory
        

that NOT makes the condition clear.

Converting Between Types

One issue you will run into is converting between types. Some types, especially among numbers, will convert implicitly. Some types, such as anything requiring a string, may need help. For a HANA example, Consider

ADD_DAYS('12/25/15',6)
        

This will produce an error while

ADD_DAYS('2015-12-25',6)
        

does not. Implicit conversion only happens when the date is the default format of YYYY-MM-DD. With DATETIME, explicit conversion is necessary for other reasons.

Earlier we had a snippet

 OINV.DocDate = DAYS(GETDATE()) AND OINV.DocTotal >= 10000
        

If used in this SQL Query to get yeaterday's invoices:

SELECT
   T0.DocNum,
   T0.DocTotal,
FROM 
   OINV T0
WHERE 
 TO.DocDate = DAYS(GETDATE()) - 1 AND T0.DocTotal >= 10000
        

would print every invoice for any month and year with the day of yeaterday. If using date components, I'd need a longer WHERE clause to filter for yesterday.

SELECT
   T0.DocNum,
   T0.DocTotal,
FROM 
   OINV T0
WHERE 
 DAY(TO.DocDate) = DAY(GETDATE()) - 1
 AND MONTH(T0.DocDate) = MONTH(GETDATE())
 AND YEAR(T0.DocDate) = YEAR(GETDATE)
 AND T0.DocTotal >= 10000
        

Consider this query, subtracting one day from GETDATE:

SELECT 
 T0.DocNum,
 T0.DocTotal,
FROM 
   OINV T0
WHERE 
   T0.DocDate = DATEADD(day,-1,GETDATE())
    AND T0.DocTotal >= 10000
        

Or in HANA

SELECT 
   T0. "DocNum", 
   T0." DocTotal",
FROM 
   OINV T0 
WHERE 
   T0." DocDate"  =  ADD_DAYS(NOW(),-1)
    AND T0." DocTotal" >= 10000        

This query will never return any results. GETDATE() and NOW() are of type DATETIME and TIMESTAMP respectively, but Most of the columns in SAP B1 are DATE. Comparing the two, the DATE implicitly converts to DATETIME/TIMESTAMP, but with a time of Midnight at 00:00:00.0000, and you never made any entries then and are very unlikely to do so. The WHERE clause is always false.

For explicit conversion in SQL, you use CAST(). For example, to change that date, I'd use

CAST(GETDATE() AS DATE)
        

That would change my query to one that works as

SELECT 
 T0.DocNum,
 T0.DocTotal,
FROM 
   OINV T0
WHERE 
   T0.DocDate = DateAdd(day,-1,CAST(GETDATE() AS DATE))
    AND T0.DocTotal >= 10000        

For compatibility, there is a CAST() in HANA, but it is a wrapper for the TO_functions, such as

TO_DATE(<date> [, <format>]) 
TO_TIMESTAMP(<date> [, <format>])
TO_TIME(<time> [, <format>])
TO_INTEGER(<value>)
TO_REAL(<value>)
TO_DOUBLE(<value>)
TO_VARCHAR(<value> [, <format>])
TO_NVARCHAR(<value> [, <format>])
        

The HANA corrected version of the query casting NOW() to a date is

SELECT 
   T0. "DocNum", 
   T0." DocTotal",
   ADD_DAYS(NOW(),-1) 
FROM 
   OINV T0 
WHERE 
   T0." DocDate"  =  ADD_DAYS(TO_DATE(NOW()),-1)
    AND T0." DocTotal">= 10000        

You'll notice many of the conversions above have an optional format. I'll discuss formats in another newsletter in this series for both HANA and SQL. However, I'll leave you with a sample to the problem of adding a literal date

ADD_DAYS(TO_DATE('12/25/15', 'MM/DD/YYY'),6)
        

We've covered a lot of ground, with most of the basics of type functions and operators available in Microsoft SQL and HANA. My goal here was not to list for you everything available; that's what documentation is for: you can find most of it online. Instead, I wanted to give you the most common flavors available to make the most delightful reporting dish possible with the tools in SAP Business One.

In two weeks, I'll cover two more types of functions: Predicates and Aggregate functions. Next week, with Luca ERP, we'll explore the common components of an ERP user Interface.