Hi,
I have the following query and was hoping that someone could help me out.
I am trying to retrieve a field named bug_type in another table (bug_type)
IF the bugs.bug_type_id != 0 (since i know that bug_type.bug_type_id does
not exist). Conversely if bugs.bug_type_id = 0 then simply add that
statement to the query. .
<cfquery datasource="#variables.DSName#" name="bug_details">
SELECT bugs.bug_id, bugs.bug_type_id, bugs.bug_site_id,
bugs.bug_section_id,
etc etc
FROM bugs, bug_site, etc etc
WHERE bug_site.bug_site_id = bugs.bug_site_id
AND bug_status.bug_status_id = bugs.status_id
AND priority.priority_id = bugs.priority_id
AND (IF bug_type_id !=0
bug_type IN
(SELECT bug_type, bug_type_id
FROM bug_type
WHERE bug_type.bug_type_id = bugs.bug_type_id)
ELSE bug_type_id =0)
</cfquery>
Furthermore, i have noticed that if i do manage to get some hybrid of the
statement to work, i cannot reference the inner statement variable
#bug_type# when i try to output it to the table as shown below.
<cfoutput query="bug_details">
....
<td>#bug_details.currentrow#</td>
<cfif #bug_type_id# neq "0">
<td width=100>#bug_type# </td>
<cfelseif #bug_type_id# eq "0">
<td width=100>Unknown</td>
</cfif>
<td width=120>#bug_site# </td>
etc etc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
