https://netsuiteprofessionals.com logo
Join SlackCommunities
Powered by
I am confused maybe IN doesn't work like I thought...
# suiteql
s
I am confused maybe IN doesn't work like I thought - I have a transaction search where I'm filtering by type using an 
IN
clause. e.g.
Copy code
sql
select id from transaction where type in ('CashSale', 'CustPymnt')
when I have only CashSale in there, it returns many rows. When I add 'CustPymnt' it returns only one row. I thought an IN clause worked like an OR, so I should only see more results not less when adding arguments to the IN clause?
t
Just out of curiosity, does this work:
Copy code
select ID, type from transaction where type = 'CashSale' or type = 'CustPymnt'
s
that seems to be working- but the search is taking much longer to execute (still waiting on results). I thought 
x IN ('a', 'b')
is supposed to be equivalent to x = 'a' or x = 'b'?
hmm, take that back - 'script execution time exceeded'
am I misunderstanding 
IN
or should I be disturbed that SuiteQL seems to treat 
IN
differently than a series of `OR`s?
hmm, so (t.type = 'CashSale' or t.type = 'CustPymnt') results in 1 result. I do think sometimes SuiteQL returns bogus results.
for example, while not an advisable query, 
select * from transaction
returns 
[]
t
"IN" should work the way that you described. However, SuiteQL seems to do "very odd things" when it comes to queries against the Transaction table. Oh the stories I could tell...
It seems to really need you to specify a transaction type in order to be consistent.
m
Have you tried using 
recordtype
instead of 
type
? I've run across some other performance related weirdness with these columns. https://netsuiteprofessionals.slack.com/archives/C01FBBZ8PQC/p1607561410072200
t
@michoel Interesting. For me, querying on RecordType is no faster than querying on Type.
m
I posted a follow up as well at the time. For some queries I saw that type was significantly faster than type
t
Copy code
-- 3.46 seconds
SELECT DISTINCT type FROM Transaction ORDER BY type

 -- 3.44 seconds
SELECT DISTINCT recordtype FROM Transaction ORDER BY recordtype
m
t
I suspect that we're seeing the results of caching.
m
t
Might as well throw this one into the mix:
Copy code
SELECT DISTINCT abbrevtype FROM Transaction ORDER BY abbrevtype
s
ugh. The promise of SuiteQL is great, but the reality is a mixed bag.
2 Views
Open in Slack
PreviousNext
Powered by