Skip to content

HANA SQL

It is almost guaranteed that new projects we get will be HANA-based SAP. While SAP is still supporting the T-SQL version most of our customers use, all new licenses and set ups are heavily incentivized to be HANA.

Unfortunately, HANA SQL is just different enough from T-SQL that it does require some thought and preparation on our part. The rules that govern how HANA SQL works in comparison are straightforward, but can be labor-intensive if you're converting old queries. Below are some observations.

Overall Format

  • Table and column names are case sensitive
  • Column names must be surrounded by double quotes. E.g. OINV."DocEntry"
  • Column aliases must be surrounded by double quotes. E.g. OINV."DocEntry" AS "Document"
  • All other strings must be single quotes. E.g. AND RDR1."State" = 'OR'
  • DISTINCT does not work on union selects. Unions must be wrapped in a separate select with its own DISTINCT
  • String concatenation can be done with the || operator

Functions, Formatting, and Display

ISNULL

Use COALESCE instead of ISNULL, which doesn't exist.

  • COALESCE(TO_VARCHAR(ORDR."Series"), '') AS "Series"

Dates

  • COALESCE(TO_VARCHAR(RDR1."TaxDate", 'YYYY-MM-DD'), '') AS "DocumentDate"
  • BETWEEN TO_DATE(RDR1."TaxDate") AND TO_DATE(NOW()))

Currency

  • '$' || TO_VARCHAR(TO_DECIMAL(ORDR."DocTotal", 2, 2))

Automat Queries

Because HANA SQL insists on so many double quotes, as you can imagine, putting these queries into traditional Automat JSON gets messy. Instead of that, consider splitting the query into a .QueryConf and .sql pair. The .QueryConf is still JSON, containing the ID, and StringParams or SqlParams arrays. The .sql file contains, you guessed it, straight SQL, no formatting required. You can still use string-replaced variables, even though it's just straight SQL.

Resources