query of a query trouble

[throm]

Every time I try and use a col other then the default I get this error spit
back at me

?Query Of Queries runtime error.
Table named "getIDs" was not found in Memory. It is misspelled, or the table
is not defined.?

For some reason my queries of queries don?t recognize the getIDs query. I
tried adding a CachedWithin="#CreateTimeSpan(0,0,1,0)#" hoping to cache the
query, but it still did not find it. Odd think is I have done something like
this before and the format was pretty much the same only real difference is
this one has more columns and more rows. No idea what is causing the problem
some kind of cacheing issue maybe? Oh and I have my coldfusion admin set to
cache queries.


<!---gets the IDs of the columns needed based on which column of links was
selected--->
<cfswitch expression="col">

<cfcase value="facility">
<cfquery name="getIDs" datasource="#request.site.datasource#">
SELECT p.profileID, p.Activity, p.task, p.topicsection
FROM profiletree p
WHERE p.profileID = #ID#
</cfquery>
</cfcase>

<cfcase value="activity">
<cfquery name="getIDs" datasource="#request.site.datasource#">
SELECT p.profileID, p.Activity, p.task, p.topicsection
FROM profiletree p
WHERE p.activity = #ID#
</cfquery>
</cfcase>

<cfcase value="task">
<cfquery name="getIDs" datasource="#request.site.datasource#">
SELECT p.profileID, p.Activity, p.task, p.topicsection
FROM profiletree p
WHERE p.task = #ID#
</cfquery>
</cfcase>

<cfcase value="section">
<cfquery name="getIDs" datasource="#request.site.datasource#">
SELECT p.profileID, p.Activity, p.task, p.topicsection
FROM profiletree p
WHERE p.topicsection = #ID#
</cfquery>
</cfcase>

<cfdefaultcase>
<!---default for the page these queries get all of the FAT stuff--->
<cfquery name="FTN" datasource="#request.site.datasource#">
SELECT DISTINCT facilitytypeID, facilitytypename
FROM facilitytype
order by facilitytypename
</cfquery>

<cfquery name="AN" datasource="#request.site.datasource#">
SELECT DISTINCT *
FROM activity
order by activityname
</cfquery>

<cfquery name="TN" datasource="#request.site.datasource#">
SELECT DISTINCT *
FROM task
order by taskname
</cfquery>

<cfquery name="CISN" datasource="#request.site.datasource#">
SELECT DISTINCT sectionheaderID, chapterID, sectionnumber
FROM sectionheader
where jurisdictiontypeid = 1
order by chapterID
</cfquery>
</cfdefaultcase>
</cfswitch>

<!--- if the default queries were not used these queries use the tags from
the get IDs query to find the right rows--->
<cfif #col# is "facility" or #col# is "activity" or #col# is "tesk" or #col#
is "section">
<!---a getfacility query is used because a query can not query a database
and a query at the same time we only need facilitytypeID and facilitytypename
since there are other columns it is best to be specific--->
<cfquery name="getfacility" datasource="#request.site.datasource#">
SELECT DISTINCT facilitytypeID, facilitytypename
FROM facilitytype
</cfquery>

<!---this queries the getIDs query and getFacility query to find the needed
facility type name and ID--->
<cfquery name="FTN" dbtype="query">
SELECT getfacility.*
FROM getIDs, getfacility
WHERE getIDs.profileID = getfacility.facilityID
order by facilitytypename
</cfquery>

<!---a getactivity query is used because a query can not query a database
and a query at the same time we need activityID and activity name but since
they are the only columns in the table we can just select all--->
<cfquery name="getactivity" datasource="#request.site.datasource#">
SELECT DISTINCT *
FROM activity
</cfquery>

<!---this queries the getIDs query and getactivity query to find the needed
activityname and ID--->
<cfquery name="AN" dbtype="query">
SELECT a.*
FROM getIDs g, getactivity a
WHERE g.activity = a.activityID
order by activityname
</cfquery>

<!---a gettask query is used because a query can not query a database and a
query at the same time we need task ID and task name but since they are the
only columns in the table we can just select all--->
<cfquery name="gettask" datasource="#request.site.datasource#">
SELECT DISTINCT *
FROM task
</cfquery>

<!---this queries the getIDs query and gettask query to find the needed task
name and ID--->
<cfquery name="TN" dbtype="query">
SELECT t.*
FROM getIDs g, gettask t
WHERE g.task = t.taskID
order by taskname
</cfquery>

<!---a getsection query is used because a query can not query a database and
a query at the same time we only need sectionheaderID, chapterID, and
sectionnumber since there are other columns in the table it is best to be
specific--->
<cfquery name="getsection" datasource="#request.site.datasource#">
SELECT DISTINCT sectionheaderID, chapterID, sectionnumber
FROM sectionheader
WHERE jurisdictiontypeid = 1
</cfquery>

<!---this queries the getIDs query and getsection query to find the needed
chapterID, sectionnumber, and sectionID--->
<cfquery name="CISN" dbtype="query">
SELECT s.*
FROM getIDs g, getsection s
WHERE g.topicsection = s.sectionheaderID
order by chapterID
</cfquery>
</cfif>

[jdeline]

Is it of concern that a getIds query does not run if your CFSWITCH takes you to <CFDEFALUTCASE>?

[throm]

Is it of concern that a getIds query does not run if your CFSWITCH takes you
to <CFDEFALUTCASE>?

I just did some testing on that and it seems even though the variable col is
facility the switch statement doesn't Recognize it. The odd thing is that the
cfif does and they are spelled exactly the same; another oddity is that before
<cfparam name="col" default="all"> if i try and print out #col# I get en error
if it not existing but after if i print #col# out it has a value of facility.

[jdeline]

Your problem is in the CFSWITCH tag. Try
<CFSWITCH EXPRESSION="#col#">

[throm]

Ok I got the cfswitch tag to work properly again i forgot to use #col# as the
expression. It is hard to believe i let something so small get me sorry to have
taken up your time for such a noob mistake.