All Forums Database
Koushik Chnadra 27 posts Joined 05/08
29 May 2008
Few basic queries

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 :declarev_num integer;beginselect 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 773 posts Joined 04/08
29 May 2008

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 N5. ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) can be usedHTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Koushik Chnadra 27 posts Joined 05/08
30 May 2008

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 773 posts Joined 04/08
30 May 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

30 May 2008

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.

Ramakrishna Vedantam, Tata consultancy services, Hyderabad

Koushik Chnadra 27 posts Joined 05/08
30 May 2008

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 505 posts Joined 07/05
01 Jun 2008

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, ENMEFROM RECTBL;

Koushik Chnadra 27 posts Joined 05/08
03 Jun 2008

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 4628 posts Joined 11/04
03 Jun 2008

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 ZONEOracle'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

Dieter

dnoeth 4628 posts Joined 11/04
03 Jun 2008

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 xwhere rn=3;And there's a nice enhancement in Teradata to get rid of Derived Tables: QUALIFYselect empname,sal from empqualify 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.aspxDieter

Dieter

Koushik Chnadra 27 posts Joined 05/08
03 Jun 2008

Thanks for your reply.If I want to take the month name as "MON" i.e. MAY - from a timestamp datatype, then how do I have to cast it.Similar questions if I want to take the week no/ quarter no/ year from a timestamp field then how do I have to cast it.

dnoeth 4628 posts Joined 11/04
03 Jun 2008

There's the Standard SQL EXTRACT function to retrieve parts of a timestamp like YEAR/MONTH/DAY/HOUR/MINUTE/SECOND as an integer. That function also exists in Oracle.WEEK and QUARTER is not implemented, of course QUARTER is easy to calculate, but WEEK is horrible in plain SQL.In a typical environment it's recommended to join to a calendar-table instead of doing the same calculation over and over again, as day/month/year/week/quarter of a given date never changes.Extracting dates as strings (similar to Oracle's TO_CHAR) is done by a FORMAT followed by a CAST:SELECT CAST((CURRENT_TIMESTAMP (FORMAT 'mmmByyyy,BddBhh:mi:ss')) AS CHAR(30))Dieter

Dieter

Koushik Chnadra 27 posts Joined 05/08
05 Jun 2008

Hi,I am looking for a front end application where in backend the database will be in Teradata. The front end application will be used to create/modify users for a new application etc. Actually I am looking for something like HTMLDB which is available with Oracle.Can you please let me know that what we can use in this case.

Koushik Chnadra 27 posts Joined 05/08
10 Jun 2008

Hi,Can you please let me know what is equivalent statement for DBMS_OUTPUT.PUT_LINE in Teradata.

Koushik Chnadra 27 posts Joined 05/08
10 Jun 2008

Hi,Can you please also let me know that is there any concept of static and dynamic dictionary tabes in Teradata.

al1_24 39 posts Joined 11/04
12 Jun 2008

You can't directly extract week no or quarter no directly form a timestamp but you can use a join on CALENDAR_DATE in CALENDAR table and get WEEK_OF_{MONTH|YEAR|CALENDAR} and QUARTER_OF_{YEAR|CALENDAR} corresponding column values

Koushik Chnadra 27 posts Joined 05/08
16 Jun 2008

Hi,I think we can find out week no, month of the year etc from the view sys_calendar.calendar, like below:select * from sys_calendar.calendar where year_of_calendar=2008 order by calendar_datePlease correct me if I am wrong.Can you please also help me in my following queries :1. I am looking for a front end application where in backend the database will be in Teradata. The front end application will be used to create/modify users for a new application etc. Actually I am looking for something like HTMLDB which is available with Oracle. Can you please let me know that what we can use in this case. 2. Can you please let me know what is equivalent statement for DBMS_OUTPUT.PUT_LINE in Teradata.3. Can you please also let me know that is there any concept of static and dynamic dictionary tabes in Teradata.

Koushik Chnadra 27 posts Joined 05/08
16 Jun 2008

Hi,In addition to the above queries can you please also let me know what is the basic difference between a macro and a procedure in teradata. What ever we are doing in a macro that we can do in a procedure as well then what is the basic requirement of having a macro.

You must sign in to leave a comment.