TDV Adapter for IBM Cloud SQL Query

Build 21.0.8137

SELECT Statements

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 IBM Cloud SQL Query adapter:

SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  { 
    * 
    | { 
        <expression> [ [ AS ] <column_reference> ] 
        | { <table_name> | <correlation_name> } .* 
      } [ , ... ] 
  }
  [ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
  { 
    FROM <table_reference> [ [ AS ] <identifier> ] 
  } [ , ... ]
  [ [ 
      INNER | { { LEFT | RIGHT | FULL  } [ OUTER ] }  
    ] JOIN <table_reference> [ ON <search_condition> ] [ [ 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>
  ] 
} | 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> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | ANY | ALL | EXISTS | NOT EXISTS | CONTAINS | NOT CONTAINS | BETWEEN | AND | OR } [ <expression> ]
    } [ { AND | OR } ... ]

Examples

  1. Return all columns:
    SELECT * FROM [CloudObjectStorage_1].[SampleBucket_1].Jobs
  2. Rename a column:
    SELECT "Status" AS MY_Status FROM [CloudObjectStorage_1].[SampleBucket_1].Jobs
  3. Cast a column's data as a different data type:
    SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM [CloudObjectStorage_1].[SampleBucket_1].Jobs
  4. Search data:
    SELECT * FROM [CloudObjectStorage_1].[SampleBucket_1].Jobs WHERE UserId = 'gentit@cdata.com';
  5. The IBM Cloud SQL Query APIs support the following operators in the WHERE clause: =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, ANY, ALL, EXISTS, NOT EXISTS, CONTAINS, NOT CONTAINS, BETWEEN, AND, OR.
    SELECT * FROM [CloudObjectStorage_1].[SampleBucket_1].Jobs WHERE UserId = 'gentit@cdata.com';
  6. Return the number of items matching the query criteria:
    SELECT COUNT(*) AS MyCount FROM [CloudObjectStorage_1].[SampleBucket_1].Jobs 
  7. Return the number of unique items matching the query criteria:
    SELECT COUNT(DISTINCT Status) FROM [CloudObjectStorage_1].[SampleBucket_1].Jobs 
  8. Return the unique items matching the query criteria:
    SELECT DISTINCT Status FROM [CloudObjectStorage_1].[SampleBucket_1].Jobs 
  9. Summarize data:
    SELECT Status, MAX(AnnualRevenue) FROM [CloudObjectStorage_1].[SampleBucket_1].Jobs  GROUP BY Status
    See Aggregate Functions for details.
  10. Retrieve data from multiple tables.
    SELECT Orders.OrderDate, Customers.ContactName FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
    See JOIN Queries for details.
  11. Sort a result set in ascending order:
    SELECT Id, Status FROM [CloudObjectStorage_1].[SampleBucket_1].Jobs  ORDER BY Status ASC

Copyright (c) 2022 CData Software, Inc. - All rights reserved.
Build 21.0.8137