Greetings everyone, I'm trying to convert a saved ...
# suiteqls
Synx
05/22/2025, 6:09 PMGreetings everyone, I'm trying to convert a saved search into an appropriate SuiteQL to import into Power BI via SuiteAnalytics ODBC. I've tried multiple times to do this but I'm constantly receiving syntax errors and I'm suspecting it may not be syntax related. I made sure the DSN is referencing the role where I created the search from so I should have access to the tables. Furthermore I'm suing NetSuite 2.0 and have the DSN set to reference Uppercase schema. I can't even pull in a simple test query on the transaction table. Any suggestions?
j
jen
05/22/2025, 6:47 PMcan you post your SQL?
s
Synx
05/22/2025, 8:22 PMSELECT
t.trandate,
t.periodname,
t.type,
t.tranid,
t.location,
t.entity,
tl.quantity,
tl.amount,
t.status,
tl.item,
t.createddate,
t.custbody_bigcommerceorder,
t.internalid
FROM
transaction t
JOIN transactionline tl
ON t.internalid = tl.transaction
WHERE
t.type = 'Sales Order
AND tl.mainline = 'F'
Synx
05/22/2025, 8:22 PMI don't know how this can be wrong which leads me to believe it's probably a permissions thig
Synx
05/22/2025, 8:24 PMeven just a simple SELECT
*
FROM
Transaction
WHERE
RowNum <= 10 doesn't give me anything
j
jen
05/22/2025, 8:33 PMyou are missing a quote after Sales Order
jen
05/22/2025, 8:33 PMalso, that should be ‘SalesOrd’ not ‘Sales Order’
jen
05/22/2025, 8:34 PMsyntax error is exactly what it says on the tin. An error in your syntax.
s
Synx
05/22/2025, 8:35 PMwhere do you find the name of the table and fields for NetSuite 2.0 also, thank you for being responsive. I've been trying to solve this for 3 days with no luck.
Synx
05/22/2025, 8:38 PMI've been referencing the schema browser online but it looks like it's for the legacy NetSuite and not 2.0 I also referenced this guide https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_156475462055.html#subsect_0325042706 I think this should work for 2.0
j
jen
05/22/2025, 8:45 PMSo I don’t use ODBC, my experience is just straight SuiteQL but for that I use the Records Catalog.
jen
05/22/2025, 8:45 PM(which will include your own custom records)
i
Israel Gonzalez
05/22/2025, 11:50 PMI don't use ODBC either so not familiar with limitations on that. Definetely checkout Tim Dietrich resources on SuiteQL I use his SuiteQL Query Tool for testing. One thing I have found is that there are certain queries that don work using the native REST API but do work when running them through a RESTLet and work on the SuiteQL Query Tool, so you may come across that as well on ODBC.
NetSuite SuiteQL Query Tool
b
Bryan Miller
05/27/2025, 8:25 PMODBC uses the records catalog naming.
Bryan Miller
05/27/2025, 8:27 PMalso: you're join is still wrong
8 Views