Reading from multiple tables

HP NonStop readers, MSSQLReader, and OracleReader can all read data from multiple tables using a single source. The MAP function allows data from each table to be output to a different stream.

The following reads all tables from the schema SCOTT.TEST.

CREATE SOURCE OraSource USING OracleReader (
  ... Tables:'SCOTT.%' ...

The following reads from all tables with names that start with SCOTT.TEST, such as SCOTT.TEST1, SCOTT.TESTCASE, and so on.

CREATE SOURCE OraSource USING OracleReader (
  ... Tables:'SCOTT.TEST%' ...

The following reads all tables with names that start with S and end with .TEST. Again, any tables added after the application starts will be ignored.

CREATE SOURCE OraSource USING OracleReader (
  ... Tables:='S%.TEST' ...

The following shows how to query data from one table when a stream contains data from multiple tables.

CREATE SOURCE OraSource USING OracleReader (
  ... Tables:'SCOTT.POSDATA;SCOTT.STUDENT' ...
)
OUTPUT TO Orders;
...
CREATE CQ renderOracleControlLogEvent
INSERT INTO oracleControlLogStream
    META(x,"OperationName"),
    META(x,"TxnID"),
    META(x,"TimeStamp").toString(),
    META(x,"TxnUserID”),
   data[0] 
FROM Orders x
WHERE META(x,”TableName").toString() = "SCOTT.POSDATA";

The following takes input from two tables and sends output for each to a separate stream using the MAP function. Note that a regular output stream (in this case Orders) must also be specified, even if it is not used by the application.

CREATE SOURCE OraSource USING OracleReader (
  ... Tables:'SCOTT.POSDATA;SCOTT.STUDENT' ...
)
OUTPUT TO OrderStream,
  PosDataStream MAP (table:'SCOTT.POSDATA'),
  StudentStream MAP (table:'SCOTT.STUDENT');

In some cases, creating a separate source for each table may improve performance.