I am using Oracle and I have a table for expenditures that stores a person's ID
when the expenditures is approved. The person's ID is from a People table.
When I query, I want to make a new variable that references the other table to
put in their full name. I have two problems doing this.
1 - I'm not so good at sql and the join only works if there's an id in the
approved_by field. This is going to be empty unless the ticket has been
approved, but I still want it to work whether it's approved or not. Is that an
outter join?
2. Currently, I do p.fname AS approved_by_fname,p.fname AS approved_by_lname.
I'd like it to be one variable though, but I'm not sure how to format p.fname +
p.lname AS approved_by_name.
Here's my current query:
<CFQUERY NAME="getSelect" DATASOURCE="dh" debug="yes" result="the_result">
SELECT
e.expenditure_id,e.date_added,e.date_modified,e.approval,e.purpose,e.estimate,e.department,e.department_other,
e.vendor_name,e.vendor_address,e.vendor_phone,e.vendor_contact,e.fei,e.state_employee,e.foundation,e.frs,
e.principal_investigator,e.requestor_fname,e.requestor_lname,e.requestor_email,e.approved_by,e.approval_date,p.fname
AS approved_by_fname,p.fname AS approved_by_lname
FROM expenditures e, people p
WHERE e.approved_by = p.id
AND date_added >= #url.start_date# AND date_added <=
#url.end_date#
<cfswitch expression="#URL.approval#">
<cfcase value="pending">
AND approval = 'Pending'
</cfcase>
<cfcase value="approved">
AND approval =
'Approved'
</cfcase>
<cfcase value="denied">
AND approval = 'Denied'
</cfcase>
<cfcase value="all">
<!--- no WHERE needed -
it's all of the tickets --->
</cfcase>
</cfswitch>
ORDER BY #the_sort#
</CFQUERY>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304979
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
