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!

September 2022: This comment is from Joseph Lee. He writes:

Hello Kevin, 

You should be able to do something similar to get yours to sum properly. The error I am now experiencing is when I try to do a group by rollup my forecast column only sums the amounts from opportunities and now everything else. I suspect it has to do with my case statement and I’m forced to roll up in the subquery.

Here is Joseph’s code. Thank you Joseph!

WITH 
	SalesSummary AS
		(
			SELECT
				Employee AS RepID,
				Type,
				SUM( ForeignTotal ) AS ForeignTotal,
				SUM( projectedTotal*probability ) AS ProjectedTotal
			FROM
				Transaction
			WHERE
				( ( Type = 'SalesOrd' ) AND ( BUILTIN.CF( Status ) IN ('SalesOrd:B','SalesOrd:A') AND TO_CHAR(shipdate, 'mm') = TO_CHAR(sysdate, 'mm')
 				AND TO_CHAR(shipdate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
				OR ( ( Type = 'Opprtnty' ) AND ( BUILTIN.CF( Status ) IN ('Opprtnty:A','Opprtnty:C') AND TO_CHAR(expectedclosedate, 'mm') = TO_CHAR(sysdate, 'mm')
 				AND TO_CHAR(expectedclosedate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
				OR ( ( Type = 'CustInvc' ) AND ( BUILTIN.CF( Status ) IN ('CustInvc:A','CustInvc:B') AND TO_CHAR(trandate, 'mm') = TO_CHAR(sysdate, 'mm')
				 AND TO_CHAR(trandate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
				OR ( ( Type = 'CashSale' ) AND ( BUILTIN.CF( Status ) IN ('CashSale:C','CashSale:B') AND TO_CHAR(trandate, 'mm') = TO_CHAR(sysdate, 'mm')
 				AND TO_CHAR(trandate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
				OR ( ( Type = 'CustCred' ) AND ( BUILTIN.CF( Status ) IN ('CustCred:B','CustCred:A') AND TO_CHAR(trandate, 'mm') = TO_CHAR(sysdate, 'mm')
 				AND TO_CHAR(trandate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
			GROUP BY
				Employee,
				Type
		)

SELECT
a.RepName,
a.Opportunity_Total, 
a.Sales_Pending_Total, 
a.Invoiced, 
a.Cash_Sale, 
a.Credit_Memo, 
case when (a.Sales_Pending_Total + a.Invoiced + a.Cash_Sale + a.Credit_Memo) < Opportunity_Total
	   then Opportunity_Total
	   else (a.Sales_Pending_Total + a.Invoiced + a.Cash_Sale + a.Credit_Memo)
	   end Forecast
from (
		SELECT
			RepID,
			( Employee.FirstName || ', ' || Employee.LastName ) AS RepName,
			SUM ( CASE WHEN Type = 'Opprtnty' THEN ProjectedTotal ELSE 0 END ) AS Opportunity_Total,
			SUM ( CASE WHEN Type = 'SalesOrd' THEN ForeignTotal ELSE 0 END ) AS Sales_Pending_Total,
			SUM ( CASE WHEN Type = 'CustInvc' THEN ForeignTotal ELSE 0 END ) AS Invoiced,
			SUM ( CASE WHEN Type = 'CashSale' THEN ForeignTotal ELSE 0 END ) AS Cash_Sale,
			SUM ( CASE WHEN Type = 'CustCred' THEN ForeignTotal ELSE 0 END ) AS Credit_Memo,
			


		FROM
			SalesSummary
			INNER JOIN Employee ON
				( Employee.ID = SalesSummary.RepID )
		WHERE
			RepID IN ('17','24','-5','26')	
		GROUP BY
			RepID,
			( Employee.FirstName || ', ' || Employee.LastName )
		ORDER BY
			RepName
	) a