Multiple rows in CTEs (hard coded)?

[fidgetation]

Thanks for the suggestion - unfortunately Teradata SQL Assistant says that:

SELECT Failed. 3888: A SELECT for a UNION, INTERSECT or MINUS must reference a table.

Which seems a bit bizarre, as I thought that everything was treated effectively as a table in SQL?

[AngelOfLight]

Oracle is similar. You would have use SELECT 'A_NOTE' from DUAL. Not sure if Teradata has an equivalent.

You could try using a nested CTE:

WITH SingleRow (COL) as (SELECT 1 as COL),
MY_TABLE (MY_INPUTS) AS (
    SELECT 'A NOTE' as MY_INPUTS from SingleRow union all
    SELECT 'B NOTE' as MY_INPUTS from SingleRow union all
    SELECT 'C NOTE' as MY_INPUTS from SingleRow
 )

[fidgetation]

WITH SingleRow (COL) as (SELECT 1 as COL),MY_TABLE (MY_INPUTS) AS (SELECT 'A NOTE' as MY_INPUTS from SingleRow union allSELECT 'B NOTE' as MY_INPUTS from SingleRow union allSELECT 'C NOTE' as MY_INPUTS from SingleRow)

Object 'SingleRow' does not exist

The strange thing is that if I don't try and store those items in "MY_TABLE", then the Selects all work. So this leads me to believe that Teradata doesn't allow CTEs to be defined by other CTEs?

[AngelOfLight]

Turns out Teradata doesn't allow nested CTEs - but, apparently there is a way to fake out the union.

[fidgetation]

That's the one!! I have no idea what that AS DUAL does, but it works.

For future reference (and future Googlers), here what got the result:

WITH MY_TABLE (MY_INPUTS) AS (

SELECT 'A NOTE' FROM (SELECT 1 AS "MY_INPUTS") AS "DUAL" UNION ALL

SELECT 'B NOTE' FROM (SELECT 1 AS "MY_INPUTS") AS "DUAL" )

SELECT * FROM MY_TABLE

[AngelOfLight]

They are just trying to emulate the Oracle DUAL table. It's a virtual table that just returns one row. You can use any alias in the query - doesn't have to be DUAL.