I am not so good with SQL Can someone confirm if this is not NetSuite Professionals #suiteql

I am not so good with SQL. Can someone confirm if ...

NickSuite

09/20/2021, 8:05 PM

I am not so good with SQL. Can someone confirm if this is not a valid statement?

Copy code

SELECT id, custbody_url FROM transaction WHERE recordtype is INVOICE AND mainline is true AND custbody_url is null AND custbodyc_sftransid is NOT NULL FETCH FIRST 15 ROWS ONLY

Clay Roper

09/20/2021, 8:17 PM

A few notes: • The SuiteQL data model doesn't use "mainline" in the

transaction

table. The

transaction

table gives you header / main line fields, whereas the

transactionline

table gives you access to transaction item lines. You can remove any reference to mainline. • recordtype needs to be a string, and it is very likely something like "CustInvc" for a customer invoice (off the top of my head) so

recordtype = 'CustInvc'

• To limit number of results in a SuiteQL query, you need to add a

RowNum

comparison to your WHERE clause - e.g.

RowNum <= 15

NickSuite

09/21/2021, 4:16 AM

Unknown identifier CustInvc. Available identifiers are: {transaction=transaction} Updated query: SELECT id, custbody_url FROM Transaction WHERE Transaction.Type = "CustInvc" AND custbody_url is null AND custbodyc_sftransid is NOT NULL AND RowNum <= 15;

NickSuite

09/21/2021, 4:38 AM

Tried this as well:

Copy code

SELECT id, custbodytrans_url FROM Transaction WHERE TRANSACTION.TYPE IN ("custinvc") AND custbodytrans_url is null AND custbodyc_sftransid is NOT NULL AND RowNum <= 15;

NickSuite

09/21/2021, 5:08 AM

Copy code

SELECT id, custbodytrans_url FROM Transaction WHERE (TRANSACTION.TYPE) in ('CUSTINVC');

Getting 0 results.

tdietrich

09/21/2021, 6:22 AM

@NickSuite This might help:

tdietrich

09/21/2021, 6:22 AM

Copy code

SELECT TOP 15
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Status ) AS Status,
	BUILTIN.DF( Transaction.Entity ) AS Entity
FROM
	Transaction
WHERE
	( Type = 'CustInvc' )

tdietrich

09/21/2021, 6:24 AM

Also, there's a blog post here that might get you started with querying for transactions, transaction lines, etc. It also discusses SuiteQL's support for "mainline": https://timdietrich.me/blog/netsuite-suiteql-querying-transactions/

NickSuite

09/21/2021, 10:34 AM

Thanks @tdietrich for the blog and query. @battk helped me identify the issue with the "CustInvc" and how it should be written. But I will go through your blog.

4 Views

Open in Slack

Previous Next