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;