4. Using an update query

You can optionally use an update query in the InputDataSource section and the OutputDataSource section to add, delete, or update case data records. You can use an update query only if the CDSC that is being used can write case data and supports this type of operation (that is, the Can Add and Can Update properties are both True for the CDSC; for more information, see Supported features of the CDSCs).

2. Setting up a filter showed how to write SQL query statements to filter the data. Update queries use a different type of SQL statement, called data manipulation statements. The following data manipulation statements are supported by the UNICOM Intelligence Data Model:

1 Follow the links above to find out more about these SQL statements and use DM Query and the Museum sample UNICOM Intelligence Data File (.ddf) to try out the examples.

InputDataSource(myInputDataSource, "My input data source")
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrDataFileDsc; _
    Location=C:\Program Files\IBM\SPSS\DataCollection\
        <version>\DDL\Data\Data Collection File\museum.ddf; _
    Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\
        <version>\DDL\Data\Data Collection File\museum.mdd"
  SelectQuery = "SELECT * FROM VDATA _
      WHERE Respondent.Serial < 101"
  UpdateQuery = "DELETE FROM vdata _
      WHERE DataCollection.Status = {Test}"
End InputDataSource

OutputDataSource(myOutputDataSource, "My output data source")
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrDataFileDsc; _
    Location=C:\Program Files\IBM\SPSS\DataCollection\
        <version>\DDL\Output\UpdateQuery.ddf"
    MetaDataOutputName = "C:\Program Files\UNICOM\
        Intelligence\<version>\6\DDL\Output\UpdateQuery.mdd"
    UpdateQuery = "UPDATE vdata _
  SET DataCollection.FinishTime = Now()"
End OutputDataSource

The update query in the InputDataSource section uses an SQL DELETE statement to delete the test data. A WHERE clause is used to filter the case data records on the DataCollection.Status system variable. Use update queries in the InputDataSource section of your DMS files with care, because the update query changes the input data source irreversibly.

The update query in the OutputDataSource section uses an SQL UPDATE statement and the function to set the value of the DataCollection.FinishTime system variable to the current time.