My goal is to "cross-tabulate" a database result set so that we have dollar and hour totals for each employee and an overall hour total as shown in this [diagram](http://bit.ly/cy7mOM).
I've already written the [script to populate the database](http://bit.ly/9NOcfN). And the result set is shown below. All that is left is to write some Factor code to post-process the database rowset. However, there are two issues with me doing so: - There is no tutorial article on how to send SQL queries to the database and get back result sets - My use of assocs is not very strong. But I think I can figure that out. Once those two things are out of the way, we simply create hour and rate assocs keyed on employee id and tabulate results. And then output them , hopefully in much fewer lines than the Unix solution! === Database rowset === sqlite> SELECT EMPLOYEE_ID as eid, HOURS, FNAME, LNAME, TASK_ID, HOURS, RATE, HOURS*RATE as TOTAL FROM hour h INNER JOIN employee e USING (EMPLOYEE_ID) ; eid HOURS FNAME LNAME TASK_ID HOURS RATE TOTAL ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 3.0 Evan Schaffer 2 3.0 75.0 225.0 1 4.0 Evan Schaffer 2 4.0 75.0 300.0 2 4.0 Mike Wolf 1 4.0 85.0 340.0 2 5.0 Mike Wolf 2 5.0 85.0 425.0 3 5.0 Barbara Wright 2 5.0 75.0 375.0 3 6.0 Barbara Wright 1 6.0 75.0 450.0 === Database Schema === sqlite> .schema CREATE TABLE employee(EMPLOYEE_ID integer, FNAME text, LNAME text, RATE real, pr imary key(EMPLOYEE_ID)); CREATE TABLE hour(EMPLOYEE_ID integer, HOURS real, TASK_ID integer); CREATE TABLE task(TASK_ID integer, NAME text, primary key(TASK_ID)); === Background === I am interested in writing a blog post entitled "Factor as a Fourth Generation Language" as comparison with the article ["The UNIX Shell As a Fourth Generation Language"](http://www.rdb.com/lib/4gl.pdf). I have made use of [the db vocabulary](http://docs.factorcode.org/content/article-db-tuples.html) to write another high-level vocabulary which populates a SQLite database with the data from the article, such that one only need call populate-database once the sqlite.dll is installed to build the database mentioned in the article. ------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ Factor-talk mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/factor-talk
