SELECT Queries

Syntra ODBC supports standard SQL SELECT syntax for reading QuickBooks data. Queries run against the local cache by default, with the option to force live fetches from QuickBooks.

Basic SELECT

SELECT * FROM customers;

SELECT name, phone, email, balance
FROM customers;

WHERE Clauses

Filter rows using standard comparison operators:

SELECT name, balance
FROM customers
WHERE balance > 1000;

SELECT * FROM invoices
WHERE txn_date >= '2025-01-01'
  AND is_paid = false;

SELECT * FROM item_inventories
WHERE name ILIKE '%widget%';

Supported Operators

Operator	Example
=, !=, <>	WHERE is_active = true
<, >, <=, >=	WHERE balance >= 500
LIKE / ILIKE	WHERE name ILIKE '%corp%'
IN	WHERE ref_number IN ('INV-001', 'INV-002')
BETWEEN	WHERE txn_date BETWEEN '2025-01-01' AND '2025-12-31'
IS NULL / IS NOT NULL	WHERE email IS NOT NULL
AND, OR, NOT	WHERE balance > 0 AND is_active = true

ORDER BY

Sort results by one or more columns:

SELECT name, balance
FROM customers
ORDER BY balance DESC;

SELECT * FROM invoices
ORDER BY txn_date DESC, ref_number ASC;

LIMIT and OFFSET

Restrict the number of returned rows:

SELECT * FROM customers
ORDER BY name
LIMIT 50;

SELECT * FROM customers
ORDER BY name
LIMIT 50 OFFSET 100;

GROUP BY and Aggregates

Use aggregate functions with GROUP BY:

SELECT customer_ref_full_name, COUNT(*) AS invoice_count, SUM(balance_remaining) AS total_owed
FROM invoices
GROUP BY customer_ref_full_name
ORDER BY total_owed DESC;

Supported Aggregate Functions

• COUNT(*), COUNT(column), COUNT(DISTINCT column)
• SUM(column)
• AVG(column)
• MIN(column), MAX(column)

Examples

-- Total customers
SELECT COUNT(*) AS total_customers FROM customers;

-- Invoice totals
SELECT SUM(subtotal) AS total, AVG(subtotal) AS average FROM invoices;

-- Revenue by customer
SELECT
    customer_ref_full_name,
    COUNT(*) AS invoice_count,
    SUM(subtotal) AS revenue,
    AVG(subtotal) AS avg_invoice
FROM invoices
GROUP BY customer_ref_full_name
ORDER BY revenue DESC;

-- Monthly invoice summary
SELECT
    DATE_TRUNC('month', txn_date) AS month,
    COUNT(*) AS invoices,
    SUM(subtotal) AS total
FROM invoices
GROUP BY month
ORDER BY month;

HAVING

Filter groups after aggregation:

SELECT customer_ref_full_name, SUM(balance_remaining) AS total_owed
FROM invoices
GROUP BY customer_ref_full_name
HAVING SUM(balance_remaining) > 5000
ORDER BY total_owed DESC;

Column Aliases

SELECT
    name AS "Customer",
    balance AS "Amount Owed",
    phone AS "Phone Number"
FROM customers;

DISTINCT

Remove duplicate rows:

SELECT DISTINCT customer_ref_full_name
FROM invoices
WHERE txn_date >= '2025-01-01';

CASE Expressions

SELECT
    name,
    balance,
    CASE
        WHEN balance = 0 THEN 'Paid'
        WHEN balance < 1000 THEN 'Low'
        ELSE 'High'
    END AS balance_category
FROM customers;

Column Metadata and Data Types

Every column returned by a query has a Syntra SQL type with a numeric type code. You can inspect column types using the Query Explorer’s hover tooltips (hover over any column header) or the Copy Schema JSON button. See the Syntra SQL Type Reference for what each code means.

Schema JSON Example

Running SELECT list_id, name, full_name, email, phone, balance FROM customers LIMIT 5 and clicking Copy Schema JSON produces:

[
  { "name": "list_id", "type": "varchar", "oid": 1043 },
  { "name": "name", "type": "varchar", "oid": 1043 },
  { "name": "full_name", "type": "varchar", "oid": 1043 },
  { "name": "email", "type": "varchar", "oid": 1043 },
  { "name": "phone", "type": "varchar", "oid": 1043 },
  { "name": "balance", "type": "numeric", "oid": 1700 }
]

Data Type Reference

Syntra ODBC maps QuickBooks field types to standard PostgreSQL types:

