Archives of the TeradataForum

Message Posted: Sat, 21 Mar 2003 @ 16:04:51 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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.

God Luck

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>