FICO
FICO Xpress Optimization Examples Repository
FICO Optimization Community FICO Xpress Optimization Home
Back to examples browserPrevious exampleNext example

Formulation of SQL (selection) statements

Description
If some advanced SQL functionality is required when accessing a data source through an ODBC or OCI connection it will in general be necessary to formulate the corresponding SQL statements directly in the Mosel model instead of using the corresponding IO driver in initializations blocks. We show here two examples:
  1. Selection statements using columns in inverted order (ODBC: odbcinv.mos, Oracle: ociinv.mos)
  2. SQL with conditional selection statements and functions (ODBC: odbcselfunc.mos, Oracle: ociselfunc.mos)
  3. Retrieving information about database table structure: list of database tables, list of field names, primary keys (odbcinspectdb.mos)
  4. Implementing 'stop on error' behaviour for SQL statements (odbcchkstatus.mos)
Further explanation of this example:


Source Files

Data Files
multicol.mdb[download]
multicol.sqlite[download]
ocidata.dat[download]
odbcsel.mdb[download]
odbcsel.xls[download]
odbcsel.sqlite[download]





ocidata.dat

! Data file for `oci*.mos'

COST: [(prod1 1) 1.2 (prod1 3) 2.4 (prod2 3) 3 (prod4 1) 4 (prod4 4) 3.2
       (prod3 3) 5.7 (prod3 4) 2.9 (prod3 1) 3  ]

DUR: [(prod1 1) 3 (prod1 3) 2 (prod2 3) 1 (prod2 2) 2 (prod4 1) 5
      (prod4 4) 2 (prod3 3) 2 (prod3 4) 8 ]

CostDist: [ ('A') [10 100]  ('B') [20 2000] ('C') [30 300] 
            ('D') [40 5000] ('E') [50 1659] ]

Back to examples browserPrevious exampleNext example