Contact Us
A person standing at a whiteboard while two men watch attentively.

Context

I was running a SuiteQL query and not seeing my expected transaction in the results. When I ran a stripped-down version of my query with only a few columns in the SELECT, I was getting more results. I narrowed it down to one of the BUILTIN.DF function calls causing the error.

What I Found

Two discoveries:

In NetSuite’s flavor of SQL, BUILTIN.DF fails to evaluate NULL values and NetSuite drops the row from the results entirely.

I tried guarding against this by wrapping the BUILTIN.DF functions in CASE statements so BUILTIN.DF would only be called if the column value was IS NOT NULL. This led to the second discovery:

Apparently NetSuite evaluates all BUILTIN.DF function calls eagerly, before any CASE logic.

No matter how I rearranged the logic in CASE statements, the query always dropped results where BUILTIN.DF was being called on a NULL value.

Using Explicit Lookups

If you suspect your query (or a JOIN in your query) might return NULL for a field, replace the BUILTIN.DF functions with explicit lookups to retrieve the display value.

Original and Revised Queries

Original version (drops some results)

-- t_0 is the Bank transaction
-- t_1 is the accrual transaction (e.g. bill, invoice) the bank transaction is applied to
-- t_2 is the receipt transaction associated with the accrual transaction
SELECT 
  t_0.tranid as bankTrans_tranid,
  t_0.trandate as bankTrans_date,
  BUILTIN.DF(t_0.type) as bankTrans_type,
  BUILTIN.DF(tal_0.account) as bankTrans_account,
  tl_0.memo as bankTrans_memo,
  tl_0.netamount as bankTrans_amount,
  BUILTIN.DF(tl_0.entity) as entity,
  t_1.tranid as appliedTo_tranid,
  BUILTIN.DF(t_1.type) as appliedTo_type,
  BUILTIN.DF(tl_1.department) as department,
  BUILTIN.DF(tal_1.account) as appliedTo_account,
  tl_1.netamount as appliedTo_amount,
  BUILTIN.DF(tl_2.item) as appliedTo_item,
  t_2.tranid as receipt_tranid,
  tl_2.netamount as inventory_amount,
  BUILTIN.DF(tal_2.account) as inventory_account
FROM 
  transaction t_0
-- Get accounting details for bank transaction
INNER JOIN transactionLine tl_0 ON tl_0.transaction = t_0.id
INNER JOIN transactionAccountingLine tal_0 ON tal_0.transactionline = tl_0.id 
  AND tal_0.transaction = t_0.id
-- Get accrual transaction(s) the bank transaction is being applied to
LEFT JOIN previousTransactionLink ptl_0 ON ptl_0.nextdoc = t_0.id 
LEFT JOIN transaction t_1 ON t_1.id = ptl_0.previousdoc
LEFT JOIN transactionLine tl_1 ON tl_1.transaction = t_1.id
  AND tl_1.mainline != 'T'
LEFT JOIN transactionAccountingLine tal_1 ON tal_1.transactionline = tl_1.id 
  AND tal_1.transaction = t_1.id
-- Get line detail of Item Receipts the accrual transaction is linked to
LEFT JOIN previousTransactionLineLink ptll_1 ON ptll_1.nextdoc = tl_1.transaction
  AND ptll_1.nextline = tl_1.id
  AND ptll_1.linktype = 'BillRcpt'
  AND ptll_1.discount = 'F'
LEFT JOIN transaction t_2 ON t_2.id = ptll_1.previousdoc
LEFT JOIN transactionLine tl_2 ON tl_2.transaction = ptll_1.previousdoc 
  AND ptll_1.previousline = tl_2.id
LEFT JOIN transactionAccountingLine tal_2 ON tal_2.transaction = tl_2.transaction
  AND tal_2.transactionline = tl_2.id
WHERE 
  -- Modify the date range of the Bank transaction here
  t_0.trandate >= BUILTIN.RELATIVE_RANGES('FHBL', 'END', 'DATETIME_AS_DATE')
  AND t_0.type IN ('VendCred', 'VendPymt', 'CashRfnd', 'VPrep', 'VPrepApp', 'Journal') -- Defines the list of purchase-related bank transactions.
  AND tal_0.accounttype = 'Bank'
ORDER BY 
  t_0.trandate DESC, 
  t_0.id

