Rテーブル演算子の例 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL外部ルーチン プログラミング

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
2020年6月
Language
日本語
Last Update
2021-03-30
dita:mapPath
ja-JP/qwr1571437338192.ditamap
dita:ditavalPath
ja-JP/qwr1571437338192.ditaval
dita:id
B035-1147
Product Category
Software
Teradata Vantage
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;

結果:

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