We are struggling with being able to use GROUP BY and ORDER BY clauses with a UNIONED set that uses identifiers. Is this possible in NetSuite? No matter how I attempt to add those clauses, I am always met with this error message:
Search error occurred: Invalid or unsupported search
In brief, the partial query is as so:
SELECT
p.name as product_group,
0 as quotes_count,
0 as orders_count,
COUNT(t.tranid) as invoices_count,
SUM(line.creditforeignamount) as invoices_total
FROM
...
WHERE
...
UNION
SELECT
p.name as product_group,
COUNT(l.item) as quotes_count,
0 as orders_count,
0 as invoices_count,
0 as invoices_total
FROM
...
WHERE
...
GROUP BY
p.name
ORDER BY
p.name
What I would like to achieve is to be able to group the results by the parent Class (represented by p.name) after UNIONING the two sets. Is there a special syntax for this? Or is it just not supported?
We have already tried wrapping the internal statements with
SELECT * FROM ([INNER SQL]) ORDER BY … GROUP BY …
but that also does not work.