At 04:17 PM 12/23/98 -0600, Chad Fowler wrote:
Amos,
Thanks for your encouraging response. I think I should provide a little more detail. What I am specifically trying to do is look up a value from a DB table for every record returned from another DB table. For example, if I had a database of Overtime hours worked for company employees, for each record in the "OThours" table, I would look up "fullname" and "phonenumber" based on "employeenumber" out of the "Employees" table.
Sounds like what you really want is another SQL method that does a join on the OThours and Employee tables.
So, what I have done (and, I'm sure it's somehow flawed) is create a SQL Method
that returns multiple rows from the "OThours" table, and using the <!--#in tab, looped through each one, displaying the data. I'm trying to display the Employee Name and Phonenumber from the "Employees" table instead of the employeenumber that is stored with each record in the "OThours" table. The way I have approached this part is to create another SQL Method called getName (for example) which takes an argument called "eid", and inside my <!--#in loop, have something like the following:
<!--#in getAllOThours--> Hours work: <!--#var hoursworked--> Emp. Name: <!--#var expr="getName(eid=<!--#var employeenumber-->)"--> <!-- yes, i realize the above definitely doesn't work, but you see what i'm getting at :) --> Mood: <!--#var mood--> <!--#/in-->
OK, so if you really want to call another SQL Method for every row returned by a given SQL Method, you're on the right track. I see two problems with the above. 1 - The syntax is wrong as I mentioned in my last post. you should substitute: <!--#var expr="getName(eid=employeenumber)"--> for this: <!--#var expr="getName(eid=<!--#var employeenumber-->)"--> 2 - The getName SQL Method probably returns a collection of records, which have attibutes. My guess is that you aren't looking to insert the whole result set, but rather an attribute of the first returned record. So my guess is that you want something like this: <!--#in getOTHours--> hours: <!--#var hours--> <!--#in expr="getEmployee(eid=eid)"--> fname: <!--#var fname--> lname: <!--#var lname--> <!--#/in--> <!--#/in--> Which assumes that getOTHours returns records with 'hours' and 'eid' columns, and that getEmployee can be queried with 'eid' and returns records with 'fname' and 'lname' columns. Make sense? -Amos