I am trying to use CTEs. I have two CTEs and I want to union them. Will that work. (Picture in reply). I get an error.

@Alan Fitch Try wrapping the UNIONs in another (outer) SELECT. Like this:

WITH 
	Temp1 AS ( SELECT 'Y' AS Field1 FROM Dual ),
	Temp2 AS ( SELECT 'X' AS Field1 FROM Dual ),
	Temp3 AS ( SELECT 'A' AS Field1 FROM Dual )
SELECT * FROM (
	SELECT Field1 FROM Temp1
	UNION
	SELECT Field1 FROM Temp2
	UNION
	SELECT Field1 FROM Temp3
) ORDER BY Field1

This works. Do you know why?

I think the layer of abstraction that SuiteQL adds to the query causes an issue, and specifically one where it can't resolve the "WITH" clause with multiple SELECTs.