Few basic queries

[Koushik_Chnadra]

I have few queries (I am very new to Teradata and I have previous experience of working in Oracle) :

1. I try to execute a simple block from Teradata SQL assistent :
declare
v_num integer;
begin
select 4/2 into v_num from CDP_DRV_0.dim_carr;
print ('v_num::::'||v_num);
end;
/
but it is throwing error. Can you please let me know how to resolve it.

2. Is the dual table exist in teradata?

3. Are the following statements valid :
a) A database can be created within a database.
b) A user can be created within a database.
c) A database can be created within a user.
d) A user can be created within a user.

4. select * from CDP_DRV_0.dim_carr where rownum < 2;
rownum is not a valid keyword in teradat for restricting rows. So what is the substitute of it.

5. Is analytical function there in teredata? If it is there can you please give an equivalent example.
/* The following query is valid in Oracle and it finds the employee name having 3rd max salary */
select empname,sal from
(select empname,sal,rownumber() over (partition by sal order by sal) rn from emp)
where rn=3;

[Adeel_Chaudhry]

Hello,

Following are the answers:

1. It will not work like that, for declaring variables, you need to have a Stored-Procedure.
2. No idea about dual table in Teradata.
3. All yes. (given there is no issue of permissions)
4. You can use TOP N
5. ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) can be used

HTH.

Regards,

Adeel

[Koushik_Chnadra]

Can you please let me know then how the following statements work in teredata :

1. select 1 fro dual;
2. what is euqivalent of dense_rank() in teredata?

[Adeel_Chaudhry]

As mentioned in last reply, i have no idea of DUAL table in Teradata. Secondly, till V2R6 DENSE_RANK is the future reserved word, no idea about V2R12.

Regards,

Adeel

[ramakrishna_ved]

There is no DUAL table in Teradata.
However you can achieve certain results in a different way.
for example select 1 will give 1 as a result.

[Koushik_Chnadra]

can you also please let me know the equivalent query for teredata :

select empno,ename from emp start with empno=5126 connect by empno= prior mgr;

The above query is valid in oracle.

[joedsilva]

This comes under recurisve query in Teradata.

WITH RECURSIVE RECTBL(ENO, ENME, MGNO)
AS
(

SELECT EMPNO, ENAME, MGR
FROM EMP
WHERE EMPNO = 5126

UNION ALL

SELECT EMPNO, ENAME, MGR
FROM EMP INNER JOIN RECTBL
ON EMPNO = MGNO

)

SELECT ENO, ENME
FROM RECTBL
;

[Koushik_Chnadra]

Thanks for the reply.

Is there any concept of TRUNC function in TEREDATA. Means is there any data type which hold data as well as time part in the same column, and if user want to extract only the date part of it then the TRUNC will work?

[dnoeth]

Hi Koushik,
there are three different dataypes in Teradata/Standard SQL:
- DATE, just the date
- TIME, just the time, optionally WITH TIME ZONE
- TIMESTAMP, date plus time, optionally WITH TIME ZONE

Oracle's DATE and TIMESTAMP both map to Teradata's TIMESTAMP.
So you better think about the right datatype before truncating all the time.

There's no TRUNC function, it's done using a typecast:
select cast(current_timestamp as date);

Dieter

[dnoeth]

Hi Koushik again,
regardig your previous questions:

"4. select * from CDP_DRV_0.dim_carr where rownum < 2;"

select top 2 * from CDP_DRV_0.dim_carr where rownum < 2;

"5. Is analytical function there in teredata? If it is there can you please give an equivalent example.
/* The following query is valid in Oracle and it finds the employee name having 3rd max salary */
select empname,sal from
(select empname,sal,rownumber() over (partition by sal order by sal) rn from emp)
where rn=3;"

You just forgot to name the Derived Table (Inline View), it's not neccessary in Oracle but in Standard SQL:
select empname,sal from
(select empname,sal,rownumber() over (partition by sal order by sal) rn from emp) AS x
where rn=3;

And there's a nice enhancement in Teradata to get rid of Derived Tables: QUALIFY

select empname,sal from emp
qualify rownumber() over (partition by sal order by sal)=3;

"2. what is euqivalent of dense_rank() in teredata?"

http://www.teradata.com/teradataforum/Topic9590-9-1.aspx

Dieter