Operators & Functions

Master SuiteQL operators and functions for filtering, transforming, and manipulating data.

Comparison Operators

Basic Comparisons

Operator	Description	Example
`=`	Equal to	`status = 'A'`
`!=` or `<>`	Not equal to	`status != 'Closed'`
`>`	Greater than	`amount > 1000`
`<`	Less than	`quantity < 10`
`>=`	Greater or equal	`balance >= 0`
`<=`	Less or equal	`creditlimit <= 50000`

Test Case: Basic Comparisons

-- Find high-value customers (balance over $10,000)
SELECT
    id,
    companyname,
    balance
FROM customer
WHERE balance > 10000
ORDER BY balance DESC

Sample Result:

id	companyname	balance
105	Enterprise Corp	45000.00
203	Global Tech	28500.00
89	Mega Industries	15750.00

Logical Operators

AND, OR, NOT

-- AND: Both conditions must be true
SELECT id, companyname
FROM customer
WHERE isinactive = 'F'
  AND balance > 1000

-- OR: Either condition can be true
SELECT id, companyname
FROM customer
WHERE category = 'Retail'
   OR category = 'Wholesale'

-- NOT: Negates the condition
SELECT id, companyname
FROM customer
WHERE NOT isinactive = 'T'

Test Case: Complex Logic

-- Find active customers who are either:
-- 1. High value (balance > 10000), OR
-- 2. In California with any balance
SELECT
    id,
    companyname,
    state,
    balance
FROM customer
WHERE isinactive = 'F'
  AND (
    balance > 10000
    OR (state = 'CA' AND balance > 0)
  )
ORDER BY balance DESC
LIMIT 20

Explanation:

isinactive = 'F' - Must be active
Parentheses group OR conditions
Without parentheses, logic would be wrong

LIKE Operator (Pattern Matching)

Wildcards

Wildcard	Meaning	Example
`%`	Any characters (0 or more)	`'A%'` matches "Apple", "A", "Amazing"
`_`	Exactly one character	`'_at'` matches "Cat", "Bat", "Hat"

Test Cases: Pattern Matching

-- Starts with "Tech"
SELECT companyname FROM customer
WHERE companyname LIKE 'Tech%'
-- Matches: "TechCorp", "Technology Solutions", "Tech"

-- Ends with "Inc"
SELECT companyname FROM customer
WHERE companyname LIKE '%Inc'
-- Matches: "Acme Inc", "Global Inc"

-- Contains "Solutions"
SELECT companyname FROM customer
WHERE companyname LIKE '%Solutions%'
-- Matches: "Tech Solutions", "Solutions Corp", "Best Solutions Inc"

-- Exactly 5 characters
SELECT companyname FROM customer
WHERE companyname LIKE '_____'
-- Matches: "Acme1", "Tech5" (exactly 5 chars)

-- Second character is 'a'
SELECT companyname FROM customer
WHERE companyname LIKE '_a%'
-- Matches: "Walmart", "Target", "Samsung"

IN Operator

Multiple Value Matching

-- Instead of multiple ORs
SELECT id, tranid, type
FROM transaction
WHERE type IN ('SalesOrd', 'CustInvc', 'CashSale')

-- Equivalent to:
-- WHERE type = 'SalesOrd' OR type = 'CustInvc' OR type = 'CashSale'

Test Case: IN with Subquery

-- Find customers who have placed orders
SELECT id, companyname
FROM customer
WHERE id IN (
    SELECT entity
    FROM transaction
    WHERE type = 'SalesOrd'
)

NOT IN

-- Find customers who have NOT placed orders
SELECT id, companyname
FROM customer
WHERE id NOT IN (
    SELECT entity
    FROM transaction
    WHERE type = 'SalesOrd'
)

BETWEEN Operator

Range Queries

-- Amount between 1000 and 5000 (inclusive)
SELECT id, tranid, amount
FROM transaction
WHERE amount BETWEEN 1000 AND 5000

-- Equivalent to:
-- WHERE amount >= 1000 AND amount <= 5000

Test Case: Date Range

-- Orders from Q1 2024
SELECT
    id,
    tranid,
    trandate,
    amount
