Archives of the TeradataForum
Message Posted: Mon, 25 Oct 2004 @ 21:30:33 GMT
I was told by NCR consultants, and the documentation backs this, that UDF's cannot be used against primary index fields. Is this true? Did it start this way, and then with later patches the rule was removed?
The documentation states the following:
A UDF cannot appear in a constant expression for calculating the hash of a unique index. For example, a UDF cannot appear in an INSERT statement as a value expression for the primary index column. (SQL Reference/UDF Programming, p. 1-70)
Many of our programs are MultiLoads to load a binary-ebcdic file. Historically, we have used UDF's in our programs to convert all fields except primary index fields, and then use inmods to handle the PI fields. One of my co-workers created a MultiLoad program that did not use an inmod and just used UDF's to convert the PI fields as the table was loaded. There were no error messages and the table loaded perfectly, PI fields and all. Am I misreading the documentation or did the rules for UDF's change?
CREATE SET TABLE mydb.mytable ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( FIELD1_ID CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, FIELD2_CD CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, FIELD3 CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC, UPDATE_DATE DATE FORMAT 'yyyy-mm-dd' NOT NULL, UPDATE_TIME TIME(6) NOT NULL) UNIQUE PRIMARY INDEX ( FIELD1_ID ,FIELD2_CD );
.BEGIN IMPORT MLOAD TABLES mydb.mytable_work WORKTABLES myworkdb.mytable_WT ERRORTABLES myworkdb.mytable_ET myworkdb.mytable_UV ERRLIMIT 100 ; /* data file layout */ .LAYOUT data_rec; .FIELD IN_FIELD1_ID 1 BYTE(3); .FIELD IN_FIELD2_CD * BYTE(8); .FIELD IN_FIELD3 * BYTE(40); .FIELD IN_UPDATE_DATE * BYTE(10); .FIELD IN_UPDATE_TIME * BYTE(8); /* data file DML */ .DML LABEL ins_data_rec; insert into mydb.mytable_work ( FIELD1_ID, FIELD2_CD, FIELD3, UPDATE_DATE, UPDATE_TIME ) values ( syslib.EBCTOASC(:IN_FIELD1_ID,3), syslib.EBCTOASC(:IN_FIELD2_CD,8), syslib.EBCTOASC(:IN_FIELD3,40), cast((syslib.EBCTOASC(:IN_UPDATE_DATE,10)) as date format 'yyyy-mm-dd'), syslib.EBCtoASC(:IN_UPDATE_TIME, 8) ) ; /* import data file */ .IMPORT INFILE mydatafile.ebc FORMAT UNFORMAT LAYOUT data_rec APPLY ins_data_rec; .END MLOAD;
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|