OUTPUT clause

Syntax

OUTPUT { [ALL] EVERY { count ROW[S] | interval [OFFSET BY interval] } } | { {AFTER | FIRST WITHIN} {count ROW[S] | interval} } | { [ALL] AT times_list } [ UNGROUPED | PER column [...] ]

Components
count	The number of rows to publish.
interval	An Interval literal specifying when to publish rows or the amount of time to shift the starting point for the time calculation.
times_list	A list of times indicating when the window should be emptied. See times_list for more information.
column	The name of a column in the data source, for grouping purposes.

times_list

time_spec | (time_spec [, ...])

Component
time_spec	A time specification. See time_spec for more information.

time_spec

{ ' [ SUN | MON | TUE | WED | THU | FRI | SAT ] hour : minute [ : second [. fraction ]] [timezone]' } |STARTUP

Components
hour	A value from 0 to 23 indicating the hour of the day. Must be preceded by at least one space.
minute	A value from 0 to 59 indicating the minute.
second	A value from 0 to 59 indicating the second.
fraction	A value from 0 to 999999 indicating the fraction of a second.
timezone	A string representing the time zone. If omitted, assumes the local time zone. See "Sybase CEP Time Zone Database" in the Sybase CEP Integration Guide for more information about valid time zone strings. Must be preceded by at least one space.

Usage

The OUTPUT clause can be used as the last clause of a Query statement, Database statement, or Remote Procedure statement, and is required in an Insert Values statement. OUTPUT is the last clause to be executed, after the statement performs all other data filtering and processing, and before it generates output. The OUTPUT clause comes in four variations, each of which has a distinct syntax and usage: OUTPUT EVERY and OUTPUT ALL EVERY, OUTPUT AFTER, OUTPUT FIRST WITHIN, and OUTPUT AT.

In a Query statement, Database statement, or Remote Procedure statement, all variations of the OUTPUT clause control the statement's output based on the number of rows arriving in the statement's data sources, or the rows' time of arrival. The OUTPUT clause processes these rows regardless of window definitions, and is unaffected by the statement's KEEP clause conditions, if any are present. An Insert Values statement uses only the OUTPUT AT variation of the OUTPUT clause, which determines when values are generated and inserted into the specified stream or window.

OUTPUT EVERY and OUTPUT ALL EVERY

Limits the statement's output to specific time intervals, or number of rows received. The output specification can be indicated as a number of rows or as a time interval.

A time-based OUTPUT EVERY clause publishes one or no rows every interval expression, where interval expression is the interval specified in the clause. If output from the statement was generated within the last interval expression, the most recently generated row is published. If no rows were generated within the last interval expression, no rows are published.
A time-based OUTPUT ALL EVERY clause publishes the row most recently generated by the statement every interval expression, as specified in the clause. The row is published whether or not it was generated during the most recent interval expression or during a previous interval expression. If the statement generated no rows since it started running, no rows are published.

By default, interval expression calculation for the OUTPUT EVERY and OUTPUT ALL EVERY clause is calculated from midnight of January 1, 1970 GMT/UTC. This starting time can be offset by the interval expression specified in the OFFSET BY subclause.

A count-based OUTPUT EVERY clause publishes every Nth row of output from the statement, where N is the number of rows specified as integer expression in the clause.
A count-based OUTPUT ALL EVERY clause behaves identically to the count-based OUTPUT EVERY clause. (Since the OUTPUT EVERY clause publishes the Nth row whenever it is generated by the statement, and does not publish a row until the Nth row is generated, the concept of publishing rows from a previous interval does not apply).

The OUTPUT EVERY clause can be combined with a GROUP BY clause. In the following descriptions, combination refers to a unique combination of values in the list of columns and, optionally, the timestamp referenced in the GROUP BY clause:

A time-based OUTPUT EVERY clause, in conjunction with GROUP BY, publishes rows every interval expression, as specified in the clause. OUTPUT EVERY publishes the most recent row for every combination defined in the GROUP BY clause, for which rows were generated in the most recent interval expression. No rows from previous interval expressions are published.
A time-based OUTPUT ALL EVERY clause, in conjunction with GROUP BY, publishes rows every interval expression, as specified in the clause. OUTPUT ALL EVERY publishes the most recent row for every combination defined by the GROUP BY clause encountered from the time the statement started executing. If no row was generated by the statement for a specific combination within the most recent interval expression, the most recent row from a previous interval expression is published. If the statement generated no rows since it started running, no rows are published.
A count-based OUTPUT EVERY clause, in conjunction with GROUP BY, publishes every Nth row of output from the statement for every combination defined by the GROUP BY clause, where N is the number of rows specified as integer expression in the clause.
A count-based OUTPUTALLEVERY clause, in conjunction with GROUP BY, behaves identically to the count-based OUTPUTEVERY clause, in conjunction with GROUP BY.

OUTPUT AFTER

Delays the publication of all rows generated by the statement, either by the specified interval, or until such time as the specified number of subsequent rows have been generated. However, no rows are filtered out with this clause; all rows that would otherwise have been published are published eventually.

OUTPUT FIRST WITHIN

Publishes only the first row generated by the statement, within a specified time interval, or within a specified number of rows, using the following rules:

