ODBC and JDBC Developer’s Guide

Supported standards 37
SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data
WHERE Sales_Data.INVOICE_ID NOT IN (SELECT Sales_Data.Invoice_ID
FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-4')
SELECT *
FROM Sales_Data WHERE EXISTS (SELECT Sales_Data.Amount
FROM Sales_Data WHERE Sales_Data.Salesperson_ID IS NOT NULL)
SELECT *
FROM Sales_Data WHERE Sales_Data.Amount = ANY (SELECT Sales_Data.Amount
FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-1')
SELECT *
FROM Sales_Data WHERE Sales_Data.Amount = ALL (SELECT Sales_Data.Amount
FROM Sales_Data WHERE Sales_Data.Salesperson_ID IS NULL)
Logical operators
You can combine two or more conditions. The conditions must be related by AND or OR, such as:
salary = 40000 AND exempt = 1
The logical NOT operator is used to reverse the meaning, such as:
NOT (salary = 40000 AND exempt = 1)
Examples
SELECT * FROM Sales_Data WHERE Sales_Data.Company_Name
NOT LIKE '%University' AND Sales_Data.Amount > 3000
SELECT * FROM Sales_Data WHERE (Sales_Data.Company_Name
LIKE '%University' OR Sales_Data.Amount > 3000)
AND Sales_Data.Salesperson_ID = 'SP-1'
Functions
The ODBC and JDBC client drivers support many functions you can use in expressions. Some of the functions
return characters strings, some return numbers, and some return dates.
Important Use uppercase letters for SQL function names (some are case sensitive).
Functions that
return character
strings Description Example
CHR Converts an ASCII code to a one-
character string
CHR(67) returns C
RTRIM Removes trailing blanks from a string RTRIM('ABC ') returns ABC
TRIM Removes trailing blanks from a string TRIM('ABC ') returns ABC
LTRIM Removes leading blanks from a string LTRIM(' ABC') returns ABC
UPPER Changes each letter of a string to
uppercase
UPPER('Allen') returns ALLEN
LOWER Changes each letter of a string to
lowercase
LOWER('Allen') returns allen
LEFT Returns leftmost characters of a string LEFT('Mattson',3) returns Mat
RIGHT Returns rightmost characters of a string RIGHT('Mattson',4) returns tson