All Forums Database
sm19 6 posts Joined 05/11
27 Jan 2012
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.

dnoeth 4628 posts Joined 11/04
27 Jan 2012
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

Dieter

sm19 6 posts Joined 05/11
27 Jan 2012

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?

dnoeth 4628 posts Joined 11/04
27 Jan 2012

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

Dieter

sm19 6 posts Joined 05/11
27 Jan 2012

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.

dnoeth 4628 posts Joined 11/04
27 Jan 2012

Did you try creating a Volatile table?

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

Dieter

Dieter

sm19 6 posts Joined 05/11
13 Feb 2012

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?

 

13 Feb 2012

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)

dnoeth 4628 posts Joined 11/04
13 Feb 2012

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

Dieter

14 Feb 2012

Thanks Diether..

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

sm19 6 posts Joined 05/11
28 Feb 2012

Thanks everyone.

What if I want to get the records from B which qualify this condition

QUALIFY RANK() OVER (ORDER BY End_dt DESC) =1.

If i add it to the end of the query the left outer join loses its meaning. how can i put it in the join condition?

ulrich 816 posts Joined 09/09
28 Feb 2012

do you really mean RANK? Are you aware that RANK can result in many rows per value?

Also this would give all records for the biggest end_dt. So you might need to consider to add a partition clause.

So this might work

select *
from A
       left outer join
       (select * from B qualify row_number() over (partition by empid, deptid order by end_dt desc) = 1) as B
           ON "join condition"
where A.comp_id in (2,3)

but this shouldn't be the standard access on table B. You might review the table design in case this would be the standard access.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

sm19 6 posts Joined 05/11
28 Feb 2012

Actually i want all records for the greatest end dt an di already tried this but thisgives an error in syntax.

ulrich 816 posts Joined 09/09
28 Feb 2012

select * from B qualify end_date = max(end_dt) over ()

should do.

but check also

select * from b where  (select max(end_date) from b) = end_date

depend on your data which will be faster

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

asadali.khan 11 posts Joined 10/10
29 Feb 2012

 

Here you go mate.

 

Select ID,Rep_date

from emp

qualify row_number() over (partition by id order by rep_date asc) = 1

Asad Ali Khan

BJ220 5 posts Joined 01/12
14 Mar 2012

I think this will not give the desire result.

Select ID,Rep_date

from emp

qualify row_number() over (partition by id order by rep_date asc) = 1

 

Becoz if the table has below entires:-

 

         ID    rep_date
-----------  ----------
          1  2012-01-01
          1  2012-01-02
          1  2012-01-03
          2  2012-01-04
          2  2012-01-05
          2  2012-01-06
          3  2012-01-07
          3  2012-01-08
 

On Running above query the result would be as below:-

 

         ID    rep_date
-----------  ----------
          1  2012-01-01
          2  2012-01-04
          3  2012-01-07
 

Which is not the desired result

 

but the below query would solve the purpose

 

select * from b where  (select max(end_date) from b) = end_date

         ID    rep_date
-----------  ----------
          3  2012-01-08
 

 

 

Cheers,
BJ

You must sign in to leave a comment.