Archives of the TeradataForum

Message Posted: Thu, 10 Jul 2003 @ 14:17:24 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Thom,

1) if you are using BTEQ you can use inbuilt Teradata variables to find how many rows were affected. Unfortunately I don't know of a way to get them into a SQL statement, so that you could for example insert them into a performance tracking table (I used to do that in SQL Server a lot...)

The available variables are:

errorcode
errorlevel
activitycount

You can use them to build conditions into your BTEQ batches like

.If ACTIVITYCOUNT = 0 THEN GOTO LABEL MY_ERROR_EXIT

2. Saying that you are an application developer I guess that you want to find objects in some sort of program before attempting to drop, create or use them.

To find a permanent table in a database:

select * from dbc.tables
where tablename ='your table name'
and databasename = 'your databasename'
and tablekind ='T'

To find an index:

select * from dbc.indices
where tablename = 'your tablename'
and databasename = 'your databasename'
and indexname = 'your indexname'

(This will work, if your indices are named, which is not necessarily required.) If they are not named, and you want to be sure that exactly the index you specified exists you will have to do something like:

SELECT *
FROM
( SELECT INDEXNUMBER, COUNT(*) ACTUAL_ROWCOUNTER
  FROM
   (SELECT *
    FROM DBC.INDICES
    WHERE DATABASENAME = 'YOUR DATABASENAME'
    AND TABLENAME = 'YOUR TABLENAME'
    AND COLUMNNAME IN ('LIST OF ALL THE COLUMNS IN
YOUR INDEX')
    ) A
  GROUP BY 1
) x
,
( SELECT INDEXNUMBER, COUNT(*) TOTAL_ROWCOUNTER
  FROM DBC.INDICES
  WHERE DATABASENAME = 'YOUR DATABASENAME'
    AND TABLENAME = 'YOUR TABLENAME'
  GROUP BY 1
) y
WHERE X.INDEXNUMBER = Y.INDEXNUMBER
AND ACTUAL_ROWCOUNTER = TOTAL_ROWCOUNTER;

Not sure about how to do a volatile table.

3. This might come as a big disappointment to you (I know it was to me), but TD does not support a function like replace. (Not sure about R5, but I don't think it does this either...)

You could probably build yourself a stored procedure that you call recursively using the index & characters functions. But it would be cumbersome & might not perform well. So if you can, you might want to consider doing this outside of TD.

5. For date data types you don't need to cast as a string. TD does implicit conversion (I think to the format defined in the table layout or if none is there to the default format for your system)

For datetime you can do cast(timestamp as char(19-26)) the values for 19 - 26 depend on the precision of our initial timestamp 19 will equal a timestamp(0), 26 will equal timestamp(6))

If you are trying to do the conversion to string only to get certain parts out of the fields you can take a look at the various extract functions that TD provides.

Examples would be:

select extract(year from current_date);
select extract(minute from current_time);
The returned results will have a numerical datatype.

Hope this helped some!

Mirjam Berger

	<Prev	Next>		<<First	<Prev	Next>	Last>>