Archives of the TeradataForum

Message Posted: Mon, 25 Oct 2004 @ 21:30:33 GMT

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

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?

Table DDL
---------

     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 );

MultiLoad code
--------------

     .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;

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