Multiple Embedded SQL Statements

While complex embedded SQL statements will let you do a lot of work in one statement, it is sometimes necessary to nest SQL statements to get the job done.

Using cursor handling lets you easily nest multiple embedded SQL statements.

Consider the scenario where we want to iterate through all orders with a specific status, and for each order we want to calculate the total of that order.

Of course, in this case it would be pretty easy to write one SQL statement that returns everything, but for the example we will use two nesting calls: 

 // Example
 Use Mertech.inc
 Integer iCursor1 iCursor2
 Integer iCustomer
 String sName
 Number nOrder nTotal

login 'Server' 'User' 'Passwd' 'Driver'

set_database_name to 'DataBase'
sql_use_database 'DataBase'

Sql_Open_Cursor_Stmt to iCursor1
Sql_Set_Stmt to "select customer_number,name from customer order where status = 'Y' by customer_number"
Sql_Prepare_Stmt
Sql_Execute_Stmt

Repeat
// Back to the main cursor
Sql_Set_Current_Cursor_Stmt to iCursor1
Sql_Fetch_Next_Row into iCustomer sName
If (Found) Begin
// Open a new cursor to the new stmt
Sql_Open_Cursor_Stmt to iCursor2

Sql_Set_Stmt To ("Select sum(order_total) from Orderhea where customer_number = " + trim(iCustomer))
Sql_Prepare_Stmt
Sql_Execute_Stmt
Sql_Fetch_Next_Row into nOrder
If (Not(Found)) Move 0 to nTotal

Sql_Close_Cursor_Stmt iCursor2

// nTotal has the Order total for this current customer
// Your code here
Indicate Found True
End
Until (Not(Found))
// End Example

The keys in the example are the calls to Sql_Open_Cursor_Stmt and Sql_Set_Current_Cursor_Stmt which allows you to switch between two open recordsets.