SELECT VALUES in Teradata

Question

I know that it's possible in other SQL flavors (T-SQL) to "select" provided data without a table. Like:

SELECT *
FROM (VALUES (1,2), (3,4)) tbl

How can I do this using Teradata?

@DmitryBychenko Executed as Single statement. Failed [3888 : 42000] A SELECT for a UNION,INTERSECT or MINUS must reference a table. STATEMENT 1: Select Statement failed. — Peter Smit, Nov 28 '17 at 13:30
Gordon showed you the strange syntax, but why do you actually need it? — dnoeth, Nov 28 '17 at 13:39
Mainly to create a small self-contained dataset on the fly for another question. In a production environment / ETL, you probably want to store all information in tables. One advantage of storing a small set of tuples like this a larger query is that you keep it in source control easily. Another advantage is that you can use CURRENT_DATETIME in this virtual table, eliminating the need to update a physical table in some cases. — Peter Smit, Nov 28 '17 at 13:47

score 7 · Answer 1 · answered Nov 28 '17 at 13:24

7

Teradata has strange syntax for this:

select t.*
from (select * from (select 1 as a, 2 as b) x
      union all
      select * from (select 3 as a, 4 as b) x
     ) t;

answered Nov 28 '17 at 13:24

Gordon Linoff

1,242,037
58
646
786

2

Whoa, lot's of nesting. But it runs. – Peter Smit Nov 28 '17 at 13:31

ravioli · Accepted Answer · 2017-11-29T18:35:59.340

I don't have access to a TD system to test, but you might be able to remove one of the nested SELECTs from the answer above:

select x.*
from (
  select 1 as a, 2 as b
  union all
  select 3 as a, 4 as b
) x

If you need to generate some random rows, you can always do a SELECT from a system table, like sys_calendar.calendar:

SELECT 1, 2
FROM sys_calendar.calendar
SAMPLE 10;

Updated example:

SELECT TOP 1000 -- Limit to 1000 rows (you can use SAMPLE too)
    ROW_NUMBER() OVER() MyNum, -- Sequential numbering
    MyNum MOD 7, -- Modulo operator
    RANDOM(1,1000), -- Random number between 1,1000
    HASHROW(MyNum) -- Rowhash value of given column(s)
FROM sys_calendar.calendar; -- Use as table to source rows

A couple notes:

make sure you pick a system table that will always be present and have rows
if you need more rows than are available in the source table, do a UNION to get more rows
you can always easily create a one-column table and populate it to whatever number of rows you want by INSERT/SELECT into it:

CREATE DummyTable (c1 INT); -- Create table
INSERT INTO DummyTable(1); -- Seed table
INSERT INTO DummyTable SELECT * FROM DummyTable; -- Run this to duplicate rows as many times are you want

Then use this table to create whatever resultset you want, similar to the query above with sys_calendar.calendar.

I don't have a TD system to test so you might get syntax errors...but that should give you a basic idea.

I'm getting the following error when I run this: Failed [3807 : 42S02] Object 'sys_calendar' does not exist. Using sys_calendar.calendar works, but in this example, the resultset is 10 rows with the same values. — Peter Smit, Nov 29 '17 at 09:54
Removing the layer of nesting doesn't work. I'm getting the following error: Failed [3888 : 42000] A SELECT for a UNION,INTERSECT or MINUS must reference a table. — Peter Smit, Nov 29 '17 at 09:55
Yes, sys_calendar.calendar is one system table you can use. You can use this table as a row-generator and then create whatever data you want in those rows. See my updated example above. — ravioli, Nov 29 '17 at 10:18

score 0 · Answer 3 · answered Jul 02 '20 at 12:04

0

I am a bit late to this thread, but recently got the same error.

I solved this by simply using

select distinct 1 as a, 2 as b from DBC.tables
union all 
select distinct 3 as a, 4 as b from DBC.tables

Here, DBC.tables is a DB backend table with a few rows only. So, the query runs fast as well

answered Jul 02 '20 at 12:04

Adhishwar Singh Mittal

189
1
6

This is really bad, as dbc.Tables is a complex view returning lots of rows. Better use a single-row Select like `select 1 as a from sys_calendar.calkendar where calendar_date = current_date` (could be implemented as a view) – dnoeth Jul 02 '20 at 21:29
Yes you're right. The calendar table would be even better. The DBC views would still be complex even through the scale of computation would be much more to make such a trivial change like was in my case. But kudos to your insight :) – Adhishwar Singh Mittal Jul 06 '20 at 12:18

SELECT VALUES in Teradata

3 Answers3