Creating a Robust PROMPT SQL for use in InfoNet Scheduling – Part 1

In this blog post I am going to expound how to write an SQL such that when used in custom scheduling, the SQL is triggered only if it is the object being scheduled. If the code that I’m going to discuss isn’t included in a scheduling SQL, then the SQL will be triggered in the scheduling process even if it’s another object that is being scheduled, leading to undesirable consequences.

SQL Dialog Box

SQL Dialog Box

The above image shows an SQL for CCTV Surveys, the full code is shown below:

SELECT COUNT(*) INTO $COUNT FROM [CCTV Survey];
IF $COUNT > 0;

LIST $direction = ‘upstream’, ‘downstream’;

PROMPT TITLE ‘Select Direction’;
PROMPT LINE $DirectionSelected ‘Choose Direction Of Survey: ‘ STRING LIST $direction;

WHILE $DirectionSelected ‘upstream’ AND $DirectionSelected ‘downstream’;

PROMPT DISPLAY;

WEND;

IF $DirectionSelected = ‘upstream’;
SET direction = ‘U’;
SET start_manhole = ds_node_id;
SET finish_manhole = us_node_id;

ELSEIF $DirectionSelected = ‘downstream’;
SET direction = ‘D’;
SET start_manhole = us_node_id;
SET finish_manhole = ds_node_id;

ENDIF;

ENDIF;

The lines of interest are the first two:

SELECT COUNT(*) INTO $COUNT FROM [CCTV Survey];
IF $COUNT > 0;

Simply put, this ensures that if there are no objects of the type as specified, in the above example CCTV Surveys, then the code in the IF block isn’t executed.

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.