Using a Reference Table Query in SPLASH

This example uses a reference table query within a SPLASH routine that iterates over the rows in a table to obtain data about a specified player's batting history that is then used to compute statistics.

1. Obtain the necessary information about the external database table containing the information you wish to look up.
   • the name of the table containing the data
   • the schema for that table
   • the service to use to connect to it
2. Create the reference table query in your project.

   For example, for a table named Batting, with an integer column, teamAtBat, a string column, player, and an integer column, bases, that can be accessed by a service named HANA, create a reference table query named AtBats:

   CREATE REFERENCE AtBats
   SCHEMA (teamAtBat INTEGER, player STRING, bases INTEGER)
   PRIMARY KEY (teamAtBat)
   PROPERTIES service='HANA',source='Batting',sourceSchema='BattingSchema';

   The teamAtBat is incremented each time a player bats, providing a unique number for each at bat by a member of the team, so that column is the primary key in the source table. It must also be the primary key of the reference table query. Because we have specified a primary key in this reference, we have to include the sourceSchema property, which identifies the schema, BattingSchema, of the database containing the table.

3. Create the input stream for events to enter the project.

   For an event which is the appearance, or mention, of a player's name, create an input stream named StatRequest, consisting of a single string, player.

   CREATE INPUT STREAM StatRequest SCHEMA (player STRING);

4. Initialize the Flex operator, named Percentage, to read in the reference query AtBats and the input stream StatRequest, specify the output stream PercentageOutput, and set the variables atBats and hits to zero.

   CREATE FLEX Percentage IN AtBats, StatRequest
   OUT OUTPUT STREAM PercentageOutput SCHEMA (atBatPercentage float)
   BEGIN
      ON StatRequest {
         integer atBats := 0;
         integer hits := 0;
      

5. Close the Flex operator by dividing the number of hits by the number of atBats to calculate the batting average, percentage, placing that value in the atBatPercentage column of the output stream, and outputting it.

         }
         float percentage := hits/atBats;
         output [atBatPercentage=percentage];
      };
   END;

6. Between the initialization and close of the Flex operator, add a SPLASH routine to iterate through the entries in the Batting table to find out how many times the player was at bat and how many hits he got. There are three ways to do this.
   1. Use the for and if commands to handle iterating through the records, and take advantage of the AtBats_stream that is automatically created with the AtBats reference query.

            for ( record in AtBats_stream ) {
               if ( record.player = StatRequest.player ) {
                  if ( record.bases > 0 ) {
                     hits := hits + 1;
                  }
                  atBats := atBats + 1;
               }
            }
      

      This method has the advantage of being easy to read and maintain.

   2. Use the modified for command syntax that incorporates testing whether the player name from the input stream matches the player name in the table entry.

            for ( record in AtBats_stream where player = StatRequest.player ) {
               if ( record.bases > 0 ) {
                  hits:= hits + 1;
               }
               atBats := atBats + 1;
            }

      This method has the advantage of reducing processing time, because the records of other players are never pulled from the reference table.

   3. Explicitly create and modify the iterator to use across the table, again taking advantage of the AtBats_stream that is automatically created with the AtBats reference query.

           AtBats_iterator := getIterator( AtBats_stream );
          resetIterator( AtBats_iterator);
          setRange( AtBats_iterator, player, StatRequest.player );
          typeof(AtBats) result := getNext( AtBats_iterator );
          while (not(isnull(result))) {
            if ( result.bases > 0 ) {
              hits := hits + 1;
            }
            atBats := atBats + 1;
            result := getNext( AtBats_iterator );
          }

      This method has the advantage of giving you the most explicit control over the processing.