A SELECT statement can consist of the following basic clauses.

• SELECT
• INTO
• FROM
• JOIN
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY
• LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SAP Ariba Procurement adapter:

SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  { 
    * 
    | { 
        <expression> [ [ AS ] <column_reference> ] 
        | { <table_name> | <correlation_name> } .* 
      } [ , ... ] 
  }
  { 
    FROM <table_reference> [ [ AS ] <identifier> ] 
  }
  [ WHERE <search_condition> ]
  [ GROUP BY <column_reference> [ , ... ]
  [ HAVING <search_condition> ]
  [ UNION  [ ALL ] <select_statement> ]
  [ 
    ORDER BY 
    <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
  ]
  [
    LIMIT <expression>
    [ 
      { OFFSET | , }
      <expression> 
    ]
  ] 
} | SCOPE_IDENTITY() 

  <expression> ::=
    | <column_reference>
    | @ <parameter> 
    | ?
    | COUNT( * | { [ DISTINCT ] <expression> } )
    | { AVG | MAX | MIN | SUM | COUNT } ( <expression> ) 
    | NULLIF ( <expression> , <expression> ) 
    | COALESCE ( <expression> , ... ) 
    | CASE <expression>
        WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
    [ ELSE { <expression> | NULL } ]
      END 
    | <literal>
    | <sql_function> 

  <search_condition> ::= 
    {
      <expression> { = | AND } [ <expression> ]
    } [ { AND | OR } ... ]

Examples

1. Return all columns:
   

   SELECT * FROM Orders

2. Rename a column:
   

   SELECT "Revision" AS MY_Revision FROM Orders

3. Cast a column's data as a different data type:
   

   SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Orders

4. Search data:
   

   SELECT * FROM Orders WHERE VendorId = 'v1'

5. The SAP Ariba Procurement APIs support the following operators in the WHERE clause: =, AND.
   

   SELECT * FROM Orders WHERE VendorId = 'v1';

6. Return the number of items matching the query criteria:
   

   SELECT COUNT(*) AS MyCount FROM Orders 

7. Return the number of unique items matching the query criteria:
   

   SELECT COUNT(DISTINCT Revision) FROM Orders 

8. Return the unique items matching the query criteria:
   

   SELECT DISTINCT Revision FROM Orders 

9. Sort a result set in ascending order:
   

   SELECT DocumentNumber, Revision FROM Orders  ORDER BY Revision ASC

Window Functions

See Window Functions for SELECT examples containing window functions.

Table-Valued Functions

See Table-Valued Functions for SELECT examples with table-valued functions.