https://netsuiteprofessionals.com logo
Join SlackCommunities
Powered by
*Just a `warning` for those who are using `BUILTIN...
# suiteql
c
Just a 
warning
for those who are using 
BUILTIN.DF()
 In some but not all instances the builtin.DF function will force an inner join to the lookup table, NOT an outer join. I can’t find a good explanation for why/when it performs the inner join, but when you are dealing with records which may have null values in a foreign key field, be sure to test whether it is actually performing an inner or outer join to get the lookup value! I just wrecked my morning trying to troubleshoot why transaction lines were missing from my report. This was the cause. EXAMPLE: Records with no items are still returned. An outer join is performed to get the lookup value. 
select
item,
BUILTIN.DF(item)
from transactionLine
This time it is using an INNER join. Records with no link type fall off the report. 
select
pl.linktype,
BUILTIN.DF(pl.linktype) link_type_name
from transactionLine l
left outer join PreviousTransactionLineLink pl
on pl.nextdoc = l.transaction
AND pl.nextline = l.id
@tdietrich
t
@Cory Weiner Yeah, this is definitely weird, and it feels like a bug to me. See my findings below.
Copy code
-- BUILTIN.DF not used.
-- Returns 3348268 rows.
SELECT COUNT(*) FROM (
select 
pl.linktype,
from transactionLine l
left outer join PreviousTransactionLineLink pl
on pl.nextdoc = l.transaction
AND pl.nextline = l.id
)


-- BUILTIN.DF not used, pl.linktype is not null.
-- Returns 3348270 rows.
SELECT COUNT(*) FROM (
select 
pl.linktype,
from transactionLine l
left outer join PreviousTransactionLineLink pl
on pl.nextdoc = l.transaction
AND pl.nextline = l.id
AND pl.linktype IS NOT NULL
)


-- BUILTIN.DF not used, pl.linktype is null.
-- Returns 2771463 rows.
SELECT COUNT(*) FROM (
select 
pl.linktype,
from transactionLine l
left outer join PreviousTransactionLineLink pl
on pl.nextdoc = l.transaction
AND pl.nextline = l.id
AND pl.linktype IS NULL
)


-- BUILTIN.DF used.
-- Returns 1317804 rows.
SELECT COUNT(*) FROM (
select 
pl.linktype,
BUILTIN.DF(pl.linktype) link_type_name
from transactionLine l
left outer join PreviousTransactionLineLink pl
on pl.nextdoc = l.transaction
AND pl.nextline = l.id
)


-- BUILTIN.DF used w/ CASE.
-- Returns 1317803 rows.
SELECT COUNT(*) FROM (
select 
pl.linktype,
CASE WHEN pl.linktype IS NULL THEN NULL ELSE BUILTIN.DF(pl.linktype) END AS link_type_name
from transactionLine l
left outer join PreviousTransactionLineLink pl
on pl.nextdoc = l.transaction
AND pl.nextline = l.id
)

-- BUILTIN.DF used w/ COALESCE.
-- Returns 1317804 rows.
SELECT COUNT(*) FROM (
select 
pl.linktype,
COALESCE( BUILTIN.DF(pl.linktype), 'n/a' ) AS link_type_name
from transactionLine l
left outer join PreviousTransactionLineLink pl
on pl.nextdoc = l.transaction
AND pl.nextline = l.id
)

-- BUILTIN.DF used w/ COALESCE as argument.
-- Fails
SELECT COUNT(*) FROM (
select 
pl.linktype,
BUILTIN.DF( COALESCE( pl.linktype, 0 ) ) AS link_type_name
from transactionLine l
left outer join PreviousTransactionLineLink pl
on pl.nextdoc = l.transaction
AND pl.nextline = l.id
)
I've often wondered how BUILTIN.DF (and the CF version as well) are really implemented under the hood. I don't think they're JOINed to, but instead are implemented as a heavily optimized / cached subquery. And I've seen similar weird behavior from them in certain queries. It would be easy to say "don't use BUILTIN.DF," and instead take the time to add a join. But in this case, I don't know what table you'd join to. There are times when BUILTIN.DF can do some very helpful things. As an example, take a look at this post - https://timdietrich.me/blog/netsuite-suiteql-shipments/ - and in particular how you can use BUILTIN.DF with Transaction.TrackingNumberList to easily get tracking numbers for an Item Fulfillment. All that being said (and sorry for this epic response), I wonder if someone who has the ability to do so can report this as a defect?
👍 2
s
We should probably start keeping a list of netsuite defects (open sourced from this slack channel) we might be surprised on how often netsuite actually resolves those defects (and how long it takes...)
👍 1
436 Views
Open in Slack
PreviousNext
Powered by