JavaScript QSQL Statements

 

Statements Overview

This chapter is a reference guide to the JavaScript QSQL statements. These statements contain the basic control operators for the AIS QSQL database system. All of the basic building blocks of QSQL system are contained in these statements.

The Analytic Information Server dialect of javaScript is case sensitive.

create

The create statement creates the specified table and saves the new table in the QSQL system.


Syntax:   create tableName;

tableName The name of the newly created QSQL table.


Syntax:   create tableName pathAndFileName;

tableName The name of the newly created QSQL table.
pathAndFileName The full path and file name of the data for the newly created table.


Syntax:   create tableName columnVector;

tableName The name of the newly created QSQL table.
columnVector The vector of column names for the newly created table.

Example1

create sales;

This will create a new table named "sales" and save it in the QSQL system.

Example2

create sales append(_path,"/","Sales.csv");

This will create a new table named "sales" (importing data from the specified file) and save it in the QSQL system.

Example3

create sales new('Vector',3,'Employee','Address','TotalSales');

This will create a new table named "sales" (using the specified column names) and save it in the QSQL system.

drop

The drop statement deletes the specified database or table from the QSQL system.


Syntax:   drop database databaseName;

database Mandatory Keyword
DATBASENAME The QSQL database to be deleted from the QSQL system


Syntax:   drop table tableName;

table Mandatory Keyword
TABLENAME The QSQL table to be deleted from the current QSQL database

Example1

drop database main;

This will delete the database named "main" from the QSQL system.

Example2

drop table sales;

This will delete the table named "sales" from the current QSQL database.

Filter all

The filter all statement performs a deletion operation on the current QSQL table view. The backup table view is not effected.

Syntax:filter tableName all filterexpression;

filter Keyword (optional)
tableName The QSQL table name to be filtered
all Mandatory keyword
filterexpression Filter expression.

Example1

filter corpsales all Sales > 10000;

This will delete all rows from the current table view where Sales are NOT greater than 10000. Any rows, where Sales are #void, will be eliminated from the current table view. The backup table view will not be altered.

Filter bottom

The filter bottom statement performs a sort on the current QSQL table, followed by a deletion operation on the current table.

Syntax: filter tableName bottom sortexpression cutoff

filter Keyword (optional)
tableName The QSQL table name to be filtered (optional)
bottom Mandatory keyword
sortexpression Sort expression.
cutoff Cut off absolute number or percent.

Example1

filter corpsales bottom Sales * Price 100;

This will delete all rows from the current table view which are not in the top 100 rows of all (Sales * Price). Any rows, where Sales or Price are #void, will be eliminated from the current table view. The backup table view will not be altered.

Notes and Hints

Any names will be treated as table row field references.

Filter check

The filter check statement performs a deletion operation on the current QSQL table.

Syntax:filter tableName check name1 ... nameN

filter Keyword (optional)
tableName The QSQL table name to be filtered (optional)
check Mandatory keyword
name1 Field name (mandatory).
nameN Field name (optional).

Example1

filter corpsales check Sales Price;

This will delete all rows from the current table view where either Sales or Price are #void. The backup table view will not be altered.

Notes and Hints

Any names will be treated as table row field references.

Filter omit

The filter omit statement performs a deletion operation on the current table QSQL table. The current view of the table is permanently altered, and the back up view of the table is permanently altered. The omit statement includes an implied checkoff statement.

Syntax:filter tableName omit filterexpression

filter Keyword (optional)
tableName The QSQL table name to be filtered (optional)
omit Mandatory keyword
filterexpression Filter expression.

Example1

filter corpsales omit Sales > 10000;

 

This will delete all rows from the current table view where Sales are greater than 10000. The current table view will be altered. The backup table view will be permanently altered.

Notes and Hints

Any names will be treated as table row field references.

Filter restore

The filter restore statement restores the backup view of the current QSQL table.

Syntax:filter tableName restore

filter Keyword (optional)
tableName The QSQL table name to be filtered (optional)
restore Mandatory keyword

Example1

filter corpsales restore;

This will restore the backup view of the current QSQL table. The backup table view will not be altered.

Notes and Hints

Any names will be treated as table row field references.

Filter set

The filter set statement performs a update on the current QSQL table view.

Syntax:filter tableName set fieldName=expression

filter Keyword (optional)
tableName The QSQL table name to be filtered (optional)
set Mandatory keyword
fieldName Name of the field, in each row of the current view, which is to be set with a new value.
= Optional assignment symbol
expression The new value which is to be set into each row of the current view.

Example1

filter corpsales set Commission 10%;