Revised Example (avoids BUILTIN.DF)

-- t_0 is the Bank transaction
-- t_1 is the accrual transaction (e.g. bill, invoice) the bank transaction is applied to
-- t_2 is the receipt transaction associated with the accrual transaction
SELECT 
  t_0.tranid as bankTrans_tranid,
  t_0.trandate as bankTrans_date,
  t_0.type as bankTrans_type,
  acct_0.fullname as bankTrans_account,
  sub_0.name as subsidiary,
  tl_0.memo as bankTrans_memo,
  tl_0.netamount as bankTrans_amount,
  curr_0.symbol as currency,
  ent_0.entityid as entity,
  t_1.tranid as appliedTo_tranid,
  t_1.type as appliedTo_type,
  dept_1.name as department,
  acct_1.fullname as appliedTo_account,
  tl_1.netamount as appliedTo_amount,
  item_2.fullname as appliedTo_item,
  t_2.tranid as receipt_tranid,
  tl_2.netamount as inventory_amount,
  acct_2.fullname as inventory_account
FROM 
  transaction t_0
-- Get accounting details for bank transaction
INNER JOIN transactionLine tl_0 ON tl_0.transaction = t_0.id
INNER JOIN transactionAccountingLine tal_0 ON tal_0.transactionline = tl_0.id 
  AND tal_0.transaction = t_0.id
-- Lookup tables for t_0 / tl_0
INNER JOIN account acct_0 ON acct_0.id = tal_0.account
INNER JOIN subsidiary sub_0 ON sub_0.id = tl_0.subsidiary
INNER JOIN currency curr_0 ON curr_0.id = t_0.currency
LEFT JOIN entity ent_0 ON ent_0.id = tl_0.entity
-- Get accrual transaction(s) the bank transaction is being applied to
LEFT JOIN previousTransactionLink ptl_0 ON ptl_0.nextdoc = t_0.id 
LEFT JOIN transaction t_1 ON t_1.id = ptl_0.previousdoc
LEFT JOIN transactionLine tl_1 ON tl_1.transaction = t_1.id
  AND tl_1.mainline != 'T'
LEFT JOIN transactionAccountingLine tal_1 ON tal_1.transactionline = tl_1.id 
  AND tal_1.transaction = t_1.id
-- Lookup tables for t_1 / tl_1
LEFT JOIN department dept_1 ON dept_1.id = tl_1.department
LEFT JOIN account acct_1 ON acct_1.id = tal_1.account
-- Get line detail of Item Receipts the accrual transaction is linked to
LEFT JOIN previousTransactionLineLink ptll_1 ON ptll_1.nextdoc = tl_1.transaction
  AND ptll_1.nextline = tl_1.id
  AND ptll_1.linktype = 'BillRcpt'
  AND ptll_1.discount = 'F'
LEFT JOIN transaction t_2 ON t_2.id = ptll_1.previousdoc
LEFT JOIN transactionLine tl_2 ON tl_2.transaction = ptll_1.previousdoc 
  AND ptll_1.previousline = tl_2.id
LEFT JOIN transactionAccountingLine tal_2 ON tal_2.transaction = tl_2.transaction
  AND tal_2.transactionline = tl_2.id
-- Lookup tables for tl_2 / tal_2
LEFT JOIN item item_2 ON item_2.id = tl_2.item
LEFT JOIN account acct_2 ON acct_2.id = tal_2.account
WHERE 
  -- Modify the date range of the Bank transaction here
  t_0.trandate >= BUILTIN.RELATIVE_RANGES('FHBL', 'END', 'DATETIME_AS_DATE')
  AND t_0.type IN ('VendCred', 'VendPymt', 'CashRfnd', 'VPrep', 'VPrepApp', 'Journal')
  AND tal_0.accounttype = 'Bank'
ORDER BY 
  t_0.trandate DESC, 
  t_0.id 

Related Articles

How to Save SuiteQL Queries

Saving a SuiteQL Query is a built-in feature of Tim Dietrich’s SuiteQL Query Tool. Enabling this functionality requires a few simple customizations: you'll need to create a folder in the File Cabinet and make a minor edit to the SuiteQL Query Tool script. The steps to set this up are outlined below.

Read the Article A person holding a white coffee cup before a plant.

Tagged with Troubleshooting, Training