Skip to main content

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;