1

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

Nic2352
  • 95
  • 8

2 Answers2

3

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.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Can you explain what the outkey, tokennum and token are used for? (I guess that the token is the actual value but can this be renamed?) Can the outkey and tokennum be excluded from the RETURN statement? Can this be used for a multiple column table? – Nic2352 Jan 05 '15 at 16:50
  • I called it *token*, you can name it whatever you want. No, you can't exclude *outkey* and *tokennum* from RETURN. No, this is only for a single column *inkey*. – dnoeth Jan 05 '15 at 20:06
0

Try this:

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

It should work in Teradata as well as in MySql.

Nicola Ferraro
  • 4,051
  • 5
  • 28
  • 60
  • Teradata... My main reason for asking this question is that once I have created this list I want to be able to join it to an actual table – Nic2352 Dec 31 '14 at 10:47
  • The query above (without from) should work also in teradata. If it is possible, think about avoiding the join and filling in the missing dates with a for loop in your code. – Nicola Ferraro Dec 31 '14 at 10:51
  • I am not able to avoid the Join and this is a query for MI purposes rather than for any coding use so I won't be able to supplement any missing dates with a for loop unless this is something available in teradata – Nic2352 Dec 31 '14 at 10:54