FICO Xpress Optimization Examples Repository: Formulation of SQL (selection) statements

FICO Xpress Optimization Examples Repository

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:

Selection statements using columns in inverted order (ODBC: odbcinv.mos, Oracle: ociinv.mos)
SQL with conditional selection statements and functions (ODBC: odbcselfunc.mos, Oracle: ociselfunc.mos)
Retrieving information about database table structure: list of database tables, list of field names, primary keys (odbcinspectdb.mos)
Implementing 'stop on error' behaviour for SQL statements (odbcchkstatus.mos)

Further explanation of this example:

odbcsel.zip

[download all files]

Source Files

By clicking on a file name, a preview is opened at the bottom of this page.

odbcinv.mos	[download]
odbcselfunc.mos	[download]
odbcchkstatus.mos	[download]
ociinv.mos	[download]
ociselfunc.mos	[download]
odbcinspectdb.mos	[download]

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] ]