Query of Queries error

[vsimmons]

I'm apparently doing something wrong, and I need help to figure out how to do what I'm doing the right way. I have the following two queries:

<cfquery name=&quot;qGetDataSeq&quot; datasource=&quot;#VARIABLES.dsn#&quot;>
	SELECT data_seq
	  FROM subject s, data_correction d
	 WHERE s.subj_seq = d.subj_seq
	   AND #PreserveSingleQuotes(v_where_clause)#
</cfquery>

<cfquery name=&quot;qDataCorrections&quot; dbtype=&quot;query&quot;>
  SELECT prot_num, cntr_num, subj_id, init, visit_num   
    FROM subject s, data_correction d
   WHERE s.subj_seq = d.subj_seq 
     AND data_seq = #qGetDataSeq.data_seq# 
</cfquery>

I am getting the correct values from the 1st query. What I need to do with the 2nd query is to pass in each value returned from the 1st query. The error I'm getting is:
Error Executing Database Query.

Query Of Queries syntax error.
Encountered "s" at line 6, column 28.

The Error Occurred in C:\CFusionMX\

http://wwwroot\DataCorrections.cfm:

line 42

40 : AND data_seq = #qGetDataSeq.data_seq#
41 : </cfquery>
42 : <p class=&quot;reportTitle&quot;>CARE - Data Correction</p>
43 :
44 : <cfoutput query=&quot;qDataCorrections&quot;>

However, it also gives me the line of SQL that looks like this:

SELECT prot_num, cntr_num, subj_id, init, visit_num
FROM subject s, data_correction d WHERE s.subj_seq = d.subj_seq AND data_seq = 125

This SQL statement looks fine by my accounts and I can run it from the command line.

What am I missing?????
Thanks,
Vanessa

 

[rhyswilliams]

Why are you using query on query? You could just use this code for your second query:


<cfquery name=&quot;qDataCorrections&quot; datasource=&quot;#VARIABLES.dsn#&quot;>
SELECT prot_num, cntr_num, subj_id, init, visit_num
FROM subject s, data_correction d
WHERE s.subj_seq = d.subj_seq
AND data_seq = #qGetDataSeq.data_seq#
</cfquery>

but why don't you just use one query to return all the fields?