This will set the Commission field to 10%, in all rows in the current table view. The current table view will be replaced with the new backup view. The backup table view will be permanently altered.

Notes and Hints

Any names will be treated as table row field references.

Filter setnr

The filter setnr statement performs an update on the current QSQL table without reseting the current view.

Syntax:filter tableName setnr fieldName=expression

filter Keyword (optional)
tableName The QSQL table name to be filtered (optional)
setnr Mandatory keyword
fieldName Name of the field, in each row of the current view, which is to be set with a new value.
= Optional assignment symbol
expression The new value which is to be set into each row of the current view.

Example1

filter corpsales setnr Commission 10%;

This will set the Commission field to 10%, in all rows in the current table view. The current table view will not be reset. The backup table view will be permanently altered.

Notes and Hints

Any names will be treated as table row field references.

Filter slice

The filter slice statement performs an ascending sort on the current QSQL table, followed by a tile operation on the current table.

Syntax:filter tableName slice sortexpression tileIndex of tileCount

filter Keyword (optional)
tableName The QSQL table name to be filtered (optional)
slice Mandatory keyword
sortexpression Sort expression.
tileIndex The index of the tile to show.
of Mandatory keyword.
tileCount The number of tiles.

Example1

filter corpsales slice (Sales * Price) 42 of 100;

This will retain all rows from the current table view which are in the 42 percentile of (Sales * Price). Any rows, where Sales or Price are #void, will be eliminated from the current table view. The backup table view will not be altered.

Notes and Hints

Any names will be treated as table row field references.

Filter top

The filter top statement performs a sort on the current QSQL table, followed by a deletion operation on the current table.

Syntax:filter corpsales top sortexpression cutoff

filter Keyword (optional)
tableName The QSQL table name to be filtered (optional)
top Mandatory keyword
sortexpression Sort expression.
cutoff Cut off absolute number or percent.

Example1

filter corpsales top Salary 10%;

This will delete all rows from the current table view which are not in the top 10% of all Salaries. Any rows, where Salary is #void, will be eliminated from the current table view. The backup table view will not be altered.

Notes and Hints

Any names will be treated as table row field references.

 

import

The import statement creates the specified table (importing the specified data) and saves the new table in the QSQL system.


Syntax:   import tableName pathAndFileName;

tableName The name of the newly created QSQL table.
pathAndFileName The full path and file name of the data for the newly created table.

Example2

import sales append(_path,"/","Sales.csv");

This will create a new table named "sales" (importing data from the specified file) and save it in the QSQL system.

list

The list statement displays the names of the tables from the QSQL repository onto the console.


Syntax:   list;

none There are no arguments for the list statement.

Example1

// This will displays the names of the tables from the QSQL repository onto the console.

list;

load

The load statement loads the specified table into the QSQL system main memory.


Syntax:   load tableName;

tableName The name of the table to be loaded into the QSQL system main memory.

Example1

// This will load the table named "sales" into the QSQL system main memory.

load sales;

save

The save statement saves the specified table in the QSQL system repository.


Syntax:   save tableName;

tableName The name of the table to be saved in the QSQL system repository.

Example1

save sales;

This will save table named "sales" in the QSQL system repository.

score

The score statement performs a set of scoring operations on the QSQL table. The score statement must be followed by a single statement. The score statement supports commands for averaging and totalling rows from the current view index. The score statement does not alter the current table view. The backup table index is not altered by the score statement.

Syntax: score command;

score Keyword (optional)
TABLENAME The QSQL table name to be filtered (optional)
score Keyword (optional)
command score statement command (mandatory).


Syntax:   score expression;

score Keyword (optional)
TABLENAME The QSQL table name to be filtered (optional)
score Keyword (mandatory)
expression Any valid javaFilter expression including imbedded score statement commands (mandatory).


Syntax:   score variable=command;

score Keyword (optional)
TABLENAME The QSQL table name to be filtered (optional)
score Keyword (optional)
variable A variable name to receive the finished score.
= The javaFilter assignment operator.
command score statement command (mandatory).

Notes and Hints

Note1: The score statement generates code which assumes that the variable named, cursor, contains the QSQL table which is to be scored.

Note2: The score statement may be used as a stand alone statement in a global declaration or inside an existing function. A stand alone filter statement is automatically enclosed in the following universal parse tree lambda:

(lambda (cursor)
(begin ...score statement block...)
)

Note3: The score statement always automatically converts any name symbol into a row field reference.

Score Command: average

The average command averages a numeric expression over each row in the current QSQL table.

Syntax: average expression

