0

Is it possible to create virtual columns in Teradata, that is, columns based on the values of another columns of the same table, for example:

CREATE SET TABLE TEST.ZZ_RECEIPTS,
    NO FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO
(
    ID_RECEIPT NUMBER,
    ID_ITEM NUMBER,
    NUM_ITEM NUMBER,
    VALUE_ITEM DECIMAL (10, 2)
/*,
    -- TOTAL_ITEM is the virtual column I want to add 
    TOTAL_ITEM DECIMAL (10, 2) AS (NUM_ITEM * VALUE_ITEM)
*/
) PRIMARY INDEX (ID_RECEIPT, ID_ITEM);

INSERT INTO TEST.ZZ_RECEIPTS (ID_RECEIPT, ID_ITEM, NUM_ITEM, VALUE_ITEM) VALUES (1, 1, 3, 4.50); --3*4,50 = 13,50
INSERT INTO TEST.ZZ_RECEIPTS (ID_RECEIPT, ID_ITEM, NUM_ITEM, VALUE_ITEM) VALUES (1, 2, 4, 5.50); --4*5,50 = 22
COMMIT;

SELECT * FROM TEST.ZZ_RECEIPTS;
ID_RECEIPT    ID_ITEM   NUM_ITEM   VALUE_ITEM TOTAL_ITEM
---------- ---------- ---------- ------------ ----------
         1          1          3         4,50       13,5
         1          2          4         5,50         22

Edit: I want to create an index over this field, similar to a function-based index.

Thanks!

RufusSC2
  • 253
  • 2
  • 9
  • This is usually done in the view level. – dnoeth Mar 08 '18 at 11:17
  • I forgot to tell, I want to create an index over it, like a function-based index – RufusSC2 Mar 08 '18 at 11:19
  • This can be done using a *Join Index*. But you should check if you actually need to materialize & index this. How big is your table? – dnoeth Mar 08 '18 at 11:25
  • The main table adds 1M rows a day. Everyday I want to update some of those rows (300k aprox) and set the value of the virtual column so I can later merge those rows with another table. Hmm, as I'm reading this, I'm thinking I can add an empty column, index for it, and setting the value only when needed. After the merge is done I can set it back to NULL. Can this work? – RufusSC2 Mar 08 '18 at 11:43
  • Do you mean the SQL `MERGE`? Indexed columns should not have too many modifications, IMHO your idea is not really efficient. – dnoeth Mar 08 '18 at 16:51

0 Answers0