FROM transaction
WHERE type = 'SalesOrd'
  AND trandate BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD')
                   AND TO_DATE('2024-03-31', 'YYYY-MM-DD')
ORDER BY trandate

NULL Handling

IS NULL / IS NOT NULL

-- Find customers without email
SELECT id, companyname
FROM customer
WHERE email IS NULL

-- Find customers with email
SELECT id, companyname, email
FROM customer
WHERE email IS NOT NULL

NVL Function (NULL Value)

-- Replace NULL with default value
SELECT
    id,
    companyname,
    NVL(email, 'no-email@example.com') AS email,
    NVL(phone, 'N/A') AS phone
FROM customer

COALESCE (First Non-NULL)

-- Use first available contact method
SELECT
    id,
    companyname,
    COALESCE(email, phone, fax, 'No Contact') AS primary_contact
FROM customer

Test Case: NULL Handling

-- Customer contact report with fallbacks
SELECT
    id,
    companyname,
    NVL(email, 'Not Provided') AS email,
    NVL(phone, 'Not Provided') AS phone,
    CASE
        WHEN email IS NOT NULL THEN 'Email'
        WHEN phone IS NOT NULL THEN 'Phone'
        ELSE 'No Contact'
    END AS preferred_contact
FROM customer
WHERE isinactive = 'F'
LIMIT 20

Sample Result:

id	companyname	email	phone	preferred_contact
1	Acme Corp	acme@test.com	555-1234	Email
2	Beta Inc	Not Provided	555-5678	Phone
3	Gamma Ltd	Not Provided	Not Provided	No Contact

String Functions

Common String Functions

Function	Description	Example	Result
`UPPER(s)`	Uppercase	`UPPER('hello')`	`'HELLO'`
`LOWER(s)`	Lowercase	`LOWER('HELLO')`	`'hello'`
`INITCAP(s)`	Title Case	`INITCAP('hello world')`	`'Hello World'`
`LENGTH(s)`	String length	`LENGTH('hello')`	`5`
`TRIM(s)`	Remove spaces	`TRIM(' hi ')`	`'hi'`
`LTRIM(s)`	Remove left spaces	`LTRIM(' hi')`	`'hi'`
`RTRIM(s)`	Remove right spaces	`RTRIM('hi ')`	`'hi'`
`SUBSTR(s,start,len)`	Substring	`SUBSTR('hello',1,3)`	`'hel'`
`CONCAT(a,b)`	Concatenate	`CONCAT('Hello',' World')`	`'Hello World'`
`\|\|`	Concatenate	`'Hello' \|\| ' World'`	`'Hello World'`
`REPLACE(s,old,new)`	Replace text	`REPLACE('hello','l','L')`	`'heLLo'`
`INSTR(s,sub)`	Find position	`INSTR('hello','l')`	`3`

Test Case: String Manipulation

-- Format customer names and extract info
SELECT
    id,
    UPPER(companyname) AS company_upper,
    INITCAP(companyname) AS company_title,
    LENGTH(companyname) AS name_length,
    SUBSTR(companyname, 1, 10) AS short_name,
    CONCAT(companyname, ' - ', NVL(city, 'Unknown')) AS full_display
FROM customer
WHERE LENGTH(companyname) > 5
LIMIT 10

Test Case: Parsing Email Domain

-- Extract email domain
SELECT
    id,
    companyname,
    email,
    SUBSTR(email, INSTR(email, '@') + 1) AS email_domain
FROM customer
WHERE email IS NOT NULL
  AND INSTR(email, '@') > 0
LIMIT 20

Sample Result:

id	companyname	email	email_domain
1	Acme Corp	info@acme.com	acme.com
2	Tech Inc	sales@tech.io	tech.io

Numeric Functions

Common Numeric Functions

