|
|
|
|
|
|
|
|
|
|
version 1.5
In the last section, you defined and activated a context. When the context was activated, 4D for Oracle generated a simple SQL query:
SELECT ename,job,mgr, … FROM emp
The result columns were then loaded into the corresponding 4th Dimension fields.
You can associate the result columns of any valid SQL query with 4th Dimension fields, variables, or arrays. For example, you could request a list of the average salary of each job in each department, sorted by salary. The associated SQL query is the following:
SELECT job,dept.loc,AVG(sal) FROM emp,dept WHERE dept.deptno=emp.deptno GROUP BY dept.loc,job ORDER BY 3
To Create a complex SQL query
In this section, you are going to define a context that executes this SQL query. You will then load the three result columns in the tLocation, tJob, and tSalaries arrays.
1. Open the ContextDemo method again and delete the ` character from the beginning of the first three lines.
ARRAY STRING (30;tLocation;0) ARRAY STRING (30;tJob;0) ARRAY REAL (tSalaries;0)
These three lines declare the three arrays that will receive the Oracle data. tLocation and tJob are 30 character string arrays while tSalaries is a real array. 4D for Oracle automatically sizes these arrays to the number of rows that the query returns.
2. Enter the User environment and execute the ContextDemo method again.
The Edit a Context dialog box reappears.
Binding the JOB Column to the tJob Array
To create this bind:
1. Select the JOB column from the list on the left.
2. Click the Variable button.
The Global Variable or 4D Array dialog box appears. In this dialog box, you will enter the name of the 4th Dimension array that will receive the data in the JOB column.
3. Type "tJob" and click OK.
4. Click Bind.
4D for Oracle defines a bind between the JOB column and the tJob array.
Binding the LOC Column to the tLocation Array.
To create this bind:
1. Select the SCOTT.DEPT table in the Oracle Table pop-up menu and select the LOC column.
2. Click the Variable… button, type "tLocation", and click OK.
3. Click Bind.
4D for Oracle defines a bind between the LOC column and the tLocation array.
Binding the Expression 1 Expression to the tSalaries Array
To create this bind:
1. Click the Expression button and type "AVG(SAL)" in the Edit a SQL Expression dialog box. Click OK.
You can enter any valid SQL expression in the dialog box.
By default, the expression is named Expression 1.
2. Choose Expressions from the Oracle Table pop-up menu.
The name of your expression appears in the list below the pop-up menu.
3. Select Expression 1 from the list on the left.
4. Click the Variable button, type "tSalaries", and click OK.
5. Request a column sort by selecting the Sorted check box.
6. Click Bind.
4D for Oracle defines a bind between Expression 1 and the tSalaries array.
You will now specify WHERE and GROUP BY clauses for the context.
Defining the WHERE and GROUP BY Clauses for the Context
To define the clauses:
1. Click the Select Clauses button.
The Edit an SQL Clause dialog box appears.
2. Choose WHERE from the Clause pop-up menu and type "DEPT.DEPTNO = EMP.DEPTNO."
3. Select GROUP BY from the Clause pop-up menu and type "dept.loc,job".
4. Click OK to accept the clauses you defined.
The Edit a Context dialog box appears:
5. Click OK to validate the context and resume execution of the ContextDemo method.
4D for Oracle executes the query and loads the results into the tJob, tLocation, and tSalaries arrays.
6. To see the results, execute the Results method.
The Result of the Query dialog box appears, displaying the three arrays.
|
|
|
|
|
|
|
|
|
|