Using FIRST and LAST

The FIRST and LAST functions return a Java.lang.Object type no matter what type they operate on. They support all Supported data types except Byte.

For example, in the following, suppose x is an integer:

SELECT
FIRST(x) – LAST(x) AS difference
FROM
MyWindow;

This results in an “invalid type of operand” error because you can’t perform simile arithmetic on a Java.lang.Object. To work around this, you must recast the object as an integer:

SELECT
TO_INT(FIRST(x)) – TO_INT(LAST(x)) AS difference
FROM
MyWindow;

The following example uses the PosApp sample data:

CREATE SOURCE CsvDataSource USING FileReader (
  directory:'Samples/PosApp/appData',
  wildcard:'PosDataPreview.csv',
  blocksize: 10240,
  positionByEOF:false
)
PARSE USING DSVParser (
  header:Yes,
  trimquote:false
)
OUTPUT TO CsvStream;
 
CREATE CQ CsvToPosData
INSERT INTO PosDataStream
SELECT TO_INT(data[3]) AS PosData
FROM CsvStream;

CREATE WINDOW Window1 OVER PosDataStream KEEP 2 ROWS ;
 
CREATE CQ CQ1
INSERT INTO output_ST
SELECT TO_INT(FIRST( Window1.PosData ))
  - TO_INT(LAST( Window1.PosData )) AS LastPosData
FROM Window1;

CREATE TARGET Target1 USING SysOut (
  name: 'SubFirstFromLast'
)
INPUT FROM output_ST;

The following example demonstrates how to convert the Java.lang.Object returned by LAST to a WAEvent for use in a CDC pipeline.

CREATE SOURCE ORA_CDC_SOURCE USING OracleReader (...) 
  OUTPUT TO ORA_CDC_STREAM;

CREATE JUMPING WINDOW ORA_CDC_WINDOW OVER ORA_CDC_STREAM 
  KEEP WITHIN 15 SECOND;

CREATE CQ ORA_CDC_CQ
  INSERT INTO CQ_ORA_CDC_STREAM 
  SELECT CAST(Last(j) as com.webaction.proc.events.WAEvent) FROM ORA_CDC_WINDOW ORA_CDC_STREAM j
  GROUP BY TO_INT(data[0]);;

CREATE TARGET ORA_CDC_TARGET USING DatabaseWriter (...) 
  INPUT FROM CQ_ORA_CDC_STREAM;