Archives of the TeradataForum
Message Posted: Sat, 21 Mar 2003 @ 16:04:51 GMT
Why not just load your values into a table?
If you don't want to load them into a real table "Load" them into a sub query. You were very close in your example:
select xxx From sometable where ( value1, value2, value3 ) in (select 11, 12, 13 union all select 21, 22, 23 union all select 31, 32, 33 etc etc etc etc ) HOWEVER, IT HAS TO BE WRITTEN LIKE THIS: select xxx From sometable where ( value1, value2, value3 ) in (select 11, 12, 13 from (select 1) as dt (c1) union all select 21, 22, 23 from (select 1) as dt (c1) union all select 31, 32, 33 etc etc etc etc )
Why the from clause? Because to use the union a table has to be referenced in each of the queries being unioned? Why, I have no idea, it just does - there may be an explanation in the manual or the ANSI SQL specs, but I shall leave it to you to research that.
NB: Since a table must be referenced, you may soon run into the hard limit of the number of tables in a query (even though they are derived tables). I think the limit in V2R4 is 64 tables per query.
BTW. I would still recommend you load the values into a table - if you need different sets of values for different users consider using a view with a filter on the user name, a volatile table, a global temporary table or even a derived table.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|