Creating a VIEW on the Foreign Stream that Performs a Basic Calculation

<< Click to Display Table of Contents >>

Navigation:  Building Streaming Applications > Tutorial 2: Introduction to Coding with Streaming SQL > Tutorial 2B: Coding Streaming SQL in SQLstream sqlLine >

Creating a VIEW on the Foreign Stream that Performs a Basic Calculation

Previous pageReturn to chapter overviewNext page

Now that we've created a foreign stream, and promoted a column to ROWTIME, we want to perform a basic calculation on this stream. We'll use a VIEW to do this as well.

We'll incorporate a WHERE clause into the new view.

The WHERE clause extracts records that meet a specified condition. The condition can be a numeric or string comparison, as in the following examples

SELECT STREAM * FROM Sales WHERE Employee='Bob';

SELECT STREAM * FROM Sales WHERE Customer_Type=1;

SELECT STREAM * FROM Sales WHERE Customer_Type<1;

 

You can use the following operators in a WHERE clause. For more information on operators and the WHERE clause, see Streaming SQL Reference Guide.

Operator

Description

=

Equality

!=

Inequality

<>

Inequality

>

Greater than

>=

Greater than or equal to (not less than)

<

Less than

<=

Less than or equal to (not more than)

NOT

Logical negation

AND

Conjunction

OR

Disjunction

IS

Logical assertion

IS NOT UNKNOWN

Negated unknown comparison:

<expr> IS NOT UNKNOWN

IS NULL

Null comparison:

<expr> IS NULL

IS NOT NULL

Negated null comparison:

<expr> IS NOT NULL

=

Equality

!=

Inequality

<>

Inequality

>

Greater than

>=

Greater than or equal to (not less than)

<

Less than

<=

Less than or equal to (not more than)

BETWEEN

Range comparison:

col1 BETWEEN expr1 AND expr2

IS DISTINCT FROM

Distinction

IS NOT DISTINCT FROM

Negated distinction

 

Here, we'll apply the WHERE clause to the "speeders" column, in order to limit results to those buses going faster than 75 km/hour.

Enter the following into SQLline:

CREATE or REPLACE VIEW "buses"."speeders" AS SELECT STREAM * FROM "buses"."buses_with_rowtime" WHERE "speed" > 75;

 

This creates a VIEW with all the columns of "buses_with_rowtime" in which the column "speed" has a value greater than 75.

Next, we'll create a more specific VIEW tailored to the needs of a map dashboard. This VIEW applies the same WHERE statement, but selects only "lat" "lon" and "speed". We'll use this VIEW in the next step of the tutorial to place the location of speeding buses on a map.

CREATE or REPLACE VIEW "buses"."speeders_map" AS SELECT STREAM "lat", "lon", "speed" FROM "buses"."buses_with_rowtime" WHERE "speed" > 75;

 

Next: Using s-Dashboard to show streaming data in dashboard.