Teradata Forums / Teradata / Teradata Database / Query Help / Latest Posts

RE: Query Help

joedsilva — Mon, 04 Jun 2007 04:40:18 GMT

As a matter of fact, the user doesn't require create table privileges to create volatile table.

RE: Query Help

Mitrich — Mon, 04 Jun 2007 03:31:57 GMT

I used it only to show how it can be implemented.
In any case could you tell what kind of priviledges it require except create table?

RE: Query Help

Luckyhansh — Thu, 31 May 2007 04:24:06 GMT

volatile table need more priviledge than just create tables :-)

RE: Query Help

PhilP — Fri, 25 May 2007 10:49:21 GMT

Perhaps I'm missing the point , but could you not just use select distinct?

RE: Query Help

Mitrich — Mon, 21 May 2007 00:44:22 GMT

I don't see any other ways how to solve your problem.
Ask permission either to create table or insert rows.

This problem was disscused here:
www.teradataforum.com/teradata/20040505_182346.htm

Best regards,
Mitrich

RE: Query Help

Meem — Sun, 20 May 2007 23:36:01 GMT

Hi Mitrich,

Your script works!! I cant implement it bcoz i dont hav the rights to INSERT any new rows, can you advise please?

Regards.

RE: Query Help

Mitrich — Sun, 20 May 2007 22:54:08 GMT

Try this
----------------------------------
create volatile multiset table test123 (
id integer
,name varchar(20)
) on commit preserve rows
;
insert into test123 (1, 'aaaa');
insert into test123 (1, 'aaaa');
insert into test123 (2, 'bbbb');
insert into test123 (2, 'bbbb');
insert into test123 (3, 'cccc');

insert into test123
select id, '##'||name as name
from test123
qualify row_number() over(partition by id order by id) = 1
;
delete from test123 where not name like '##%'
;
update test123 set name = substring(name from 3)
;
select * from test123
-----------------------------------------------------------

RE: Query Help

Meem — Sun, 20 May 2007 20:47:22 GMT

Hi Mitrich,
Thanks for your reply!
It did worked but im in need of a Delete query which would produce the result similar to the one you mentioned.

Regards

RE: Query Help

Mitrich — Sun, 20 May 2007 20:28:19 GMT

Hi Meem,

Try this:

SELECT EMPID, EMPNAME, EMPSSN FROM EMPLOYEE GROUP BY 1,2,3

It should remove all duplicates.

Query Help

Meem — Sun, 20 May 2007 02:52:59 GMT

Hi All,
I would like to eliminate duplicates and make the following table with unique records. I wish to have a query to solve such issues.

TableName : EMPLOYEE

EMPID EMPNAME EMPSSN
************************
1 Jack 555-55-5555
2 Joe 555-56-5555
3 Fred 555-57-5555
4 Mike 555-58-5555
5 Cathy 555-59-5555
6 Lisa 555-70-5555
1 Jack 555-55-5555
4 Mike 555-58-5555
5 Cathy 555-59-5555
6 Lisa 555-70-5555
6 Lisa 555-70-5555
. .... ...........
. .... ...........
. .... ...........

Note :
I dont have the rights to Create any Table
I dont have the rights to Add a new Column to the table
Im not suppose to use any other utils

I tried a Query using Row_Number() which makes the records unique but again i donno how to remove duplicates, Kindly help!

SELECT B.EMPID,
B.EMPNAME,
B.EMPSSN,
MIN(A.RID)
FROM
EMPLOYEE B,
(SELECT EMPID,
ROW_NUMBER() OVER (ORDER BY EMPID ASC) RID
FROM EMPLOYEE) A
WHERE
B.EMPID = A.EMPID
GROUP BY 1,2,3;

Cheers
Meem