OID	PG Type	Description	QuickBooks Examples
1043	varchar	Variable-length string	Names, IDs, addresses, memos
1700	numeric	Decimal with precision/scale	Balance, subtotal, rate, amount
23	int4	32-bit integer	TxnNumber, counts
20	int8	64-bit integer	COUNT(*) results
16	bool	Boolean (true/false)	IsActive, IsPaid, IsPending
1082	date	Calendar date	TxnDate, DueDate, ShipDate
1114	timestamp	Date + time	TimeCreated, TimeModified

Querying Column Metadata

You can also query column metadata directly via SQL:

-- List all columns for a table
SHOW COLUMNS FROM customers;

-- Detailed column info via information_schema
SELECT column_name, data_type, ordinal_position
FROM information_schema.columns
WHERE table_name = 'invoices'
ORDER BY ordinal_position;

-- Column metadata via pg_catalog (includes type OIDs)
SELECT attname, typname, atttypid
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON true
WHERE c.relname = 'customers';

Table Index

Syntra ODBC exposes 121 QuickBooks tables. Here are the most commonly queried:

Customers & Receivables

Table	Key Column	Description
customers	list_id	Customer records with contact info and balance
invoices	txn_id	Sales invoices
invoice_lines	txn_line_id	Line items on invoices (linked by txn_id)
credit_memos	txn_id	Credit memos
estimates	txn_id	Estimates / quotes
sales_receipts	txn_id	Sales receipts (immediate payment)
sales_orders	txn_id	Sales orders
receive_payments	txn_id	Payments received

Vendors & Payables

Table	Key Column	Description
vendors	list_id	Vendor records
bills	txn_id	Bills from vendors
purchase_orders	txn_id	Purchase orders
bill_payment_checks	txn_id	Bill payments by check
bill_payment_credit_cards	txn_id	Bill payments by credit card

Items & Inventory

Table	Key Column	Description
item_inventories	list_id	Inventory items with quantity on hand
item_non_inventories	list_id	Non-inventory items
item_services	list_id	Service items
item_other_charges	list_id	Other charge items
item_discounts	list_id	Discount items
item_groups	list_id	Group items
item_sales_taxes	list_id	Sales tax items

Banking & General Ledger

Table	Key Column	Description
accounts	list_id	Chart of accounts
checks	txn_id	Checks written
deposits	txn_id	Bank deposits
journal_entries	txn_id	Journal entries
currencies	list_id	Currency definitions

Reference

Table	Key Column	Description
classes	list_id	Class tracking categories
employees	list_id	Employee records
payment_methods	list_id	Payment method types
ship_methods	list_id	Shipping methods
customer_types	list_id	Customer type categories
vendor_types	list_id	Vendor type categories
sales_tax_codes	list_id	Sales tax codes

Use SHOW TABLES; to see the complete list of all 121 tables.

Common Query Patterns

Customer Aging Report

SELECT
    c.full_name,
    c.balance,
    COUNT(i.txn_id) AS open_invoices,
    MIN(i.txn_date) AS oldest_invoice,
    MAX(i.txn_date) AS newest_invoice
FROM customers c
LEFT JOIN invoices i ON i.customer_ref_list_id = c.list_id AND i.is_paid = false
WHERE c.balance > 0
GROUP BY c.full_name, c.balance
ORDER BY c.balance DESC;

Top Selling Items

SELECT
    il.item_ref_full_name,
    COUNT(*) AS times_sold,
    SUM(il.quantity) AS total_qty,
    SUM(il.amount) AS total_revenue
FROM invoice_lines il
GROUP BY il.item_ref_full_name
ORDER BY total_revenue DESC
LIMIT 20;

Unpaid Invoices with Customer Details

SELECT
    i.ref_number,
    i.txn_date,
    i.due_date,
    c.full_name,
    c.phone,
    i.balance_remaining
FROM invoices i
INNER JOIN customers c ON i.customer_ref_list_id = c.list_id
WHERE i.is_paid = false AND i.balance_remaining > 0
ORDER BY i.due_date ASC;

Revenue by Month

SELECT
    DATE_TRUNC('month', txn_date) AS month,
    COUNT(*) AS invoice_count,
    SUM(subtotal) AS gross_revenue,
    SUM(sales_tax_total) AS tax_collected,
    SUM(balance_remaining) AS outstanding
FROM invoices
WHERE txn_date >= '2025-01-01'
GROUP BY month
ORDER BY month;

Inventory Valuation

SELECT
    full_name,
    quantity_on_hand,
    average_cost,
    quantity_on_hand * average_cost AS total_value
FROM item_inventories
WHERE is_active = true AND quantity_on_hand > 0
ORDER BY total_value DESC;

Forcing a Live Query

By default, SELECT queries read from the local cache. To force a live fetch from QuickBooks:

SET QB_MAX_STALENESS = 0;
SELECT * FROM customers WHERE list_id = '80000001-1234567890';

See Special Commands for more details on cache control.