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]





ociselfunc.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file ociselfunc.mos
   ```````````````````
   Accessing an Oracle database with SQL selection 
   statements and functions.
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2007, rev. Jul. 2010
*******************************************************!)

model "OCI selections and functions"
 uses "mmoci"

 parameters
  DB="myname/mypassword@dbname"     ! Login to Oracle database (not provided)
 end-parameters

 declarations
  Item: set of string
  COST1,COST2,COST3: dynamic array(Item) of real
 end-declarations

! setparam("OCIdebug",true)
 OCIlogon(DB)

! Select data depending on the value of a second field, the limit for which
! is given in a second table USER_OPTIONS
 OCIexecute("select ITEM,COST from MYDATA where DIST > (select MINDIST from USER_OPTIONS)", COST1)

! Select data depending on the values of ITEM
 OCIexecute("select ITEM,COST from MYDATA where ITEM in ('A', 'C', 'D', 'G')",
            COST2)

! Select data depending on the values of the ratio COST/DIST
 OCIexecute("select ITEM,COST from MYDATA where COST/DIST between 0.01 and 0.1",
            COST3)

 writeln("COST1: ", COST1, ", COST2: ", COST2, ", COST3: ", COST3)
	    
! Print the DIST value of ITEM 'B'
 writeln("Distance of 'B': ", 
         OCIreadreal("select DIST from MYDATA where ITEM='B'"))

! Number of entries with COST>30
 writeln("Count COST>30: ", 
         OCIreadinteger("select count(*) from MYDATA where COST>30"))

! Total and average distances
 writeln("Total distance: ", OCIreadreal("select sum(DIST) from MYDATA"), 
         ", average distance: ", OCIreadreal("select avg(DIST) from MYDATA"))

 OCIlogoff
 
end-model


**************************************************


! Creation of the data table in an Oracle database:


 declarations
  tsucc: array ({false,true}) of string
  ITEM: set of string
  CO, DI: array(ITEM) of real
 end-declarations

 tsucc(false):="failed"; tsucc(true):="succeeded"
 
 initializations from "ocidata.dat"
  [CO, DI] as "CostDist"
 end-initializations 

 OCIexecute("create table MYDATA (ITEM varchar(5), COST float, DIST float)")
 writeln(" - Create MYDATA (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into MYDATA (ITEM, COST, DIST) values (:1, :2, :3)", 
            [CO,DI])
 writeln(" - Insert values in MYDATA (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 OCIexecute("create table USER_OPTIONS (MINDIST float, ETC integer)")
 writeln(" - Create USER_OPTIONS (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into USER_OPTIONS (MINDIST) values (:1)", [500])
 writeln(" - Insert values in USER_OPTIONS (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

Back to examples browserPrevious exampleNext example