How to split one column data into multiple columns

2010-02-19T23:34:20Z

How to split one column data into multiple columns using Teradata sql-assistant?

For example: I have a table “T1” which has only one column C1 varchar (100) and it has pipe delimited “|” value like this:

XYZ|ABC|DEF|1234|WER|QWE

I have other table “T2” which has 6 columns. I want to read data from table T1, split it and load into T2 (in all 6 columns).

I will appreciate your help. Thanks

get rid of zeros after decimal point

2010-03-10T18:38:14Z

Hi All,

select CAST(MIN(column_1) AS DECIMAL(10,2)) Days from tale_1

I get output as 13.09 13.30 31.00

Can anybody let me know how to get rid of the zero after the decimal point I need output as 13.09 13.3 31

REgards,

Retention of data in DBC.DBCAssociations

2010-03-08T19:47:13Z

This table is used to support the migration of objects between Teradata installations using the ARCMain tools (COPY/RESTORE) and provide a mapping of the old object id's to the new object ids. However, I am seeing that it can also lend itself to being a point of contention with BAR activities running concurrently with ETL activities.

How long does the data in DBC.DBCAssociations need to remain?

If the objects have been archived on the destination system at least one time after being copied from the source system is the link still relevant?

Is the link their for subsequent migrations of the object from the source to the target system?

6760 Invalid timestamp

2009-10-27T07:02:47Z

Hi,

I am trying to update the timestamp into a table like this:

Update tablea set status = 'Succeeded', end_date = cast(cast(current_timestamp(0) as format 'dd/mm/y4bhh:mi:ss') as char(19)) where id = 3456;

I am getting Invalid timestamp for this query. When I check the timestamp data through select it looks like this:

select cast(cast(current_timestamp(0) as format 'dd/mm/y4bhh:mi:ss') as char(19))

Resultset:27/10/2009 02:56:12

I want to update the end_date through current_timestamp not by putting timestamp value in it.

Please help me in this. Thanks and Regards, TDHelp

order by issues

2010-01-06T07:48:41Z

Hi,

I need help in order by, I have a subquery which gets this data.

pname version   count
xxx    V12.1        1
yyy    V22.1.3      1

We are using grouping function and roll up in the main query, the query was written in the following way:

SELECT case when grouping(pname) =  1 then 'Total' 
       else 
          case when grouping(version) = 1 then 'Subtotal ' || pname
      when RANK() over (partition by pname order by SUM(count)) = 1 then pname
      else ' ' 
          end 
       end "NAME", 
version "VERSION", SUM(count) "Qty" 
FROM (
       SELECT pname, version, 1 count from tablea, tableb, tablec
       where tablea.key = tableb.key
       and tableb.key = tablec.key
       --order by pname, version   (ghis order by worked in oracle , but does not work in TD because order by does not work on subquery)
      )as derived_table GROUP BY rollup (pname,version)

With this query, the resultset comes in the following way:

NAME                   VERSION    QTY
Total                     ?	2
Subtotal XXX             ?	1
XXX               V12.1	1
YYY               V22.1.3	1
Subtotal YYY             ?	1

When compared to oracle the data looks good, but the records are not ordering properly. The actual output should come like this:

NAME                   VERSION    QTY
XXX               V12.1	1
Subtotal XXX             ?	1
YYY               V22.1.3	1
Subtotal YYY             ?	1
Total                     ?	2

Could anyone help me to display the data in this order. I appreciate your help. Thanks in advance.

Regards, TDHELP

Teradata SQL Assistant takes forever to interrupt large answerset

2010-02-11T03:30:41Z

I am using Teradata SQL Assistant version 13.0.0.08 on Windows. I think it's the version written in C#.

When I run a query that returns more than 2000 rows, which is my max in the options for rows to display, and it asks me if I want to cancel after 2000 rows and I click Yes, I expect it to immediately give me back control. Instead, it still takes a long time (sometimes minutes) to come back. During this time, SQL Assistant is unresponsive and the query pane background is light red, like it's busy. I have checked the network activity and it appears that the rest of the rows are still being read from Teradata. Very annoying.

This happens the same way whether I am using the .Net provider or ODBC to connect to Teradata. I have looked through all the options but nothing seems like it would help. Tomorrow I will try installing a slightly newer version that I think is available.

How can I fix this?

Least function in TD

2009-11-10T00:19:44Z

Hi,

