InfoNet SQL: Overriding the Default Table – A Worked Example

It is possible to override the default table by explicitly naming the table or tables in the clause. This is done by using the ‘SELECT FROM’ keyword e.g. if the default table is Pump Station and you want to select all pipes with width greater than 100 mm you should say SELECT FROM Pipe WHERE width > 100.

Overriding the Default Table

Overriding the Default Table

In the unlikely event that you wish to select all pipes you can omit the keyword WHERE and the expression after it and simply say SELECT FROM Pipe. If the table name you wish to use contains spaces in it, it is necessary to put square brackets before and after the name e.g. SELECT FROM [All Links] WHERE width > 100. It is in fact possible to enclose any table name within square brackets so you can in fact say SELECT FROM [Pipe] WHERE width > 100.

A Worked Example

In InfoNet incidents and customer complaints are spread across numerous tables, these are; General Incident, Customer Complaint, Blockage Incident, Pollution Incident, Collapse Incident, Flooding Incident and Odor incident. So let us imagine that we wanted to carry out an SQL to display some statistics on all of these, perhaps a count on all incidents and customer complaints. The above approach provides the solution on how to combine syntax on all these objects into the one SQL.

For each of the objects in turn a count SQL and output grid would look like:

SELECT COUNT(*)

Individual Count() SQL

Individual Count() SQL

Count() SQL Output

Count() SQL Output

I will now introduce the use of a variable to hold the output of the Count() function, and create a table from this, the reason for doing this will become clear later.

SELECT COUNT(*) INTO $GeneralIncident;
SELECT MAX($GeneralIncident)

Introducing Variable

Introducing Variable

The output is identical as previous SQL with no variable.

Output identical as SQL with no variable

Output identical as SQL with no variable

Going further I can now override the default table.

SELECT COUNT(*) INTO $GeneralIncident FROM [General Incident];
SELECT MAX($GeneralIncident)

Overriding the default table with use of variable

Overriding the default table with use of variable

And taking this one step further to it’s logical conclusion combining all objects:

SELECT COUNT(*) INTO $GeneralIncident FROM [General Incident];
SELECT COUNT(*) INTO $CustomerComplaint FROM [Customer Complaint];
SELECT COUNT(*) INTO $BlockageIncident FROM [Blockage Incident];
SELECT COUNT(*) INTO $PollutionIncident FROM [Pollution Incident];
SELECT COUNT(*) INTO $CollapseIncident FROM [Collapse Incident];
SELECT COUNT(*) INTO $FloodingIncident FROM [Flooding Incident];
SELECT COUNT(*) INTO $OdorIncident FROM [Odor Incident];
SELECT MAX($GeneralIncident + $CustomerComplaint + $BlockageIncident + $PollutionIncident + $CollapseIncident + $FloodingIncident + $OdorIncident)

Combined syntax

Combined syntax

Output from combined syntax SQL

Output from combined syntax SQL

Share this post!

    About Idris Nujjoo

    Idris is the Product Sector Leader for InfoNet and InfoMaster and has been working for Innovyze (and its earlier incarnations) since 2001 and specifically working on InfoNet and Asset Management for the past decade.
    This entry was posted in InfoNet. Bookmark the permalink.