16.20 - R Table Operator Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)
CREATE TABLE tab1(col1 INTEGER,col2 INTEGER)PRIMARY INDEX(COL1);
INSERT INTO tab1 VALUES(1, 20);
INSERT INTO tab1 VALUES(2, 30);
INSERT INTO tab1 VALUES(3, 40);
INSERT INTO tab1 VALUES(4, 50);

SELECT *
FROM TD_SYSGPL.ExecR (
   on (select * from tab1)
   hash by col1
   local order by col2
   using contract('library(tdr);
         stream<-0;
         direction<-"R";

         incols<-tdr.GetColDef(stream, direction);
         tdr.SetOutputColDef(stream,incols)')
      operator('library(tdr);
         stream<-0;
         direction<-"R";
         direction1<-"W";
         options<-0;
         inHandle<-tdr.Open(direction, stream, options);

         print(inHandle);
         outHandle<-tdr.Open(direction1, stream, options);

         print(outHandle);
         colcount <- tdr.GetColCount(stream , direction);

         colcount <- colcount -1 ;
         while(tdr.Read(inHandle)== 0)
         {
            for( index  in 0:colcount )
               {
                 att <- tdr.GetAttributeByNdx( inHandle , index , NULL);
                 tdr.SetAttributeByNdx(outHandle , index , att, NULL);
               };
               tdr.Write(outHandle);
            };
            tdr.Close(inHandle);
            tdr.Close(outHandle);')
) as d1;

Results:

   col1      col2
-------------------
    1         20
    4         50
    2         30
    3         40