Function	Description	Example	Result
`ROUND(n,d)`	Round to d decimals	`ROUND(123.456, 2)`	`123.46`
`TRUNC(n,d)`	Truncate to d decimals	`TRUNC(123.456, 2)`	`123.45`
`CEIL(n)`	Round up	`CEIL(123.1)`	`124`
`FLOOR(n)`	Round down	`FLOOR(123.9)`	`123`
`ABS(n)`	Absolute value	`ABS(-123)`	`123`
`MOD(n,m)`	Modulo (remainder)	`MOD(10, 3)`	`1`
`POWER(n,p)`	Power	`POWER(2, 3)`	`8`
`SQRT(n)`	Square root	`SQRT(16)`	`4`

Test Case: Financial Calculations

-- Calculate invoice details with rounding
SELECT
    id,
    tranid,
    amount,
    ROUND(amount * 0.1, 2) AS tax_amount,
    ROUND(amount * 1.1, 2) AS total_with_tax,
    FLOOR(amount / 100) AS hundreds,
    MOD(CAST(amount AS INTEGER), 100) AS remainder
FROM transaction
WHERE type = 'CustInvc'
  AND amount > 0
LIMIT 10

Sample Result:

id	tranid	amount	tax_amount	total_with_tax	hundreds	remainder
101	INV-001	1234.56	123.46	1358.02	12	34
102	INV-002	567.89	56.79	624.68	5	67

Date Functions

Current Date/Time

Function	Description
`SYSDATE`	Current date and time
`CURRENT_DATE`	Current date
`CURRENT_TIMESTAMP`	Current timestamp

Date Conversion

-- String to Date
TO_DATE('2024-01-15', 'YYYY-MM-DD')
TO_DATE('15-Jan-2024', 'DD-Mon-YYYY')
TO_DATE('01/15/2024', 'MM/DD/YYYY')

-- Date to String
TO_CHAR(trandate, 'YYYY-MM-DD')
TO_CHAR(trandate, 'Month DD, YYYY')
TO_CHAR(trandate, 'Day')

Date Parts (EXTRACT)

-- Extract parts of a date
SELECT
    trandate,
    EXTRACT(YEAR FROM trandate) AS year,
    EXTRACT(MONTH FROM trandate) AS month,
    EXTRACT(DAY FROM trandate) AS day,
    TO_CHAR(trandate, 'Day') AS day_name,
    TO_CHAR(trandate, 'Month') AS month_name
FROM transaction
LIMIT 5

Date Arithmetic

-- Add/subtract days
SELECT
    SYSDATE AS today,
    SYSDATE + 7 AS next_week,
    SYSDATE - 30 AS last_month,
    SYSDATE + 365 AS next_year
FROM DUAL

-- Difference between dates (in days)
SELECT
    tranid,
    trandate,
    duedate,
    duedate - trandate AS days_to_pay
FROM transaction
WHERE type = 'CustInvc'

Test Case: Date Analysis

-- Order aging analysis
SELECT
    id,
    tranid,
    trandate,
    TO_CHAR(trandate, 'YYYY-MM') AS order_month,
    ROUND(SYSDATE - trandate) AS days_old,
    CASE
        WHEN SYSDATE - trandate <= 30 THEN 'Current'
        WHEN SYSDATE - trandate <= 60 THEN '30-60 Days'
        WHEN SYSDATE - trandate <= 90 THEN '60-90 Days'
        ELSE 'Over 90 Days'
    END AS aging_bucket
FROM transaction
WHERE type = 'CustInvc'
  AND status != 'C'  -- Not closed
ORDER BY trandate
LIMIT 20

Sample Result:

id	tranid	trandate	order_month	days_old	aging_bucket
501	INV-501	2024-11-15	2024-11	37	30-60 Days
502	INV-502	2024-10-01	2024-10	82	60-90 Days
503	INV-503	2024-07-15	2024-07	160	Over 90 Days

Type Conversion (CAST)

Converting Data Types

-- String to Number
CAST('123' AS INTEGER)
CAST('123.45' AS FLOAT)

-- Number to String
CAST(123 AS VARCHAR)
TO_CHAR(123.45, '999.99')

-- Date conversions
CAST(trandate AS VARCHAR)
TO_CHAR(trandate, 'YYYY-MM-DD')

Test Case: Type Conversion

-- Format amounts as currency strings
SELECT
    tranid,
    amount,
    '$' || TO_CHAR(amount, 'FM999,999,990.00') AS formatted_amount,
    CAST(amount AS INTEGER) AS rounded_amount
