Using Data from a Table or View

A new (fictional) European Union directive requires that the information about which vehicle is registered to which owner has to be stored in a central transnational database.

The vehicle identification number (CarID) is unique across the EU, but not the Owner ID.

You can specify multiple columns in the projection list:

12
SELECT PNr, Name, Salary
    FROM Official;

The sequence of the columns in the projection list is relevant:

12
SELECT PNr, Name 
    FROM Official;

12
SELECT Name, PNr
    FROM Official;

An asterisk (*) in the projection list represents all columns:

12
SELECT * 
    FROM Official;

The following options are also supported:

• You can include a single column in the projection list.

• You can include the same column in the projection list repeatedly.

• You can combine using the asterisk and explicitly named columns.

• You can use the asterisk repeatedly in the projection list.

We do not recommend using the last three options because each makes the result set difficult to understand and work with.

You can rename result columns:

12
SELECT PNr AS PersNumber, Salary AS "Salary Group"
    FROM Official;

Column names not enclosed in quotation marks are converted to upper case. A column name has to be enclosed in quotation marks if one of the following applies:

• The column name should not be converted to upper case but should be kept exactly as spelled in your statement.

• The column name contains a space, a punctuation mark, a colon, or any other special character.

The keyword AS is optional.

You can use column names of the table selected from to rename other columns of the result set, for example, to exchange column names.

You can use existing column names for (re)naming of result columns:

12
SELECT PNr Salary, Salary PNr 
    FROM Official;

You can generate additional "literal" result columns.

The additional result column can have a string or a numeric type:

12
SELECT 'The working week of', Name, 'amounts to', 40, 'hours.'
    FROM Official;

The projection list can even comprise one or more literal values only. Note that the result set still contains as many records, all comprising the same literal values only, as the table select from contains. If your intent is to select a single literal record, you can use the built-in table DUMMY.

12
SELECT 'Good Morning!'
  FROM DUMMY;

The DUMMY table contains one column and one row:

12
SELECT *
    FROM DUMMY;

The projection list can have computed columns.

If a NULL value is used in an arithmetic operation, the result is also a NULL value:

12
SELECT Name, Overtime * 60
    FROM Official;

You can explicitly name computed result columns:

12
SELECT Name, Overtime * 60 AS Overminutes
    FROM Official;

SAP HANA supports using functions to calculate columns of the projection list. Different functions are supplied for different data types of the input columns and values, and users may create their own functions.

When were vehicle owners first allowed to drive a car in Germany?

12
SELECT Name, ADD_YEARS(Birthday, 18) AS "18th Birthday"
    FROM Owner;

Function calls can be nested.

On which day is the owner’s 18th birthday?

12345678
SELECT Name,
       DAYNAME
       ( ADD_YEARS
          (Birthday,
            ROUND(ABS(-18.2))
          )
       ) AS Weekday
  FROM Owner;

Currently there are 248 built-in functions available, grouped into 12 categories (for example, aggregate functions, array functions, datatype functions). Functions can be very specific, such as ADD_WORKDAYS():

123456789
ADD_WORKDAYS(
	<factory_calendar_id>,
	<start_date>, 
   	<workdays>
	
	[, <source_schema> 
   	[, <table_name_suffix>, 
   	[, <client> ] ] ] 
		)

SPS07 and later allow the usage of factory and holiday calendar for ADD_WORKDAYS() and WORKDAYS_BETWEEN() functions. The parameters <table_name_suffix> and <client> have been added to these functions. A prerequisite for this is that the day collections are provided in TFACS or structure-like tables.

Built-in function ADD_WORKDAYS TFACS Example

-- Germany Bavaria 2023

1234567891011121314
insert into TFACS values ('BY', '2023’,
'0111100011111001111100111110011’, 	-- january	
'1110011111001111100111110011’,  	-- february
'1110011111001111100111110011111’, 	-- march
'001111000011110011111001111100’, 	-- april
'0111100111110011101001111100011’, 	-- may
'110011101001111100111110011111’, 	-- june
'0011111001111100111110011111001’, 	-- july
'1111001111100101110011111001111’, 	-- august
'100111110011111001111100111110’, 	-- september
'0101110011111001111100111110011’, 	-- october
'011001111100111110011111001111’, 	-- november
'1001111100111110011111000011100’  	-- december
, ' ', ' ', ' '	, ' ');

What calendar day was the first workday of the year 2023 in Bavaria?

