SQL selection statements

As has been said before, using SQL statements instead of initializations blocks gives the user considerably more freedom in the formulation of his SQL queries. In this section we are going to show examples of advanced functionality that cannot be achieved with initializations blocks.

We are given a database with two tables. The first table, called MYDATA, has the following contents.

The second table, USER_OPTIONS, defines a few parameters, that is, it has only a single entry per field/column. We may perform, for instance, the following tasks:

• Select all data entries from table MYDATA for which the DIST value is greater than the value of the parameter MINDIST in the table USER_OPTIONS.
• Select all data entries with indices among a given set.
• Select all data entries for which the ratio COST/DIST lies within a given range.
• Retrieve the data entry for a given index value.
• Apply some functions to the database entries.

The following Mosel model odbcselfunc.mos shows how to implement these tasks as SQL qeries.

model "ODBC selection and functions"
 uses "mmodbc"

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

 setparam("SQLdebug",true)
 SQLconnect('odbcsel.mdb')

! 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, COST2, COST3)
	
! Print the DIST value of item 'B'
 writeln(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"))

end-model