|
|
|
Junior Member
 
Group: Forum Members
Last Login: 8/12/2008 9:57:16 PM
Posts: 12,
Visits: 41
|
|
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;
Regards,
Koushik
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 4:46:44 AM
Posts: 134,
Visits: 370
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/12/2008 9:57:16 PM
Posts: 12,
Visits: 41
|
|
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?
Regards,
Koushik
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 4:46:44 AM
Posts: 134,
Visits: 370
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 12:53:07 PM
Posts: 118,
Visits: 60
|
|
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.
Regards
Ramakrishna_Vedantam
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/12/2008 9:57:16 PM
Posts: 12,
Visits: 41
|
|
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.
Regards,
Koushik
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 8/24/2008 2:47:14 PM
Posts: 425,
Visits: 398
|
|
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
;
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/12/2008 9:57:16 PM
Posts: 12,
Visits: 41
|
|
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?
Regards,
Koushik
|
|
| |