Qualifying with rownumber()

Database
Enthusiast

Qualifying with rownumber()

Hi Experts,

I am new to teradata, I came to know that qualfying is used in teradata to group the rows.

for eg :

I am getting a output 

emp_no  dept   sal      date

1

1               20     2000

1                                  6/13

The desired output should be

emp_no  dept   sal      date

1              20     2000       6/13

Thanks in advance

Plz help me out from this.

Tags (1)
3 REPLIES
Supporter

Re: Qualifying with rownumber()

this is a quite incomplete requirement and makes it difficult to give you the right answer. 

select emp_no, max(dept),max(sal),max(date) from yourtable group by emp_no

would do what you want in case only one of the columns has a not null value always. 

Enthusiast

Re: Qualifying with rownumber()

Thank u ulrich for your response,

sel

b.acct_no,

current_date as today,

case when a.lob =31 and a.pkg_desc ='xxxx'then 1 else 0 end  as XX,

case when a.lob =31 and a.pkg_desc= 'yyyyy' then 1 else 0 end  as YY,

case when a.lob =31 and a.pkg_desc= 'zzzzz' then 1 else 0 end  as ZZ,

case when a.lob =31 and a.pkg_desc= 'qqqqqq' then 1 else 0 end  as QQ,

case when a.lob =31 and a.pkg_desc= 'eeeee' then 1 else 0 end as EE,

case when a.lob =31 and a.pkg_desc= 'rrrrr' then 1 else 0 end  as RR

from  tableB as b  join tableA as a

           on u_id = b.prod_id

     where b.acct_no =''123456'

       and a.lob in ('31','32','33','35','36','37','38')

        group by 1,2;

I am getting output as

123456       1       0     0       0           0       0

123456        0     0        0    1            0         0

123456        0       1      0        0          0          0

I used Max. It worked well But i just wanted to know whether How can we use Qualify in this query.

Once Again thank you.

Supporter

Re: Qualifying with rownumber()

Yes, it's possible but I am not sure its the best way to do it.

Start reading the manuals on the OLAP functions and check the qualify options. If you run into trouble after reading and trying share the DDLs and SQL's allong with some inserts to get help.

Why do you have a condition on  a.lob in ('31','32','33','35','36','37','38')

but all your cases only have a condition on a.lob=31???