FROM transaction
WHERE type = 'CustInvc'
LIMIT 10

DISTINCT Keyword

Removing Duplicates

-- Unique customer states
SELECT DISTINCT state
FROM customer
WHERE state IS NOT NULL
ORDER BY state

-- Unique combinations
SELECT DISTINCT state, city
FROM customer
WHERE state IS NOT NULL
ORDER BY state, city

Test Case: Unique Values Report

-- Count unique values
SELECT
    COUNT(*) AS total_customers,
    COUNT(DISTINCT state) AS unique_states,
    COUNT(DISTINCT city) AS unique_cities,
    COUNT(DISTINCT salesrep) AS unique_salesreps
FROM customer
WHERE isinactive = 'F'

Sample Result:

total_customers	unique_states	unique_cities	unique_salesreps
1250	48	312	15

Quick Reference

Operator Precedence (Highest to Lowest)

() - Parentheses
*, / - Multiplication, Division
+, - - Addition, Subtraction
=, !=, <, >, <=, >= - Comparisons
NOT - Negation
AND - Logical AND
OR - Logical OR

Common Patterns

-- Case-insensitive search
WHERE UPPER(companyname) LIKE UPPER('%search%')

-- Check if value exists in list
WHERE status IN ('A', 'B', 'C')

-- Date in current month
WHERE EXTRACT(MONTH FROM trandate) = EXTRACT(MONTH FROM SYSDATE)
  AND EXTRACT(YEAR FROM trandate) = EXTRACT(YEAR FROM SYSDATE)

-- Handle NULL in comparisons
WHERE NVL(field, 'default') = 'value'

-- Between with dates
WHERE trandate BETWEEN TRUNC(SYSDATE, 'MM') AND SYSDATE

Comparison Operators​

Basic Comparisons​

Test Case: Basic Comparisons​

Logical Operators​

AND, OR, NOT​

Test Case: Complex Logic​

LIKE Operator (Pattern Matching)​

Wildcards​

Test Cases: Pattern Matching​

IN Operator​

Multiple Value Matching​

Test Case: IN with Subquery​

NOT IN​

BETWEEN Operator​

Range Queries​

Test Case: Date Range​

NULL Handling​

IS NULL / IS NOT NULL​

NVL Function (NULL Value)​

COALESCE (First Non-NULL)​

Test Case: NULL Handling​

String Functions​

Common String Functions​

Test Case: String Manipulation​

Test Case: Parsing Email Domain​

Numeric Functions​

Common Numeric Functions​

Test Case: Financial Calculations​

Date Functions​

Current Date/Time​

Date Conversion​

Date Parts (EXTRACT)​

Date Arithmetic​

Test Case: Date Analysis​

Type Conversion (CAST)​

Converting Data Types​

Test Case: Type Conversion​

DISTINCT Keyword​

Removing Duplicates​

Test Case: Unique Values Report​

Quick Reference​

Operator Precedence (Highest to Lowest)​

Common Patterns​

Comparison Operators

Basic Comparisons

Test Case: Basic Comparisons

Logical Operators

AND, OR, NOT

Test Case: Complex Logic

LIKE Operator (Pattern Matching)

Wildcards

Test Cases: Pattern Matching

IN Operator

Multiple Value Matching

Test Case: IN with Subquery

NOT IN

BETWEEN Operator

Range Queries

Test Case: Date Range

NULL Handling

IS NULL / IS NOT NULL

NVL Function (NULL Value)

COALESCE (First Non-NULL)

Test Case: NULL Handling

String Functions

Common String Functions

Test Case: String Manipulation

Test Case: Parsing Email Domain

Numeric Functions

Common Numeric Functions

Test Case: Financial Calculations

Date Functions

Current Date/Time

Date Conversion

Date Parts (EXTRACT)

Date Arithmetic

Test Case: Date Analysis

Type Conversion (CAST)

Converting Data Types

Test Case: Type Conversion

DISTINCT Keyword

Removing Duplicates

Test Case: Unique Values Report

Quick Reference

Operator Precedence (Highest to Lowest)

Common Patterns