123
SELECT ADD_WORKDAYS
	('BY', '20230101', 5 ) AS "Workday"
    FROM DUMMY;

…and in North-Rhine Westphalia?

123
SELECT ADD_WORKDAYS
	('NRW', '20230101', 5) AS "Workday"
    FROM DUMMY;

SAP HANA supports a rich list of built-in function. The following is only a minor selection.

See the SAP HANA SQL reference available on the SAP Help Portal for the full list of supported built-in functions. http://help.sap.com/hana_platform#section6

You can also create scalar user-defined functions and use them in the same way as built-in functions.

The projection list can contain columns that are based on a CASE expression.

These columns can be named explicitly:

1234567
SELECT *,
       CASE 
         WHEN HP < 120 THEN 'low'
         WHEN HP >= 120 AND HP < 180 THEN 'medium'
         ELSE 'high'
       END AS Rating
    FROM Car;

If a CASE expression does not correspond to any of the listed cases, a NULL value is returned:

12345678910
SELECT CarID, 
       CASE Color
         WHEN 'red'    THEN 'FF0000'
         WHEN 'green'  THEN '00FF00'
         WHEN 'yellow' THEN 'FFFF00'
         WHEN 'blue'   THEN '0000FF'
         WHEN 'white'  THEN 'FFFFFF'
         WHEN 'black'  THEN '000000'
       END AS Color
    FROM Car;

Both the THEN and the ELSE branch can contain references to table columns:

123456789101112
SELECT CarID, 
	CASE    
           WHEN PlateNumber IS NOT NULL THEN PlateNumber
	   ELSE 'The Car is not registered!'
	END AS PlateNumber,
	CASE Brand
	   WHEN 'Mercedes' THEN 'Mercedes-Benz'
	   WHEN 'VW' THEN 'Volkswagen'
	   ELSE Brand
	END AS Brand, 
	Color, HP  
    FROM Car;

A table with an enforced primary key does not contain duplicates.

12
SELECT * 
    FROM Car;

If you project a table and don’t include the full primary key, duplicate entries can result.

Duplicates can occur when a key column is not included in the projection list:

12
SELECT Brand
    FROM Car;

In most cases, this is not intended or practical, as there is no way to distinguish the duplicate entries from each other in the result set. This is why duplicate entries can be removed explicitly using the keyword DISTINCT. Note that duplicates are not removed by default to speed up data retrieval.

The keyword DISTINCT ensures that the result table contains no duplicates:

12
SELECT DISTINCT Brand
    FROM Car;

NULL values ​​are treated in duplicate elimination as normal values.

The result table contains (at most) one row that consists entirely of NULL values:

12
SELECT DISTINCT Overtime
    FROM Official;

If a projection list contains multiple columns, DISTINCT always refers to the combination of all these columns:

12
SELECT DISTINCT Brand, Color
    FROM Car;

In principle, you could always include the DISTINCT keyword in the SELECT statement to be sure you never get duplicates. This is because DISTINCT is compatible with using an asterisk in the projection list. Note, however, that using DISTINCT comes at a small performance penalty.

You can check for the presence of NULL values in the WHERE clause:

123
SELECT CarID, Brand, Color
    FROM Car
WHERE PlateNumber IS NULL;

Likewise, you can check for the absence of NULL values in the WHERE clause:

123
SELECT CarID, Brand, Color
    FROM Car
WHERE PlateNumber IS NOT NULL;

You can check if values are included in a value list.

123
SELECT Brand, Color
    FROM Car
WHERE Color IN ('red', 'blue', 'orange');

You can check if values are included in an interval.

123
SELECT PlateNumber, Brand, Color, HP
    FROM Car
WHERE HP BETWEEN 140 AND 170; 

You can use search patterns in the WHERE clause.

123
SELECT PlateNumber, Brand, Color, HP
    FROM Car
WHERE PlateNumber LIKE '%MM%';

Wildcard searching is done with the LIKE predicate, not the equals (=) predicate. The wildcard character % (percentage sign) represents any string containing zero, one, or multiple characters. The wildcard character _ (underscore) represents any single character.

If you want to search for the percentage sign (%) or underscore (_) itself, you have to place an ESCAPE character in front. You can choose the ESCAPE character (with some restrictions).

• The license plate started with "HD" for Heidelberg.
• I'm sure I saw an "M".
• I clearly remember the digits "2" and "6".
• The "2" was definitely left of the "6".
• Between the "2" and the "6" was exactly one digit.
• It was neither Skoda, nor a VW.
• The car was blue or green.

