Problem with cfoutput

[Biglop]

I have a query of queries, which works fine and when I dump the results all looks good. When I try a cfoutput, however, I only get results for one row, repeating. It's probably something basic, but I'm stumped...Ideas anyone??

Query:

<cfquery name="pb_bp_time_diff" dbtype="query">
SELECT pb_bp_mod_time.mod_ID, pb_bp_enroll_time.enroll_ID, pb_bp_mod_time.mod_date,pb_bp_enroll_time.enroll_date
FROM pb_bp_enroll_time, pb_bp_mod_time
WHERE pb_bp_mod_time.mod_date BETWEEN <cfqueryparam cfsqltype="cf_sql_timestamp" value="#form.startdate#"> AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#form.enddate#"> AND pb_bp_mod_time.ssn = pb_bp_enroll_time.ssn
ORDER BY pb_bp_mod_time.mod_date

Output Table:

<table width="80%" border="0">
<tr class="style6">
<th scope="col"><span class="style9">Mod ID</span></th>
<th scope="col"><span class="style9">Enroll ID</span></th>
<th scope="col"><span class="style9">Enroll Date</span></th>
<th scope="col"><span class="style9">Mod Date</span></th>
<th scope="col"><span class="style9">Months to Enroll</span></th>
</tr>
<cfoutput query="pb_bp_time_diff">
<tr>
<td><span class="style6">#pb_bp_mod_time.mod_ID#</span></td>
<td><span class="style6">#pb_bp_enroll_time.enroll_ID#</span></td>
<td><span class="style6">#pb_bp_enroll_time.enroll_date#</span></td>
<td><span class="style6">#pb_bp_mod_time.mod_date#</span></td>
<td><span class="style6">#DateDiff("m", pb_bp_enroll_time.enroll_date, pb_bp_mod_time.mod_date)#</span></td>
</tr>
</cfoutput>
</table>

CFdump sample:

time to add - query
ENROLL_DATE ENROLL_ID MOD_DATE MOD_ID
1 2002-12-03 00:00:00.0 4243 2003-01-01 00:00:00.0 4333
2 2000-08-17 00:00:00.0 733 2003-01-03 00:00:00.0 4349
3 2002-08-27 00:00:00.0 3858 2003-01-09 00:00:00.0 4377
4 2000-06-16 00:00:00.0 447 2003-01-10 00:00:00.0 4379


Cfoutput sample:

Mod ID Enroll ID Enroll Date Mod Date Months to Enroll
7873 292 2000-05-08 00:00:00.0 2004-12-19 19:35:13.707 55

 

[r937]

your CFOUTPUT says pb_bp_time_diff

but you are printing rows from pb_bp_mod_time and pb_bp_enroll_time

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[TruthInSatire]

that wouldn't keep it from repeating.

your cfdump example doesn't even have mod id 7873 in it. by saying query = "pb_bp_time_diff" you're trying to loop through that query. my guess is it only has 1 row and you're doing a cfdump of a different query.

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)

 

[Biglop]

It works now... the problem was that even though the cfoutput was referencing the correct query, "<cfoutput query="pb_bp_time_diff">", the actual field names were in the format "table.fieldname". In my case that was, for instance, "pb_bp_mod_time.mod_ID". It seems that even though the correct query is declared in cfoutput tag, the variables can't use the format I tried. I fixed by removing the "table." from the variable names.

<cfoutput query="pb_bp_time_diff">
<tr>
<td><span class="style6">#mod_ID#</span></td>
<td><span class="style6">#enroll_ID#</span></td>
<td><span class="style6">#mod_date#</span></td>
<td><span class="style6">#enroll_date#</span></td>
<td><span class="style6">#DateDiff("m", enroll_date, mod_date)#</span></td>
</tr>
</cfoutput>


In the case of a query of queries, and the two queries have fields of the same name (like "ID"), how do you referce in output from the QoQ? It seems you would need to use a SELECT ID AS Unique Name in order to reference both in the final cfouput... Does that make sense??

 

[r937]

yes, it does make sense

when you write a query of queries, if it's doing a join, you have to use queryname.column to distinguish columns from the two different query result sets, as well as assign a column alias to them

if one of your database queries is doing a join, and two tables have the same column name, then you qualify those by table name, as well as assign a column alias to them


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)