Predicate in SAP HANA Database

Er Tasneem Ahmad Karimi Sunday, October 25, 2020




What is predicate?


A predicate is specified by combining one or more expressions, or logical operators, and returns one of the following logical/truth values: TRUE, FALSE, or UNKNOWN.


Following are the predicate in SAP HANA Database: 


  • Comparison Predicates (ANY, SOME, and ALL)

    Compares values using the specified comparison operator and returns true, false, or unknown.


ANY | SOME

 

Specifies that the comparison returns true if the comparison of the <expression> and at least one value returned by the <subquery> or <expression_list> is true. 

 

For example:

 

SELECT * FROM EmployeeMaster WHERE EmpID=SOME(‘1’,’2’);

 

ANY and SOME, with equal Operator, is the equivalent of IN.

 

ALL

                 Specifies that the comparison returns true if the comparison of all values returned by the <subquery> or <expression_list> is true. If the <subquery> or <expression_list> is empty, the comparison returns true. 

For example:

SELECT * FROM EmployeeTable WHERE Salary>=(2000,3000);


  • BETWEEN Predicate

    Compares a value with a list of values within the specified range and returns true or false.

For Example:


SELECT * FROM ORDR WHERE DocDate Between ‘20200101’ and ‘20200131’;


NOT

Inverts the operation of the BETWEEN predicate

SELECT * FROM ORDR WHERE DocDate Not Between ‘20200101’ and ‘20200131’;


  • CONTAINS Predicate

    Matches a search string with the results of a subquery.


  • EXISTS Predicate

    Tests for the presence of a value in a set and returns either true or false.

  • IN Predicate

    Searches for a value in a set of values and returns true or false.

  • LIKE Predicate

    Performs a comparison to see if a character string matches a specified pattern.


The LIKE predicate performs string comparisons: <source_expression> is tested for the pattern contained in <pattern_expression>. LIKE returns true if the value of <pattern_expression> is found in <source_expression> (assuming NOT is not set).

Wildcard characters ( % ) and ( _ ) can be used in <pattern_expression>. The percentage sign (%) wildcard matches zero or more characters. The underscore (_) wildcard matches exactly one character.

To match a percentage sign or underscore with the LIKE predicate, an escape character must be placed in front of the wildcard character. Use ESCAPE <escape_expression> to specify the escape character you are using. For example, LIKE 'data_%' ESCAPE '_' matches the string data%, and LIKE 'data__%' ESCAPE '_' (that is, two underscores, followed by a percent sign) matches a string that starts with 'data_'

The underscore ( _ ) and percentage sign ( % ) are ASCII characters.

An expression is either a simple expression such as a character, a date or a number, or it can be a scalar subquery.


  • LIKE_REGEXPR Predicate

    Performs regular expression matching.

  • MEMBER OF Predicate

    Determines whether a value is a member of an array.

  • NULL Predicate

    Performs a comparison of the value of an expression with NULL.

IS NULL

Returns true if the value of <expression> is NULL.

IS NOT NULL

Returns true if the value of <expression> is not NULL.

#predicates#one#reference#predicate#index#like#logicaltruth#false#expressions#contains#exists#null#using#returns#sap#true#unknown#operators#values#syntax#comparison predicates#predicate predicate#more expressions#null predicate#mainpage index#following logicaltruth#unknown syntax#operators returns#predicate like#sap hana#returns true#reference manual#values compared#true #false#like predicate null#one following logicaltruth#index sql reference#expressions logical operators#contains predicate predicate#predicate predicate exists#predicates comparison predicates#true false unknown#sap hana reference#returns true false#predicates two values#using comparison predicates



Tags: