SQL  procedures

Top  Previous  Next

Here the SQL queries can be set. This part requires SQL language knowledge.

 

 

clip0059

 

1. The used databases (access the databases structures)

       - here we see only one - Northwind, but a lot of databases can be used if we have proper connections between them.

       - data and structure can be seen

       - this control can be hidden using the vertical shape on right of the control.

 

2. SQL Queries

       - each query should have a name.

       - there can be as many as needed

       - the query can be written by hand (right text edit or text editor clip0060) or a visual query editor can be used also clip0061.

 

clip0062

 

Each query can be tested by pressing clip0064 button.

It is a good idea to test each query when created.

 

3. Here we set the code that runs last on server.

RUNSQL(<sqlname>)    - runs the SQL query named <sqlname>

RETURNSQL(<sqlnqme>) - return SQL result.

 

Between those commands, SQL's that collect data from multiple sources can be inserted.

Available new functions:

       -    _month(<date field>) - returns month like "M2011.01"  from "2011-01-01"

       -    _quarter(<date field>) - returns quarter from date like "Q2011-01'

 

Sample:

 

RunSQL('first')

 

select _month(first.orderdate) as month,_quarter(first.orderdate) as quarter,first.* from first into cursor second

 

ReturnSQL('second')

All queries can be tested at once by pressing clip0064 button here.