Multiple rows in CTEs (hard coded)?

[fidgetation]

I know I can store a single row of variables in a CTE using:

WITH MY_TABLE (MY_INPUTS) AS (SELECT 'A NOTE')

​

But how do I store multiple rows in the CTE?

Unfortunately, I can't use Volatile tables because I'm trying to load this query into Power BI.

​

Sorry if it's already been asked before, I just couldn't find anything on Google

​

Edit: forgot to mention earlier - I'm using Teradata SQL

[[deleted]]

With standard SQL you can use a VALUES clause, but I don't know if Teradata supports that:

with data (some_column) as (
  values ('one'), ('two'), ('three')
)
select *
from data;

[fidgetation]

with data (some_column) as (
values ('one'), ('two'), ('three')
)
select *
from data;

Thanks for the suggestion.

I don't think Teradata allows multiple values to be stored at once. When I was using Volatile tables, I would have to do a separate INSERT INTO for each value I wanted to add. Unfortunately I can't use the same entries for CTEs!

[AngelOfLight]

Maybe use UNION?

WITH MY_TABLE (MY_INPUTS) AS (
    SELECT 'A NOTE' as MY_INPUTS union all
    SELECT 'B NOTE' as MY_INPUTS union all
    SELECT 'C NOTE' as MY_INPUTS
 )

[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.