Has anyone been able to work with the UNION ALL operator and with common table expressions? I've used these separately successfully - though they are not playing nice together. This works:

WITH CTE AS (
    SELECT 'abc' AS test FROM dual
)

SELECT test FROM CTE

This works:

SELECT 'abc' AS test FROM dual

UNION ALL

SELECT 'abc' AS test FROM dual

This works:

WITH CTE AS (
    SELECT 'abc' AS test 
UNION ALL
    SELECT 'abc' AS test
)

SELECT test FROM CTE

This does not:

WITH CTE AS (
    SELECT 'abc' AS test FROM dual
)

SELECT test FROM CTE

UNION ALL

SELECT test FROM CTE

SQL is not my strongest - but it's getting a bit frustrating. Is this a weird netsuite thing? or am i doing sql wrong?

It's a weird NetSuite thing. You need to wrap it with a

FROM

. e.g.:

WITH
companies AS (
  SELECT id, externalid FROM entity WHERE isperson = 'F'
),
people AS (
  SELECT id, externalid FROM entity WHERE isperson = 'T'
)
SELECT id, externalid FROM (
  SELECT id, externalid FROM companies UNION
  SELECT id, externalid FROM people
)

@Shai Coleman Thanks - that helps.