Print

Send to colleague

E-mail us

FAQ

Q: Can a macro be invoked from a stored procedure?

In the stored procedure we delete and load rows into a table and then need to collect statistics on the table before execution of the other statements in the stored procedure. We thought we couldn't collect statistics in a stored procedure, so we created a macro. However, we cannot invoke the macro from the stored procedure either.

A: Beginning with Teradata Database V2R5, you can invoke collection of statistics via a "collect statistics" command, i.e.:

create procedure dropme()
begin
collect statistics on customer_service.employee column employee_number; end;

You can use SQL Data Definition Language (DDL) statements in a stored procedure when the creator is also the immediate owner of the procedure. That is, a stored procedure can contain DDL statements only if it is created in the database of the user.

The Teradata Database supports the following SQL DDL statements for stored procedures:

...
. COLLECT STATISTICS

The EXECUTE macro statement is not supported by stored procedures. Teradata SQL macros can be used as part of a DECLARE CURSOR (macro form) within a stored procedure.

Q: What is the internal storage for numeric datatypes?

We want to write a function which takes two numeric datatypes and returns the greater one or 0 according to a condition. How will we be able to do that?

Also, we would like to write a to_char function which takes a numeric type and returns the result in varchar. The cast function will return with a dot (.) and we don't want that.

For example:

If we have column with type NUMERIC (10, 5) and the actual value in the column is 105 Teradata returns 105.00000, but we need the value of 105 itself.

A: Use the following to get the results you are looking for:

cast(cast(10.333 as integer) as varchar(8))

(Please note that this example will always truncate the decimal portion of the numeric value. For example, 0.888 will be returned as '0'.)

Q: We are trying SQL Assistant import but are receiving the following result: "Wrong number of data values in Import file."

The following is the command:

INSERT INTO DEVG123AHE.EXCHANGEGROUP VALUES (?,?,?,?,?,?);

The data in the file looks like this:

'YR','2005-08-05','2060-12-31','EUR','2005-08-05 11:40:49','CLOUGH'
'MT','2005-08-05','2060-12-31','GBP','2005-08-05 11:40:49','CLOUGH'

The table looks like this:

CREATE MULTISET TABLE DEVG123AHE.EXCHANGEGROUP ,NO FALLBACK ,

NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
EGP_ID CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
EGP_START_DT DATE FORMAT 'yyyy-mm-dd' NOT NULL,
EGP_END_DT DATE FORMAT 'yyyy-mm-dd' NOT NULL,
CUY_ID_BASE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
EGP_UPDT_TD TIMESTAMP(0) NOT NULL,
EGP_UPDT_USER_ID CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
UNIQUE PRIMARY INDEX EXCHANGEGROUP_UPI ( EGP_ID ,EGP_START_DT );

We thought perhaps it had to do with the TimeStamp(0) formats but we can't seem to get even the simplest import to work for some reason.

We are sure it is operator error, but any insight you can provide would be of great assistance.

A: You will get this error if the delimiter of the import file does not match the delimiter defined in SQL Assistant Options -> Export. You may also have problems if the NULL display value isn't the same.

Q: We're looking for the standard deviation of the values of a column in a Teradata table. Does Teradata have a standard deviation function? If not, any suggestion on how to do this?

A: The functions are: STDDEV_POP and STDDEV_SAMP.

For more details regarding these functions please reference the Teradata manual "SQL Reference: Functions and Operators." T

© Teradata Magazine-March 2006

Archived FAQs

back to top