Beware: SuiteSQL – SUM() function is a loser!

I don’t know if this is an Oracle issue or something related to NetSuite. I come from a Microsoft SQL server world where queries like this work. Perhaps there’s a way to do this in PL/SQL, but I have not found it.

It is possible this query works outside of NetSuite, but it does not work in SuiteSQL. I’ve noticed other problems rolling up data in NetSuite, even in Saved Searches. Maybe that’s related.

I’ll add that I know from attending sessions at SuiteWorld that NetSuite is in the process of modifying their provisions such that each customer will have their own database. I’m unclear where they are in that process. But at the last SuiteWorld I attended, all queries were being modified to include an additional filter adding a customer’s account number. This was explained to me as all tables included data for all customer accounts in a common database. So NetSuiteCustomer#1 and NetSuiteCustomer#2 both submit a query to list their customers, “Select * from customers“. NetSuite will modify this to be “Select * from customers where NetSuiteCustomerID = [Your NetSuite Account Number]“. Perhaps this explains what you see below.

If you have a solution to this issue, feel free to send it to me. I’ll repost it and credit you. But wait! Before you rewrite my query, thinking you’ve found my flawed reasoning…

This doesn’t work either…

AND… Removing the NVL wrapper around my raw data column… Uh no! That doesn’t work either.

Best of luck to you if you decide to take on this problem. I ended up solving it by pulling the raw data (omitting the SUM() function altogether) and rolling it up in Excel.

Happy Programming!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s