CODEWITHSUNDEEP
teradata
Nic2352
Nic2352

Reputation: 95

Query a manual list of data items

I would like to run a query involving joining a table to a manually generated list but am stuck trying to generate the manual list. There is an example of what I am attempting to do below:

SELECT
    *
FROM
    ('29/12/2014', '30/12/2014', '30/12/2014') dates
;

Ideally I would want my output to look like:
29/12/2014
30/12/2014
31/12/2014

Upvotes: 1

Views: 615

Answers (2)

dnoeth
dnoeth

Reputation: 60472

What's your Teradata release?

In TD14 there's STRTOK_SPLIT_TO_TABLE:

SELECT * 
FROM TABLE (STRTOK_SPLIT_TO_TABLE(1 -- any dummy value
                                 ,'29/12/2014,30/12/2014,30/12/2014' -- any delimited string
                                 ,',' -- delimiter
           )
     RETURNS (outkey INTEGER
             ,tokennum INTEGER
             ,token VARCHAR(20) CHARACTER SET UNICODE) -- modify to match the actual size
             ) AS d

You can easily put this in a Derived Table and then join to it.

inkey (here the dummy value 1) is a numeric or string column, usually a key. Can be used for joining back to the original row.

outkey is the same as inkey.

tokennum is the ordinal position of the token in the input string.

token is the extracted substring.

Upvotes: 3

Nicola Ferraro
Nicola Ferraro

Reputation: 4189

Try this:

select '29/12/2014' union select '30/12/2014' union ...

It should work in Teradata as well as in MySql.

Upvotes: 0

Related Questions