score Keyword (optional)
TABLENAME The QSQL table name to be filtered (optional)
average Mandatory keyword
expression Numeric expression.

Example1

score average Sales;

This will return the average of Sales for the current table. Any rows, where Sales are #void, will be treated a zero values in the average. The current table view will not be altered. The backup table view will not be altered.

Notes and Hints

Any names will be treated as table row field references.

Score Command: averageForAll

The averageForAll command averages a numeric expression over ALL rows in the current QSQL table.

Syntax: averageForAll expression

score Keyword (optional)
TABLENAME The QSQL table name to be filtered (optional)
averageForAll Mandatory keyword
expression Numeric expression.

Example1

score averageForAll Sales;

This will return the average of Sales for the backup table. Any rows, where Sales are #void, will be treated a zero values in the average. The current table view will not be altered. The backup table view will not be altered.

Notes and Hints

Any names will be treated as table row field references.

Score Command: total

The total command totals a numeric expression over each row in the current QSQL table.


Syntax:   total expression

score Keyword (optional)
TABLENAME The QSQL table name to be filtered (optional)
total Mandatory keyword
expression Numeric expression.

Example1

score total Sales;

This will return the total of Sales for the current table. Any rows, where Sales are #void, will be treated a zero values in the total. The current table view will not be altered. The backup table view will not be altered.

Notes and Hints

Any names will be treated as table row field references.

Score Command: totalForAll

The totalForAll command totals a numeric expression over ALL rows in the current QSQL table.

Syntax:: totalForAll expression

score Keyword (optional)
TABLENAME The QSQL table name to be filtered (optional)
totalForAll Mandatory keyword
expression Numeric expression.

Example1

score totalForAll Sales;

This will return the total of Sales for the backup table. Any rows, where Sales are #void, will be treated a zero values in the total. The current table view will not be altered. The backup table view will not be altered.

Notes and Hints

Any names will be treated as table row field references.

select

The select statement creates a new table by conditionally selecting data from two other tables. The user specifies the names of the columns for the new table, the manner in which each new column's data is to be extracted from the two existing tables, and any conditions which are imposed before a new row is to be created.

Syntax:   select { newColNames } from xTableName yTableName into newTableName with { newColExpressions } join xJoinColName yJoinColName where condition

newColNames The list of column names for the new table.
from Mandatory keyword.
xTableName The QSQL table name to be used as the x table.
yTableName The QSQL table name to be used as the y table.
into Mandatory keyword.
newTableName The name of the new QSQL table to be created.
with Mandatory keyword.
newColExpressions The list of new JavaScript column expressions for extracting data for each new table record.
join Mandatory keyword.
xJoinColName The name of the x table join column.
yJoinColName The name of the x table join column.
where Mandatory keyword.
condition The javaScript condition which must be true if a new record is to be created.

Example1

select { Date, Price, Vol } from SPX VIX into NEW with { x.Date, x.Close, y.Close } join Date Date where ((x.Close > x.Open) && (y.Close y.Open));

This will create a new table named, NEW. The NEW table will contain the columns named: Date, Price, and Vol. A new record will be added to the NEW table only under the following conditions: the SPX Date and VIX Date values must match; the SPX must have closed higher than it opened; and the VIX must have closed higher than it opened. For every pair of SPX and VIX records where those conditions are true, the new record will be constructed as follows: NEW.Date=x.Date; NEW.Price=x.Close; NEW.Vol=y.Close;

Notes and Hints

The join clause allows the select to run much faster as an outter join is not performed.

Syntax:   select { newColNames } from xTableName yTableName into newTableName with { newColExpressions } where condition

newColNames The list of column names for the new table.
from Mandatory keyword.
xTableName The QSQL table name to be used as the x table.
yTableName The QSQL table name to be used as the y table.
into Mandatory keyword.
newTableName The name of the new QSQL table to be created.
with Mandatory keyword.
newColExpressions The list of new JavaScript column expressions for extracting data for each new table record.
where Mandatory keyword.
condition The javaScript condition which must be true if a new record is to be created.

Example2

select { SPXDate, VIXDate, Price, Vol } from SPX VIX into NEW with { x.Date, y.Date, x.Close, y.Close } where ((x.Close > x.Open) && (y.Close y.Open));

This will create a new table named, NEW. The NEW table will contain the columns named: SPXDate, VIXDate, Price, and Vol. A new record will be added to the NEW table only under the following conditions: the SPX must have closed higher than it opened; and the VIX must have closed higher than it opened. For every pair of SPX and VIX records where those conditions are true, the new record will be constructed as follows: NEW.SPXDate=x.Date; NEW.VIXDate=y.Date; NEW.Price=x.Close; NEW.Vol=y.Close;