I need help in getting this function,

In oracle, least(2, 5, 12, 3) would return 2

Do we have similar function in TD or how do we do.

Thanks for Regards, TDHelp

How to interpret value of field 'CreateDate' in TDWM tables?

2010-03-01T12:32:58Z

CreateDate and RemoveDate are two fields in many of the tables related to TDWM e.g. in WlcDefs. The values and their meanings (according to me) are like:

1100301 - 2010/03/01 1091114 - 2009/11/14

What does the first '1' represent in these date fields? and is my interpretation correct?

Does RollbackPriority=TRUE mean to use Rush or No Rush for rollbacks?

2010-02-26T16:21:58Z

This DBSControl setting is supposed to control whether rollbacks run at Rush priority or not. They used to always run at Rush because Teradata wanted to get them out of the way as soon as possible, but later they provided this RollbackPriority DBSControl parameter so the DBA can decide. However, it is not clear whether setting it to TRUE means that the rollback runs at Rush or not.

Does TRUE mean that rollbacks run at Rush or that they run at the user's logon priority?

The help for this setting is comically ambiguous:

RollbackPriority - This Field defines the system default for the rollback priority. This allows the DBA to either take the default priority of Rush or the user's logon priority. To enable this feature set the field to TRUE. To disable the feature set the field to FALSE. Note that this new feature will affect all users on the system.

How to perform Bit-wise operations in SQL?

2010-01-18T09:18:40Z

How to perform simple bit-wise operations like OR (|), AND (&), XOR (^) in SQL queries?

Can these be performed on numeric values? like

sel 1 | 2;

Order by based on case sensitivity

2010-02-10T23:55:31Z

Hello All ,

Is there any way to sort the column data based on case sensitivity. How can I do order by if I want to display the UPPER CASE data first of same order first and then the lower case .

Decode with Rownum

2010-01-26T20:31:10Z

Hi,

I have a requirement in Oracle using Decode function with Rownum. I need to convert this into Teradata. I need help in this. I tried using Top function and Rank qualify but unable to do the same. Please help me in this.

sample query in Oracle is:

SELECT DECODE(ROWNUM, 1, party, 2, party, 3, party, 4, party, 5, party,6, party, 7,party,'Others') party,
mnth,SUM(qty) FROM
(
SELECT NVL(trim(b.Party_name),'Others') party, c.MONTH_ID mnth, SUM(a.qty) qty 
FROM tablea a,tableb b,tablec c
WHERE a.col1 = b.col1 
AND a.col2 = c.col1
AND a.col3='Y'
GROUP BY party,mnth
ORDER BY SUM(a.qty) DESC
)
GROUP BY DECODE(ROWNUM, 1, party, 2, party, 3, party, 4, party, 5, party, 6, party, 7, party, 'Others'),mnth

Please help me the conversion of outer query in Teradata. Thanks in Advance.

Thanks and Regards, TDHelp.

Difference between FilePres and FilePreReads

2009-12-21T07:24:02Z

From Teradata documentation, the definitions of these ResUsageSpma columns are:

FilePres         Total number of times a cylinder is loaded.

FilePreReads     Number of times a cylinder is loaded.

which look similar, but I observed on our lab's Teradata and found quite different values with an observation that FilePres = 2 x FilePreReads (approx.)

I also looked into KA but couldn't find any explanation. Please respond if you know the meaning of these fields.

The story doesn't end here. When I compared different versions of Teradata documents (V2R6, 12, 13) I found out quite a few differences among them.

The screenshots of the differences (from Excel) are attached.

Any idea on differences or this maybe a Teradata documentation problem?

Timestamp difference

2010-01-21T19:17:39Z

Hi Experts,

Need help here. In Oracle when we do timestamp difference. The resultset would be days and with some decimal numbers. For Eg.

SELECT '03.12.2004:10:34:24' "Now",
       TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
       TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
       - hiredate "Hired since [Days]"
  FROM emp;
Now                 Hiredate            Hired since [Days]
------------------- ------------------- ------------------
03.12.2004:10:34:24 17.12.1980:00:00:00         8752.44056

In the above example, we get 8752.44056 as answer. In that, '8752' is no. of days and want to know what is '44056' the precision values,

I want to do the same difference in teradata, I am able to get the no. of days but do not know how to get the precision values. I need the same format in teradata. Please help me in this. Thanks in advance.

Regards, TDHelp