The first row generated by the statement is published.
For count-based clauses, the N-1 subsequent rows generated by the statement are ignored, where N is the number of rows specified as integer expression in the OUTPUT FIRST WITHIN clause. For time-based clauses, all rows generated by the statement within the next interval expression are ignored, as specified in the OUTPUT FIRST WITHIN clause.
The first row to be generated by the statement after the specified number of rows are ignored, or the specified interval elapses, is published.
The interval or row count restarts and all subsequent rows are ignored until the count expires.

When the OUTPUT FIRST WITHIN clause is combined with a GROUP BY clause, these steps are performed separately for every combination defined in the GROUP BY.

OUTPUT AT

In a Query statement, Database statement, or Remote Procedure statement, an OUTPUT AT clause limits statement output to one or more daily or weekly times, as specified by a times list. The times list can include STRING literals and STRING type parameters. Times listed in the times list can include the day of the week. If no day of the week is specified, output occurs at the indicated time daily. The listed times can also specify a time zone. If no time zone is specified, rows are removed according to local time. The STARTUP keyword cannot be used in these statements. An OUTPUT AT clause is also required in an Insert Values statement, where it specifies the times at which rows are generated and published to the destination window or stream. The Insert Values statement OUTPUT AT can use the STARTUP keyword.

For more information, see OUTPUT AT BEHAVIOR IN THE QUERY STATEMENT, DATABASE STATEMENT AND REMOTE PROCEDURE STATEMENT and OUTPUT AT BEHAVIOR IN THE INSERT VALUES STATEMENT.

Note:

Time changes, such as the change between standard and daylight savings time, may cause undesired results in the behavior of the OUTPUT AT and OUTPUT ALL AT clauses when the time change occurs.

To avoid this problem, Sybase recommends omitting times of the day or week that coincide with the time change from your time list.

OUTPUT AT in the QUERY Statement, DATABASE Statement and REMOTE PROCEDURE Statement

An OUTPUT AT clause publishes one or no rows at the specified times. If output from the statement was generated since the last specified time, the most recently generated row is published. If no rows were generated since the last output time, no rows are published.
An OUTPUT ALL AT clause publishes the row most recently generated by the statement at the specified times. The row is published whether or not it was generated since the last output time. If the statement generated no rows since it started running, no rows are published.

The OUTPUT AT clause can be combined with a GROUP BY clause. In the following descriptions, combination refers to a unique combination of values in the list of columns and, optionally, the timestamp referenced in the GROUP BY clause:

An OUTPUT AT clause, in conjunction with GROUP BY, publishes rows at the specified times. OUTPUT AT publishes the most recent row for every combination defined in the GROUP BY clause, for which rows were generated since the last output time. No rows generated before the last output time are published.
An OUTPUT ALL AT clause, in conjunction with GROUP BY, publishes rows at the specified times. OUTPUT ALL AT publishes the most recent row for every combination defined by the GROUP BY clause encountered from the time the statement started executing. If no row was generated by the statement for a specific combination since the last output time, the most recent row from before the last output time is published. If the statement generated no rows since it started running, no rows are published.

OUTPUT AT in the INSERT VALUES Statement

Both the OUTPUT AT and the OUTPUT ALL AT clauses produce the rows specified by the VALUES clause and publish them to the statement's destination either at the specified times of the week or day, or on project startup (which you specify with the STARTUP keyword). In a query module where persistence is not turned on, STARTUP refers to any time when the project containing the module starts. In a query module where persistence is turned on, "startup" refers to the time when the project containing the module first starts or starts with a clean slate.

PER Clause

If you specify explicit grouping with a PER subclause, your output specifications apply to each unique combination of values in the indicated input columns. If you omit the PER subclause, any GROUP BY clause in the statement applies to the output clause. If you specify UNGROUPED, no grouping applies. Note that, while you can use grouping with OUTPUT AFTER interval, the grouping has no effect and generates a warning message.

Restrictions

The Insert Values statement uses only the OUTPUT AT variation of the OUTPUT clause. OUTPUT EVERY, OUTPUT ALL EVERY, OUTPUT AFTER, and OUTPUT FIRST WITHIN cannot be used with this statement.
Only the Insert Values statement can use the STARTUP keyword in an OUTPUT AT clause.

Examples

The following example publishes the most recent row for every value in the Symbol column within a thirty-second period, where the value in the Price column exceeds fifty:

INSERT INTO OutStream
SELECT Symbol, Price
FROM Trades
WHERE Price > 50
GROUP BY Symbol
OUTPUT EVERY 30 SECONDS;

This query publishes the value of the Temp column of the TempIn stream for rows with "New York" in the Location column. All output is delayed by ten seconds:

INSERT INTO InStream
SELECT Temp
FROM TempIn
WHERE TempIn.Location = 'New York'
OUTPUT AFTER 10 SECONDS;

This example publishes every third row from the SFTemp stream:

INSERT INTO OutStream
SELECT Temp, MAX(Temp)
FROM SFTemp KEEP 5 ROWS
OUTPUT FIRST WITHIN 3 ROWS;

This example publishes daily at 6:00 o'clock in the evening, any rows with a value greater than 100 in the Wind column that were generated in the last 24 hours:

INSERT INTO OutStream
SELECT Wind, Time
FROM Weather
WHERE Wind > 100
OUTPUT AT '18:00:00';