Hi, I'm trying to convert a saved search into an a...
# generals
Synx
05/22/2025, 5:42 PMHi, 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. 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?
s
Shawn Talbert
05/22/2025, 6:15 PMI don't think the SuiteQL schema matches the ODBC schema. If you already know that, it would be useful to share specific errors you're seeing.
b
Bryan Miller
05/22/2025, 7:16 PMDoes your gateway connect successfully?
s
Synx
05/22/2025, 8:16 PMYes it connects successfully. I don't pass a SQL statement, it'll expose the tables but for some reason I can't pass a SQL statement even if its jus something like
Copy code
SELECT
*
FROM
Transaction
WHERE
RowNum <= 10Synx
05/22/2025, 8:23 PMthis is the query I'm using, I don't know how this can be wrong which leads me to believe it's a permissions issue: SELECT
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'
b
Bryan Miller
05/22/2025, 8:57 PMyou are missing a closing ' on : t.type = 'Sales Order' also, I think it should be SalesOrd. ALSO it should be join transactionline tl ON t.id = tl.transaction
Bryan Miller
05/22/2025, 8:58 PMand for the top one I would use SELECT TOP 10 instead of trying to pull the rownum in the WHERE