Using a Reference Table Query in SPLASH to Get a Single Row from a Table

This example uses a reference table query within a SPLASH routine to obtain a single row from a table.

1. Obtain the necessary information about the external database table containing the row you want to obtain.
   • the name of the table containing the data
   • the schema for that table
   • the service to use to connect to it
2. Create a second reference table query in your project.

   For example, for a table named chainList, with an integer column, chainID, a string column, city, a string column, name, and a string column, headquartersAddress, that can be accessed by a service named HANA, create a reference table query named chainList:

   CREATE REFERENCE restaurants
   
   SCHEMA (storeID INTEGER, city STRING, name STRING, address STRING)
   PROPERTIES service='HANA',source='restaurants';

   Again, because we have not specified a primary key in this reference, we can omit the sourceSchema property.

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

   Create an input stream named restaurantQuery, consisting of a single string, city.

   CREATE INPUT STREAM StatRequest SCHEMA (player STRING);

4. Initialize the Flex operator, named optionQuery, to read in the reference table queries restaurants and chainList, and the input stream restaurantQuery, output the restaurant chain names in the output stream chainsInCity when it receives the name of a city in the restaurantQuery stream.

   CREATE FLEX optionQuery IN restaurants, chainList, restaurantQuery
   OUT OUTPUT STREAM chainsInCity SCHEMA (name string, address string)
   BEGIN
      ON restaurantQuery {
         
      

5. Add a SPLASH routine to produce the list of chains that have a presence in the city. For each restaurant chain in the chainList table, it grabs the first instance of that chain name in the restaurants table whose address is in the city specified in the restaurantQuery input stream and outputs the restaurant chain name and address.

         for (record in chainList_stream) {
            typeof(restaurants) inCity := restaurants_stream{[city=restaturantQuery.city;name=record.name;]};
            output [name=inCity.name;address=restaurants.address;];
         }

6. Since we are not performing any calculations this time, simply close the Flex operator.

      };
   END;