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:

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]




odbcselfunc.mos

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

   file odbcselfunc.mos
   ````````````````````
   ODBC with selection statements and functions.
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2002, rev. Aug. 2023
*******************************************************!)

model "ODBC selection and functions"
 uses "mmodbc"
 options keepassert

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

 setparam("SQLdebug",true)

! SQLconnect('DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/odbcsel.xls')
! SQLconnect('odbcsel.xls')
! SQLconnect('DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/odbcsel.mdb')

 SQLconnect('odbcsel.mdb')

! SQLconnect('DSN=sqlite;DATABASE=odbcsel.sqlite')
! SQLconnect('odbcsel.sqlite')

 assert(getparam("SQLsuccess"))

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

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

! Select data depending on the values of the ratio COST/DIST
 SQLexecute("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': ", 
         SQLreadreal("select DIST from MYDATA where ITEM='B'"))

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

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

 SQLdisconnect
 
end-model
Back to examples browserPrevious exampleNext example
© Copyright 2024 Fair Isaac Corporation.