123456
SELECT *
    FROM Car
WHERE PlateNumber LIKE 'HD-%M% %2_6%'
      AND Brand <> 'Skoda' 
      AND Brand <> 'VW'
      AND (Color = 'blue' OR Color = 'green');

The operators have the precedence indicated by the table below:

Both the EXISTS keyword as well as the IN keyword are predicate keywords. This means that in combination with other keywords, the developer is able to create an expression to determine whether something is TRUE, FALSE, or UNKNOWN.

• EXISTS allows the developer to evaluate if a result set has any entries.
• IN allows the developer to evaluate if a given value matches one of a enumerated set of values.

In the case of an expression using EXISTS, the expression will return TRUE if the query contained in the expression returns a minimum of one record (in other words, not an empty result set). Otherwise, the expression will return FALSE (in other words, an empty result set). EXISTS can be used in IN and WHILE expressions (to be discussed later).

In the case of an expression containing an IN, the expression will return TRUE if the value on the left side of the IN matches at least one of a defined list of values on the right side of the IN. Otherwise, the expression will return FALSE. The value on the left side of the IN can be the result of a subquery.

In addition, a tuple (multiple values to match) can be used, that is, (city, country) IN ((‘Los Angeles’,’UnitedStates’),(‘London’,’England’));). IN can be used in expressions formulated with conditional logical such as IF and WHILE (to be discussed later) as well as WHERE clauses.

The result table can be sorted by a specific column:

123
SELECT Brand, Color
    FROM Car
ORDER BY Brand;

A descending sorting is possible:

123
SELECT Brand, Color
    FROM Car
ORDER BY Brand DESC;

Although ascending order is assumed, you may use the optional keyword ASC to make sort order explicitly clear:

123
SELECT Brand, Color
    FROM Car
ORDER BY Brand ASC;

The sorting can be applied using a column that does not appear in the projection list:

123
SELECT Brand, Color
    FROM Car
ORDER BY PlateNumber;

You can sort using a combination of columns:

123
SELECT Brand, Color
    FROM Car
ORDER BY Brand ASC, Color DESC;

Instead of the column names in the ORDER BY clause, the column numbers (based on the projection list) can be used:

123
SELECT Brand, Color
    FROM Car
ORDER BY 1 ASC, 2 DESC;

If result columns are named explicitly, you can refer to the new name for sorting:

123
SELECT Brand AS Manufacturer, Color
    FROM Car
ORDER BY Manufacturer ASC, Color DESC;

You can sort based on calculated values.

Sorting criteria: How much is the power below 200 kW?

123
SELECT CarID, Brand, HP
    FROM Car
ORDER BY 200 - HP / 1.36 ASC;

You can also use functions in the expression in the ORDER BY clause.

You can limit the number of rows to be included in the query result to a maximum number.

What are the ten most powerful vehicles (based on horsepower)?

123
 SELECT TOP 10 CarID, Brand, Color, HP
     FROM Car
 ORDER BY 4 DESC;

You can combine the TOP N clause with the keyword DISTINCT.

What are the seven highest HP values?

123
SELECT TOP 7 DISTINCT HP
    FROM Car
ORDER BY 1 DESC;

No error is thrown if you request more rows than are available. The result set will not be filled with additional, artificial rows.

570 different colors are requested:

12
SELECT TOP 570 DISTINCT Color
    FROM Car;

It is possible to request zero result rows. In this case, the result set does not contain any rows.

Zero colors are requested:

12
SELECT TOP 0 Color
    FROM Car;

The TOP N clause allows you to limit the result set to a specified maximum number of rows, but it does not allow you to retrieve the "next" N rows. To browse through a result in a page-by-page way, you can use the LIMIT and OFFSET clauses.

You can use the LIMIT clause as an alternative to the TOP N clause. The LIMIT clause comes at the very end of the SELECT statement.

What are the five most powerful vehicles (based on horsepower)?

1234
SELECT *
    FROM Car
ORDER BY HP DESC
LIMIT 5;

The LIMIT clause can be combined with the OFFSET clause to skip records. This allows you to read result sets page by page across multiple queries.

What are the next five most powerful vehicles (based on horsepower)?

12345
SELECT *
    FROM Car
ORDER BY HP DESC
LIMIT 5
OFFSET 5;