{"id":55965,"date":"2019-05-08T12:54:43","date_gmt":"2019-05-08T07:24:43","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=55965"},"modified":"2021-12-04T13:11:35","modified_gmt":"2021-12-04T07:41:35","slug":"sap-hana-sql-expressions","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/","title":{"rendered":"How to Execute SAP HANA SQL Expressions – Learn in Few Minutes!"},"content":{"rendered":"

After completing SAP HANA SQL synonyms<\/strong>, we will subsequently move on to learn SAP HANA SQL expressions. We will be discussing the SQL expressions supported and used in SAP HANA. We will cover all four major types of SQL expressions used in SAP HANA.<\/p>\n

Without wasting any time, let’s start our tutorial.<\/p>\n

SAP HANA SQL Expressions<\/h3>\n

Expressions in any scripting language are used to evaluate a clause to produce the desired output i.e. return values. The general syntax of an SQL expression used in SAP HANA is given below. It is represented in BNF form.<\/p>\n

Syntax:<\/strong><\/p>\n

<expression> ::= <case_expression>\r\n| <function_expression>\r\n| <aggregate_expression>\r\n| (<expression> )\r\n| ( <subquery> )\r\n| - <expression>\r\n| <expression> <operator> <expression>\r\n| <variable_name>\r\n| <constant>\r\n| [<correlation_name>.]<column_name><\/pre>\n
The four types of SQL expressions that SAP HANA supports are:<\/p>\n
1. Case Expressions<\/h4>\n
We use the case expressions to pass multiple condition statements having a conditional clause like IF, ELSE, THEN in a SQL statement. Using case expression, we can evaluate a statement having multiple conditions and provide the output as per the situation.<\/p>\n
Below is the BNF representation of a case expression and its two types; simple case expression and search case expression.<\/p>\n
Syntax:<\/strong><\/p>\n
<case_expression> ::= <simple_case_expression> | <search_case_expression>\r\n\r\n<simple_case_expression> ::=\r\nCASE <expression>\r\nWHEN <expression> THEN <expression>\r\n[{ WHEN <expression> THEN <expression>}\u2026]\r\n[ ELSE <expression>]\r\nEND\r\n\r\n<search_case_expression> > ::=\r\nCASE\r\nWHEN <condition> THEN <expression>\r\n[{ WHEN <condition> THEN <expression>}\u2026]\r\n[ ELSE <expression>]\r\nEND\r\n\r\n<condition> ::= <condition> OR <condition> | <condition> AND <condition> | NOT <condition> | ( <condition> ) | <predicate><\/pre>\n
If the input matches the expression following WHEN statement, then the expression following THEN statement returns as output. If not, then the expression following ELSE statement returns.<\/p>\n
2. Function Expressions<\/h4>\n
The function expressions in SAP HANA scripting includes all the SQL built-in functions that you can use as expressions in SQL scripts.<\/p>\n
Syntax:<\/strong><\/p>\n
<function_expression> ::= <function_name> ( <expression> [{, <expression>}...] )<\/pre>\n
3. Aggregate Expressions<\/h4>\n
The aggregate expressions in SAP HANA essentially use aggregate functions to carry out complex calculations. Aggregate expressions evaluate single values from a set of multiple values taken from multiple rows of a data table. You can also sort the order of the values.<\/p>\n
The default order of returning the values is in the form of ascending order. In this type of order, NULL values returns first followed by greater values. You can change the ordering to descending.<\/p>\n
Syntax:<\/strong><\/p>\n
<aggregate_expression> ::= COUNT(*) | COUNT ( DISTINCT <expression_list> ) | <agg_name> ( [ ALL | DISTINCT ] <expression> ) | STRING_AGG ( <expression> [, <delimiter>] [<aggregate_order_by_clause>])\r\n\r\n<agg_name> ::= CORR | CORR_SPEARMAN | COUNT | MIN | MEDIAN | MAX | SUM | AVG | STDDEV | VAR | STDDEV_POP | VAR_POP | STDDEV_SAMP | VAR_SAMP\r\n\r\n<delimiter> ::= <string_constant>\r\n\r\n<aggregate_order_by_clause> ::= ORDER BY <expression> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST]\r\n\r\n<\/pre>\n
Commonly used aggregate functions in aggregate expressions are:<\/p>\n
    \n
  • SUM<\/strong>\u00a0– Returns the sum of the aggregate expression.<\/li>\n
  • AVG<\/strong>\u00a0– Returns the arithmetic mean of the aggregate expression.<\/li>\n
  • MIN<\/strong>\u00a0– Returns the minimum value of the aggregate expression.<\/li>\n
  • MAX<\/strong>\u00a0– Returns the maximum value of the aggregate expression.<\/li>\n
  • MEDIAN<\/strong>\u00a0– Returns the statistical median of the set of values given in aggregate expression as an input column.<\/li>\n
  • COUNT <\/strong>– Returns the total number of rows evaluated in the aggregate expression.<\/li>\n
  • CORR <\/strong>– Returns the result of the Pearson product momentum correlation coefficient between two columns specified in the expression.<\/li>\n
  • CORR_SPEARMAN<\/strong>\u00a0– Returns the Spearman’s rank correlation coefficient of the values present in the corresponding rows of two columns specified in the expression.<\/li>\n
  • STDDEV<\/strong> – Returns the standard deviation of the aggregate expression in terms of the square root of the VAR function.<\/li>\n
  • STDDEV_POP<\/strong>\u00a0– Returns the standard deviation of the aggregate expression in terms of the square root of the VAR_POP function.<\/li>\n
  • STDDEV_SAMP<\/strong> – Returns the standard deviation of the aggregate expression in terms of the square root of the VAR_SAMP function.<\/li>\n
  • VAR<\/strong> – Returns the variance value of the aggregate expression in terms of the square of the standard deviation value.<\/li>\n
  • VAR_POP<\/strong>\u00a0– Returns the population variance of the given aggregate expression.<\/li>\n
  • VAR_SAMP<\/strong> – Returns the sample variance of the given aggregate expression.<\/li>\n
  • STRING_AGG<\/strong> – Returns the concatenated string of the input given in the expression.<\/li>\n<\/ul>\n
    4. JSON Object Expressions<\/h4>\n
    The JSON object expressions used in SAP HANA generate a JSON object. A typical JSON object has two elements; Key and Value. A \u201c<key>\u201d is a string literal which must be\u00a0always enclosed in double quotes. And a <value> is an expression specific to a key and can be a simple value (string, integer).<\/p>\n
    JSON object is enclosed in {} braces or an array []<\/strong>. You can only use JSON object expressions when you are working with the JSON collection tables. You can refer the JSON object expressions by statements like SELECT, INSERT INTO, UPDATE or you can use it with operators like +, -, \/, *.<\/p>\n
    Syntax:<\/strong><\/p>\n
    <json_object_expression>::=\r\n{\"<key>\":<json_value_expression>}\r\n\r\n<json_value_expression> ::= '<string>'\r\n| <numeric_literal>\r\n| <boolean_literal>\r\n| NULL\r\n| <path_expression>\r\n| <json_object_expression>\r\n| <json_array_expression>\r\n\r\n<json_array_expression>::= [<json_array_value_expression>,\u2026 ]\r\n\r\n<json_array_value_expression> ::= '<string>'\r\n| <numeric_literal>\r\n| <boolean_literal>\r\n| NULL\r\n| <path_expression>\r\n| <json_object_expression><\/pre>\n
    Example:<\/strong><\/p>\n
    { \"firstname\":'Aditya', \"lastname\":'Kapoor', \"age\":45 }\r\n\r\n{ \"firstname\":'Aditya', \"lastname\":'Kapoor', \"age\":45, \"address\": { 'street': \u2018Flat no.512, wing B, Leela Residency', 'city': 'Mumbai' } }\r\n\r\n<\/pre>\n
    Adding Subqueries in SQL Expressions<\/h3>\n
    Subqueries passes an additional logic to the database as a SQL statement. A subquery statement is a SELECT statement enclosed in parentheses. A subquery SELECT statement can only contain one select list item.<\/p>\n
    You can use a subquery SELECT statement anywhere where a column name involves. For instance, in the SELECT list of top-level SELECT or in the SET clause of an UPDATE statement.<\/p>\n
    Syntax:<\/strong><\/p>\n
    <scalar_subquery_expression> ::= (<subquery>)\r\nA sample SQL script with a subquery select statement is given below.\r\n\r\nSELECT DataFlairDepartments, COUNT(*), 'out of',\r\n(SELECT COUNT(*) FROM Employees)\r\nFROM Departments AS Dep, Employees AS Emp\r\nWHERE Dep.DepartmentID = Emp.DepartmentID\r\nGROUP BY DataFlairDepartment;\r\n\r\n<\/pre>\n
    Summary<\/h3>\n
    These were all the different types of SQL expressions used in SAP HANA to generate a script and perform operations on the SAP HANA database. There are many implementations of SQL in the SAP HANA database which we will cover in upcoming tutorials.<\/p>\n
    We hope the explanation was helpful. Please leave your comments below in case of any doubts or feedbacks.<\/p>\n","protected":false},"excerpt":{"rendered":"
    After completing SAP HANA SQL synonyms, we will subsequently move on to learn SAP HANA SQL expressions. We will be discussing the SQL expressions supported and used in SAP HANA. We will cover all...<\/p>\n","protected":false},"author":6,"featured_media":55970,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18002],"tags":[19697,19699,19698],"class_list":["post-55965","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sap-hana","tag-sap-hana-sql-expressions","tag-sql-aggregate-expressions","tag-sql-case-expressions"],"yoast_head":"\nHow to Execute SAP HANA SQL Expressions - Learn in Few Minutes! - DataFlair<\/title>\n<meta name=\"description\" content=\"Understand the four SQL expressions that are supported and used in SAP HANA database and learn to add subqueries in expressions with syntax. Learn more.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Execute SAP HANA SQL Expressions - Learn in Few Minutes! - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Understand the four SQL expressions that are supported and used in SAP HANA database and learn to add subqueries in expressions with syntax. Learn more.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2019-05-08T07:24:43+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-12-04T07:41:35+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SAP-HANA-SQL-Expressions-01.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"802\" \/>\n\t<meta property=\"og:image:height\" content=\"420\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Execute SAP HANA SQL Expressions - Learn in Few Minutes! - DataFlair","description":"Understand the four SQL expressions that are supported and used in SAP HANA database and learn to add subqueries in expressions with syntax. Learn more.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/","og_locale":"en_US","og_type":"article","og_title":"How to Execute SAP HANA SQL Expressions - Learn in Few Minutes! - DataFlair","og_description":"Understand the four SQL expressions that are supported and used in SAP HANA database and learn to add subqueries in expressions with syntax. Learn more.","og_url":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2019-05-08T07:24:43+00:00","article_modified_time":"2021-12-04T07:41:35+00:00","og_image":[{"width":802,"height":420,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SAP-HANA-SQL-Expressions-01.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"How to Execute SAP HANA SQL Expressions – Learn in Few Minutes!","datePublished":"2019-05-08T07:24:43+00:00","dateModified":"2021-12-04T07:41:35+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/"},"wordCount":805,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SAP-HANA-SQL-Expressions-01.jpg","keywords":["SAP HANA SQL Expressions","SQL Aggregate Expressions","SQL Case Expressions"],"articleSection":["SAP HANA Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/","url":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/","name":"How to Execute SAP HANA SQL Expressions - Learn in Few Minutes! - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SAP-HANA-SQL-Expressions-01.jpg","datePublished":"2019-05-08T07:24:43+00:00","dateModified":"2021-12-04T07:41:35+00:00","description":"Understand the four SQL expressions that are supported and used in SAP HANA database and learn to add subqueries in expressions with syntax. Learn more.","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SAP-HANA-SQL-Expressions-01.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SAP-HANA-SQL-Expressions-01.jpg","width":802,"height":420,"caption":"SAP HANA SQL Expressions Topics"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-expressions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"SAP HANA Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sap-hana\/"},{"@type":"ListItem","position":3,"name":"How to Execute SAP HANA SQL Expressions – Learn in Few Minutes!"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/55965","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=55965"}],"version-history":[{"count":12,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/55965\/revisions"}],"predecessor-version":[{"id":104921,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/55965\/revisions\/104921"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/55970"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=55965"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=55965"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=55965"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}