Blank Column title

2010-01-21T19:43:46Z

Hi,

I need help in not displaying the column title in the select list.

For eg: in Oracle

select 'abc' as " " from dual

we get abc with blank column name

In teradata we are not able to do this. Want to know is there a way to do this. Please help me.

Regards, TDHelp

Oracle update to teradata update with select subquery

2009-10-20T23:37:13Z

Here is the oracle query .Can anybody give me teradata query .

update table_a A 
    	set display_format =  nvl((select prop.propvalue from table_a B,
    							      table_c C 
    							 where B.request_ID = A.request_ID 
    							 and   B.report_Name = A.report_name 
    							 and B.report_type = A.report_type 
    							 and C.systemname = 'system' 
    							 and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt' 
    							 and C.PROPVALUE in ('yes','No') ), display_format)
    	where request_id = 123

Regards,

How to yield maximum decimal digits from a cast?

2010-01-13T11:49:49Z

I want to demonstrate the problem with the help of an example.

Accurate division: 7299 / 48 = 152.0625 However, in Teradata all precision is lost when querying for select 7299/48 = 152

We have to apply a cast for precision, like: select cast(7299/48 as decimal(8,5))

Assuming that my data column has a length of 8. Here I cannot provide value greater than 8 in the first argument. In the second argument (number of digits after decimal point), at max I can provide 5 because the result yields 3 digits in the numeric part and as a remainder we get 5 (8-3).

I'm asking this because I can go into troubles if I apply the same cast to: select 72990/48 = 1520.625 because here the numeric part has 4 digits and I can only supply 4 in the second argument.

Is there a way that I can specify only the first argument 8 (equaling to the length of the column) and get the result with complete precision?

Rounding increase by 1

2010-01-07T22:26:09Z

Hi,

I need help in Rounding,

value >5 increases to 1.

Eg: 72.70 should Round to 73.0

Could anyone please help me.

Thanks in Advance. TDHelp

query in select list

2010-01-08T00:23:20Z

Hi,

I need help in writing a query in select list, Is this possible in TD. In Oracle we are able to do it. I have a requirement like this:

select col1, col2, Round((col2*100.0/**(select count(col4) from tableh group by col)),**999.9)
FROM (select col1,count(col2) 
      from tablea, tableb
      Where tablea.col1 = tableb.col1
      and tablea.col2 = tableb.col2
      group by col1)

Thanks in advance. TDHelp

Determine the maximum number of AWTs

2009-12-16T10:03:48Z

The default number of AWTs per AMP is defined to be 80. Can this number grow to some larger value than 80 and can we set a threshold (of number of AWTs) on an AMP level or on Node or System level?

Additional note: ResUsageSpma and ResUsageSawt has AwtInuseMax but it does not give us the maximum defined number of AWTs.

UPDATE from recursive query

2009-11-11T13:21:57Z

Is it possible to do an update from a recursive query in one SQL statement without using a temporary table?

I am trying to set the very top boss id for each employee and I get the error

3707: Syntax error, expected something like a name or '(' between '(' and the 'with' keyword.

CREATE TABLE EMP(ID INTEGER, BOSS_ID INTEGER, TOP_BOSS_ID INTEGER);
INSERT INTO EMP VALUES(1,NULL,NULL);
INSERT INTO EMP VALUES(2,NULL,NULL);
INSERT INTO EMP VALUES(3,1,NULL);
INSERT INTO EMP VALUES(4,2,NULL);
INSERT INTO EMP VALUES(5,1,NULL);
INSERT INTO EMP VALUES(6,2,NULL);
INSERT INTO EMP VALUES(7,3,NULL);

UPDATE EMP
FROM
(
  WITH RECURSIVE EH(ID,BOSS_ID,SUPER_BOSS_ID,LEVEL) AS
  (
   SELECT ID,BOSS_ID,BOSS_ID,0
   FROM EMP
   UNION ALL
   SELECT E.ID,E.BOSS_ID,P.BOSS_ID,E.LEVEL+1
   FROM EH E JOIN EMP P ON E.SUPER_BOSS_ID = P.ID
  )
  SELECT * FROM EH
) X
SET TOP_BOSS_ID = X.BOSS_ID
WHERE X.SUPER_BOSS_ID IS NULL
AND X.BOSS_ID IS NOT NULL
;

Tab delimited question

2009-11-11T09:37:51Z

Hi,

I do not know whether I can ask this question here or not. But I am facing this problem and need help from the experts.

I have a table called Properties_tab which contains oracle sql queries like this: Name query sql1 Select * from abc sql2 select col1 "name", col2 "address" from abcd where col3 = 123 Sql3 select col1, nvl(col2,0), col3 from abcde where col4 = 'abcd'

The team gave us this data into an excel file for Teradata conversion. we converted into Teradata. After converting I am trying to upload them into the Teradata. I do not want to do an manual update or insertion. I am trying to do in sql assistant- file- Import data. So it is looking for a tab delimited.

I tried converting the excel file into tab delimited and it is showing like this. sql1 "Select * from abc" sql2 "select col1 ""name"", col2 ""address"" from abcd where col3 = 123" sql3 "select col1, nvl(col2,0), col3 from abcde where col4 = 'abcd'"

and it is inserting with double quotes into the table. I do not want to insert with double quotes in table. Manual inserts works good like this Insert into Properties_tab(name, query) values ('sql3','select col1, nvl(col2,0), col3 from abcde where col4 = ''abcd''')

Any help in this would really appreciate to avoid the manual updates or insertion.

Thanks and Regards, TDHelp

TDWM configuration data - purging practices

2009-11-19T19:26:42Z

Is TDWM configuration data e.g. WlcDefs, RuleDefs, SysCons etc. ever purged? I've noticed that whenever some modification is made in TDWM the old configuration data is kept in the table with the field RemoveDate, RemoveTime. Is this data purged after some configured duration? or is it a common practice to purge this data in the industry?

I'm asking this because there are clear instructions about purging the ResUsage and DBQL data in Teradata documents but so far I haven't found anything on TDWM configuration data.

Teradata documentation online search

2009-11-23T08:25:38Z

Hi, I am new to Teradata and have to very often search for documentation whenever I hit any error. Does Teradata have any online search feature similar to "IBM Infocentre for DB2-(http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp) " where I can search on any topic and get the exact results easily (not in terms of pdfs)? I do have access to manuals in the form of pdfs but dont find it very user friendly. Appreciate any help on this.

Help Writing a Query for Historical Disk Space Usage

2009-11-19T16:13:47Z

I am trying to write a query that displays a summary of the total disk space used for all databases for each month.

The return results should look something like this for 2008:

 Month   Sum(CurntPerm)    Sum(PeakPerm)
 01      2.3  tb           2.4  tb
 02      2.35 tb           2.35 tb
 03      2.38 tb           2.5  tb
 etc.................

Is there a table that can be queried for historical disk space information?

Thanks, Jennifer

Relation between Priority Scheduler in Priority Scheduler Administrator and the Priority Scheduler in TDWM

2009-11-16T12:28:32Z

In TDWM we have a Priority Scheduler where we can map WD to AG, add new RP, AG etc. The key here is the 'Priority' that maps WD to AG. This 'Priority' can be set in the WD as 'Enforcement Priority' and can be mapped to AG in the Priority Scheduler screen in the TDWM. Uptill here the relation between TDWM's workloads and priority is clear, and this association can be confirmed by observing the SlgPriority field in the TDWM.WlcDefs table.

However the 'Priority Scheduler Administrator' shows a different picture of priority and the association between RP, AG, Perf Group, Perf Period looks to be contained in this administrator itself.

There seems to be no relation between the priority of these two (TDWM and PSA). Is this designed this way? If so, then what is the purpose of PSA? If not, then how can the priority in TDWM's Priority Scheduler be linked to PSA?

Thanks,

Reading DBS Control values via SQL

2009-11-12T06:05:10Z

To read values in DBS Control we have to execute the DBSControl utility. Is there a way to read these by issuing queries against Teradata database?

Timestamp diff with HOUR(4) TO SECOND(0)

2009-11-03T23:05:48Z

Hi,

I am having problem in converting timestamp difference in Hour to Second

Earlier with timestam columns I get,

select (CLOSED_DATE-CREATED_DATE) hour(4) to second(0) from Tablea where id = '12345'

Resultset: 955:30:19

The values for those two columns are:

CLOSED_DATE:2008-06-30 13:50:29
CREATED_DATE:2008-05-21 18:20:10

The same thing if I try with current_date - another date, am getting Invalid operation on an ANSI datetime or interval value

and I changed the current_date like this, but still not helping:

select ((cast(current_date as timestamp(0)) + ((current_time - time '00:00:00') hour to second))-CREATED_DATE) hour(4) to second(0) from tablea where id = '12345'

This is actually, Oracle to Teradata conversion. In Oracle they wrote a user defined function for it.

CREATE OR REPLACE FUNCTION GET_TIMESTAMP (vdays IN NUMBER)
RETURN VARCHAR2 IS
v_timestamp VARCHAR2(50);
/*    This function takes the input as Number of days e.g 1/2/2.42 etc...
    and returns HH:mi:ss.
  */
BEGIN
SELECT
TRUNC((vdays * 24 * 60 * 60)/(60*60)) || ':' ||
TRUNC(MOD((vdays * 24 * 60 * 60),(60*60)) / 60) || ':' ||
TRUNC(MOD(MOD((vdays * 24 * 60 * 60),(60*60)) , 60)) INTO v_timestamp FROM dual;
RETURN v_timestamp;
END;
/

Here they are passing the no. of days into the function. In TD, when I tried it with two timestamp columns difference the resultset is good, but with converting something like this, I am having trouble:

 select Get_Timestamp(NVL(duration,0)+NVL(SYSDATE-CREATED_DATE,0))
 from Oracle_Tablea where id = '12345'

Resultset in Oracle:12299:3:48

Here the values for duration = 21,0779050925926
CREATED_DATE = 6/30/2008 5:50:29 AM
SYSDATE-CREATED_DATE = 491,378449074074

Please help me in Teradata. Thanks and Regards, TDHelp

2665 Invalid Date

2009-11-04T21:24:16Z

Hi,

I am trying to insert some date values in a table and getting error: 2665 Invalid Date

The format of the date field on the table is "date_1 FORMAT 'DD-MM-YYYY' ". In the file the date is coming over as '1-01-2008' from java. If I try with '01-01-2008', it inserts fine.

Insert into Tablea (id,user_id,start_date,end_date) 
 values (176428,'abcd',
   CAST ('1-01-2008' AS DATE FORMAT 'DD-MM-YYYY'),
   CAST ('4-04-2008' AS DATE FORMAT 'DD-MM-YYYY'))

In Oracle, if we send in this format to_date('1-01-2008','DD-MM-YYYY'), it inserts fine. In Teradata we are getting errors.

Any help would really appreciate.

Regards, TDHelp

Top function in Subquery

2009-10-28T23:15:08Z

Hi,

I am converting a query from Oracle to Teradata:

In oracle, I have rownum function in subquery. and it runs fine there. But in TD I am using TOP function in subquery and it is not supported. Please help me the work around for this.

Query in Oracle:

Select col1, col2, col3
FROM tablea where col4 in
    (Select col4 from 
        (Select distinct col4 from tablea
        order by col4 DESC)
     Where ROWNUM < 7
     )
Order by col1, col2 desc

Converted in Teradata, but this is not correct:

Select col1, col2, col3
FROM tablea where col4 in
    (Select TOP7 a.col4 from 
        (Select distinct col4 from tablea) as a
     order by a.col4 DESC
     )
Order by col1, col2 desc

Please help me the work around for this query.

Thanks in advance. Regards, TDHelp

Top Questions - TeradataQuestions

How to split one column data into multiple columns

get rid of zeros after decimal point

Retention of data in DBC.DBCAssociations

6760 Invalid timestamp

order by issues

Teradata SQL Assistant takes forever to interrupt large answerset

Least function in TD

How to interpret value of field 'CreateDate' in TDWM tables?

Does RollbackPriority=TRUE mean to use Rush or No Rush for rollbacks?

How to perform Bit-wise operations in SQL?

Order by based on case sensitivity

Decode with Rownum

Difference between FilePres and FilePreReads

Timestamp difference

Blank Column title

Oracle update to teradata update with select subquery

How to yield maximum decimal digits from a cast?

Rounding increase by 1

query in select list

Determine the maximum number of AWTs

UPDATE from recursive query

Tab delimited question

TDWM configuration data - purging practices

Teradata documentation online search

Help Writing a Query for Historical Disk Space Usage

Relation between Priority Scheduler in Priority Scheduler Administrator and the Priority Scheduler in TDWM

Reading DBS Control values via SQL

Timestamp diff with HOUR(4) TO SECOND(0)

2665 Invalid Date

Top function in Subquery