translate join on two conditions to a select statement

Database
Enthusiast

translate join on two conditions to a select statement

table emp has three columns:

ID, Rep_Date, Sales

I want to find sales for all rows where ID and Date matches my list "

1, 2011-01-01

4,2011-04-01

6, 2011-09-02

Where table emp has following rows:

1, 2011-01-01 , 100

4,2011-04-01 , 100

6, 2011-09-02 , 100

1, 2011-12-01, 80

4,2011-12-01, 80

6, 2011-12-02, 90

My result should only have first three rows returned. It can be easily achieved using a join condition if the second set of values were inserted in a temp table but i do not have the righ to do so.

15 REPLIES
Senior Apprentice

Re: translate join on two conditions to a select statement

select * from emp
where
(ID=1 AND Rep_Date = '2011-01-01')
or
(ID=4 AND Rep_Date = '2011-04-01')
or
(ID=6 AND Rep_Date = '2011-09-02')

Of course it would be much easier, if Teradata supported "Row Value Constructors":

where (ID, Rep_Date) in
((1, '2011-01-01'),
(4, '2011-04-01'),
(6, '2011-09-02'))

Dieter

Enthusiast

Re: translate join on two conditions to a select statement

Thanks Dieter.

I was looking for a Terdata version of "Row Value Constructor".

What about concatenating the two values and matching it with concatenated column? Would that work?

Senior Apprentice

Re: translate join on two conditions to a select statement

Of course, it works, but

- there's a huge overhead for the neccessary typecasts and concats

- it would prevent the optimizer from using an index

- existing statistics couldn't be used anymore

Dieter

Enthusiast

Re: translate join on two conditions to a select statement

Yes, that is true. Thank you for your prompt response. I will try getting access to create a temp table ad do a join since i have many id's that i need to lookup.

Senior Apprentice

Re: translate join on two conditions to a select statement

Did you try creating a Volatile table?

DBAs usually don't revoke this right form end users.

Dieter

Enthusiast

Re: translate join on two conditions to a select statement

Thanks Dieter. I was able to create a temp table after getting the privleges from the dbs. but will try the volatile table next time.

Another issue , you can probably help me with.

two tables,

Table A: empid,comp_cd 

Table B:empid, deptid,start_dt, End_dt

I want to get all records from A where comp_cd in (1,2) and corresponding records from B where End_dt is blank.

I tried

select *

from A left outer join B

where A.comp_id in (2,3)

and B.End_dt is null

This only returns the matching rows and not all rows from A with the matching condition. What is it that I am doing wrong here?

Enthusiast

Re: translate join on two conditions to a select statement

is End_dt on tableB is populated with blanks? If thats the case you can't use your query.

you can try

sel * from A left outer join

(sel * from B where End_dt is null) B

where A.comp_id in (2,3)

Senior Apprentice

Re: translate join on two conditions to a select statement

Your SQL wil return an error as the ON clause is missing.

The condition on the inner table should be in the ON:

select *

from A left outer join B ON "join condition" AND B.End_dt is null

where A.comp_id in (2,3)

Dieter

Enthusiast

Re: translate join on two conditions to a select statement

Thanks Diether..

I jus copied frm previous one.Forgot to add the ON condition.