Getting started with SQL Where Clause on secondary file(s)
|
Getting started with SQL Where Clause on secondary file(s) |
|
Secondary File(s)
When implementing a "Where Clause" statement for a secondary file read, it is imperative to comprehend the distinctions in processing methodology employed by the kernel in comparison to primary file operations. To facilitate a comprehensive understanding, the subsequent section provides several illustrative examples.
Customer Entries are:
|
Customer |
Name |
State |
Inactive |
|
Apple |
Apple Corp |
TX |
N |
|
Microsoft |
Microsoft Corp |
CA |
N |
|
P&G |
Proter & Gamble |
TX |
Y |
|
Walmart |
Walmart Coporation |
UT |
Y |
Order entries are:
|
OrderNo |
OrderCustomer |
OrderTotal |
|
0001 |
Walmar |
1000.00 |
|
0002 |
Walmart |
2000.00 |
|
0003 |
P&G |
3000.00 |
|
0004 |
Apple |
4000.00 |
|
0005 |
Microsoft |
5000.00 |
|
0006 |
Microsoft |
6000.00 |
|
0007 |
Microsoft |
7000.00 |
Example PROIV Requirment - Retrieve Active Orders
Develope a many time update cycle that will systematically retrieve all "Orders". For each active "Customer" our objective is to calculate the cumulative sum of the "OrderTotal" column and generate a comprehensive output of the results.
File Accessor are as follows:
|
File Acessor |
Where Clause |
Before Read |
On Error |
After Read |
|
Order |
|
|
|
|
|
Customer |
Where Customer = OrderCustomer And Inactive <> 'Y' |
|
DSEL |
#ActiveTot += OrderTotal |
Retrieve Active Orders - Results
The last four active customer orders are extracted with #ActiveTot variable being calculated as "22000.00".
Example PROIV Requirment - Retrieve Active Orders over 5000.00, where the customer is located in the state of California.
Develope a many time update cycle that will systematically retrieve "Orders" over 5000.00. For each active "Customer" that are located in the state of California our objective is to calculate the cumulative sum of the "OrderTotal" column and generate a comprehensive output of the results.
File Accessor are as follows:
|
File Acessor |
Where Clause |
Before Read |
On Error |
After Read |
|
Order |
|
|
|
If OrderTotal > 5000 else dsel endif |
|
Customer |
Where Customer = OrderCustomer And Inactive <> 'Y' and State = 'CA' |
|
DSEL |
#ActiveTot += OrderTotal |
Retrieve Active Orders - Results
The last two active customer orders for "Microsoft" are extracted with #ActiveTot variable being calculated as "13000.00".
Related Topics
Document Function File Accessor
Report Function File Accessor
Screen Function File Accessor
Update Function File Accessor
SQL Where Clause
Virtual File Accessor
Virtual File Accessor Advanced Operators
Virtual File Accessor Considerations
Virtual File Accessor Conventions and Limitations
Topic ID: 520202