Notes and Hints

This select will perform an outter join and will require a great deal of processing time.

Syntax:   select { newColNames } from xTableName into newTableName with { newColExpressions } where condition

newColNames The list of column names for the new table.
from Mandatory keyword.
xTableName The QSQL table name to be used as the x table.
into Mandatory keyword.
newTableName The name of the new QSQL table to be created.
with Mandatory keyword.
newColExpressions The list of new JavaScript column expressions for extracting data for each new table record.
where Mandatory keyword.
condition The javaScript condition which must be true if a new record is to be created.

Example3

select { Date, Price, Gap } from SPX into NEW with { x.Date, x.Close, (y.Close - y.Open) } where (x.Close > x.Open);

This will create a new table named, NEW. The NEW table will contain the columns named: Date, Price, and Gap. A new record will be added to the NEW table only under the following conditions: the SPX must have closed higher than it opened. For every SPX record where those conditions are true, the new record will be constructed as follows: NEW.Date=x.Date; NEW.Price=x.Close; NEW.Gap=(y.Close - y.Open);

Notes and Hints

This select will perform single pass of the SPX table and will require a linear amount of processing time.

show

The show statement displays the specified table on the console.


Syntax:   show tableName startRow rowCount fieldWidth;

tableName The name of the table to be saved in the QSQL system repository.
startRow The starting row of the table to be displayed on the console.
rowCount The number of rows to be displayed on the console.
fieldWidth The width of each field to be displayed on the console.

Example1

// This will display the table named "sales" on the console.

show sales 22;

sort

The sort statement performs a sort on the current QSQL table.

Syntax:   sort tableName direction columnName ... direction columnName

sort Mandatory keyword
tableName The QSQL table name to be filtered
direction Mandatory Sort direction (up or down).
columnName Mandatory column to be sorted.


Syntax:   sort tableName direction columnName direction columnName

sort Mandatory keyword
tableName The QSQL table name to be filtered
direction Mandatory Sort direction (up or down).
columnName Mandatory column to be sorted.
direction Sort direction (up or down).
columnName column to be sorted.


Syntax:   sort tableName direction columnName direction columnName direction columnName

sort Mandatory keyword
tableName The QSQL table name to be filtered
direction Mandatory Sort direction (up or down).
columnName Mandatory column to be sorted.
direction Sort direction (up or down).
columnName column to be sorted.
direction Sort direction (up or down).
columnName column to be sorted.

Example1

filter corpsales sort down Sales up Profits;

This will sort all rows in the current table view in descending order by Sales and then in descending order by Profits as a minor sort field. Any rows, where Sales or Profits is #void, will be eliminated from the current table view. The backup table view will not be altered.

Notes and Hints

There may be more than one direction and columnName.

unload

The unload statement deletes the specified table from the QSQL system main memory but NOT from the QSQL repository.


Syntax:   unload tableName;

TABLENAME The QSQL table to be deleted from the current QSQL database main memory but NOT from the QSQL repository.

Example1

unload sales;

This will delete the table named "sales" from the current QSQL database main memory but NOT from the QSQL repository.

Use databaseName

The use databaseName statement specifies a database name for the current focus. If the named database does not exist, it will be created. A use statement must be issued before any further QSQL commands can be issued by the QSQL system.

Syntax: use databaseName;

databaseName Mandatory database name

Example1

        // Specify the database named "main" as the current focus of the QSQL system.
        use main;

This will set the database named "main" as the current focus of the QSQL system. Any further QSQL statements will operate on the "main" database.

Notes and Hints

If the specified database does not exist, it will be created.

Use databaseName clear:

The use databaseName clear statement specifies a database name for the current focus. If named database does not exist, it will be created. If the database does exit, it will be cleared of all existing tables. A use statement must be issued before any further QSQL commands can be issued by the QSQL system.

Syntax: use databaseName clear:

databaseName Mandatory database name
clear: Command to clear the database.

Example1

        // Specify the database named "main" as the current focus of the QSQL system and clear it.
        use main clear:;

This will set the database named "main" as the current focus of the QSQL system. Any existing tables in this database will be cleared. Any further QSQL statements will operate on the "main" database.

Notes and Hints

If the specified database does not exist, it will be created.

view

The view statement displays the specified table in Excel.


Syntax:   view tableName;

tableName The name of the table to be viewed in Excel.

Example1

// This will display the table named "sales" in Excel.

view sales;