need help - list of 'constant' values in a join

UDA
Enthusiast

need help - list of 'constant' values in a join

Dear all,

i need to join a list of values with real table, like

sel a1, a2, b2
from tab1
inner join
(b1-1, b2-2
b1-2, b2-2
) tab2
on a1=b1

My problem is the 'definition' of tab2.
The 'normal' Teradata sql for tab2:
select b1-1, b2-1
works fine - for only one pair of values.
If i use union to have more than one pair of values, it needs a 'from' and a table name - because of the union.
So i would need sg. like the DB2 sysdummy1 table - or another solution.
I definitely can't create tab2 in the environment.

4 REPLIES
Enthusiast

Re: need help - list of 'constant' values in a join

Hi,
You can create volatile table to store these values and then use in your query.
Senior Apprentice

Re: need help - list of 'constant' values in a join

There's a workaround:

select *
from
(select * from (select 1 as b1, 2 as b2) x
union all
select * from (select 3 as b1, 4 as b2) x
) dt

But in your example the join could be easily replaced with a CASE like:

sel a1, a2,
case a1
when 1 then 2
when 3 then 4
end
from tab1

Dieter
Enthusiast

Re: need help - list of 'constant' values in a join

Dear Rupesh, Dieter,

thx for your answers, actually i wanted to have Dieters solution - and it just works so fine!
Dieter, i didn't want to bother you on your private email...

br from WIE/BUD, 2008 okt 27-30 :)
Senior Apprentice

Re: need help - list of 'constant' values in a join

Hi Banán,
aaah, WAVE, i remember that :-)
You can always write me directly, i usually try to answer...

Dieter