In SQL, this works beautifully:

SELECT 'All' AS RootName, ' All' AS SortValue
UNION
SELECT DISTINCT RootName, RootName AS SortValue
FROM dbo.APP_OnlineReportingRoots
ORDER BY SortValue

It adds the word "All" to both the RootName ans SortValue columns of the
results which I then use for a report parameter.

I have not been able to get it to work with Oracle. Does anyone know

Peter,

add "FROM dual" to the end of your select statement as Oracle does not
support the SELECT statement without a FROM clause.

SELECT 'All' AS RootName, ' All' AS SortValue FROM dual
UNION
SELECT DISTINCT RootName, RootName AS SortValue
FROM dbo.APP_OnlineReportingRoots
ORDER BY SortValue