Has anyone noticed that the use of

BUILTIN.DF()

removes results from your queries? I am running a query where I’m left joining together some transactions, expecting some to be blank (e.g. when there is an Opportunity that hasn’t yet gone on to an Estimate or Sales Order). I’m trying to report out the

id

,

tranid

and

status

for the linked transactions. If I just

SELECT

the

status

for each instead of trying to

SELECT BUILTIN.DF(status)

then I get the expected number of results, but using the

BUILTIN.DF

excludes any rows where the status is blank. Here is my SQL

SELECT op.id AS op_id, op.tranid AS op_tranid, BUILTIN.DF(op.status) AS op_status,
est.id AS est_id, est.tranid AS est_tranid, BUILTIN.DF(est.status) AS est_status,
so.id AS so_id, so.tranid AS so_tranid, BUILTIN.DF(so.status) AS so_status

FROM transaction op
LEFT JOIN nexttransactionlinelink ntll_est_op ON ntll_est_op.previousdoc = op.id AND ntll_est_op.nexttype = 'Estimate'
LEFT JOIN transaction est ON ntll_est_op.nextdoc = est.id
LEFT JOIN nexttransactionlinelink ntll_so_est ON ntll_so_est.previousdoc = est.id AND ntll_so_est.nexttype = 'SalesOrd'
LEFT JOIN transaction so ON ntll_so_est.nextdoc = so.id

WHERE op.id IN (
	837263, -- Opportunity Only
	798116, -- Opportunity + Estimate
	678733 -- Opportunity, Estimate, + Sales Order
)

I only get one result with this query, where I should get three (noting that there will be blanks under the est and so columns for some results). If I remove the

BUILTIN.DF

as follows, I get the expected number of results:

SELECT op.id AS op_id, op.tranid AS op_tranid, op.status AS op_status,
est.id AS est_id, est.tranid AS est_tranid, est.status AS est_status,
so.id AS so_id, so.tranid AS so_tranid, so.status AS so_status

FROM transaction op
LEFT JOIN nexttransactionlinelink ntll_est_op ON ntll_est_op.previousdoc = op.id AND ntll_est_op.nexttype = 'Estimate'
LEFT JOIN transaction est ON ntll_est_op.nextdoc = est.id
LEFT JOIN nexttransactionlinelink ntll_so_est ON ntll_so_est.previousdoc = est.id AND ntll_so_est.nexttype = 'SalesOrd'
LEFT JOIN transaction so ON ntll_so_est.nextdoc = so.id

WHERE op.id IN (
	837263, -- Opportunity Only
	798116, -- Opportunity + Estimate
	678733 -- Opportunity, Estimate, + Sales Order
)

yikes, that's kinda nasty

if you wrap the BUILTIN.DF w/ a NVL does it still drop the rows?

Still drops ’em, as does

CASE WHEN op.status IS NULL THEN '' ELSE BUILTIN.DF(op.status) END AS op_status

I don't have an example off the top of my head but YES I have seen

BUILTIN.DF()

omit results.

yikes, thanks for sharing

This is caused by NetSuite using for some tables left and for others right join when BUILTIN.DF function used. It is not documented so I don't use it at all and rather joining the table in a standard way to get the names.