Query of Query problem

[JakeFlynn]

Error Executing Database Query.

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

Now I am 100% sure that DATA query was run right before I ran the query that
depends on it. Is there anyway to force a query to be in memory. Also I am
hosting two CF sites off the same server and had to turn some cache options off
or the two site would butt heads. Any suggestions?
Jake

[paross1]

Won't work because "...it is run on a different page"

Phil

[Lupus 23]

Paross1 is correct. You can not refer to a query that is "on another page". To refer to the query you must refer to it in the same transaction.

[JakeFlynn]

I think i may have mis spoken for simplicity's sake I will try my best to
claify thank you to all those who are reading this and willing to help.
Here is the situation:

I have a page that when first loaded runs a query called DATA. I have it set
up so that it will only do this the first time the page is loaded using CFIF's
yada yada. Data is formatted on the page into a table. The table headings are
then links that depending on which is clicked the page is reloaded (skipping
the DATA query) but a query of DATA is done which is simply:

select * from DATA
order by something

so then the table is reformatted ordered by which heading was clicked.

Still with me? If so and you have a better way to do this i would be most
greatful to hear it.

[paross1]

You ought to post your code - it removes the guesswork.

[kyle969]

It probably has to to with the CFIF's yada yada. If the query is cached, let it
run, it won't hit the database.

Alternately, you could store the query in the application scope:
<cfif not isDefined("application.data")>
<cfquery name="application.data" ...>
select * from DATA
order by tableheading
</cfquery>
</cfif>

<cfquery dbtype="query" ...>
SELECT something
FROM application.data
</cfquery>

Don't bother using cachedWithin, it will be cached.

[FusionRed]

I have a master query and a query of the master, the master runs fine, but I
can't seem to get the correct count our of the query of the master. I've tried
translating the queries which work in access, but am not quite sure how to do
that. I'm trying to produce a recordcount based on the first 2 entries in the
column "ref" and they need to be equal to WholeString which is output in a
table.

Can someone please help me with this??? Many thanks in advance.

In access the queries are:
Master Query
SELECT Mid([ref],1,2) AS combstring, 1 AS Recordcount
FROM pcn
ORDER BY Mid9[ref],1,2)
Recordcount Query (query of queries)
SELECT Q1.combstring AS Expr1, SUM(Q1.Expr1001) AS SUMofExpr1001
FROM q1
GROUP BY Q1.combstring


Master Query
<cfquery datasource="CopyPCN" name="qGetPCNcount"
cachedwithin="#CreateTimeSpan(0,1,0,0)#">
SELECT MID([ref],1,2) AS combstring, 1 AS Recordcount
FROM pcn
ORDEr BY Mid([ref],1,2)
</cfquery>

Query of Query
<cfquery name="getPCNCount" dbtype="query">
SELECT combstring AS Recordcount, Sum(Recordcount) AS pcncount
FROM qGetPCNcount
GROUP BY combstring
</cfquery>

My query will produce a recordcount, but it is incorrect. Any help I can get
with this is greatly appreciated.

Red;->

[MikerRoo]

Change your QofQ to:
<cfquery name="getPCNCount" dbtype="query">
SELECT
combstring AS ANYTHING_BUT_Recordcount,
Sum(Recordcount) AS pcncount
FROM qGetPCNcount
GROUP BY combstring
</cfquery>

Then, How is recordcount incorrect?
Post sample data and the desired results.

Regards,
-- MikeR

[FusionRed]

Hi there and thanks for the response to my question.

Here's what my output looks like:

suffix 0 1 2 3 4
0 00 01 02 03 04
9 9 9 9 9
1 10 11 12 13 14
9 9 9 9 9
2 20 21 22 23 24
9 9 9 9 9

The number 9 is the count from the SQL QofQ of partnumbers with that prefix.
My test db has only 9 items all with 00 as the prefix, yet, my SQL returns 9
for all counts.

I changed the SQL to read:

<!--- Record count Query --->
<cfquery dbtype="query" name="getPCNCount">
SELECT Combstring AS Expr1, SUM(Recordcount) AS pcncount
FROM qGetPCNcount
GROUP BY Combstring
</cfquery>

I'm trying to get a count of all ref numbers beginning with the combstring.
IE my table is a list of numbers which make up the beginning portion of part
numers, like 00 01 02....0a 0b 0c....0z. I want to pull the first two numbers
out of the "ref" column and do a count on that and return that number to my
table.

I have poured over this one item for some time now and just don't understand
why in access when I create an expression based on the first query, I can't
return the same result to Cold Fusion??????

Thanks for any help you can offer.

Red;->

[MikerRoo]

There are some missing pieces here.

But, as I understand it, you do not need a QofQ in this case.

Try the attached query.

Regards,
-- MikeR



<cfquery datasource="CopyPCN" name="qGetPCNcountDirect"
cachedwithin="#CreateTimeSpan(0,1,0,0)#">
SELECT
Combstring,
SUM (iCombCount)
FROM
(
SELECT
Mid ([ref],1,2) AS Combstring,
1 AS iCombCount
FROM
pcn
)
AS PhantomTable
GROUP BY
Combstring
ORDER BY
Combstring
</cfquery>

[FusionRed]

Hi Mike and thanks for your help with this. I've tested it and get exactly the
same results--9's in the pcncount of every cell of my table. So, my next
question is, am I missing a where clause???? I'm including the page code below
.. The table is built by loops and all I'm doing is getting a count of those
part numbers in my db with the same first two strings as my table. I keep
thinking that I'm missing how the system is comparing the created "combstring"
to the combstring coming from the SQL query to come up with the correct count
for each item in the table.

<!--- Master Query --->
<cfquery datasource="CopyPCN" name="qGetPCNcountDirect"
cachedwithin="#CreateTimeSpan(0,1,0,0)#">
SELECT
Combstring,
SUM (iCombCount) AS pcncount
FROM
(
SELECT
Mid ([ref],1,2) AS Combstring,
1 AS iCombCount
FROM
pcn
)
AS PhantomTable
GROUP BY
Combstring
ORDER BY
Combstring
</cfquery>

<html>
<head>
<title>PID Chart</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<form action="PID2PCN.cfm" method="post">

<table border="2" cellspacing="0" bordercolor="#993300" bgcolor="#FFFFCC">
<cfset indexstring = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ">
<cfset prefix = indexstring>
<tr>
<th><font color="#993300" size="2" face="Verdana, Arial, Helvetica,
sans-serif">Suffix</font></th>
<cfloop index="x" from="1" to="36">
<!--- header loop --->
<th><font color="#993300" size="2" face="Verdana, Arial, Helvetica,
sans-serif"><cfoutput>#mid(indexstring,x,1)#</cfoutput></font></th>
</cfloop>
</tr>
<cfloop index="x" from="1" to="36">
<!--- row loop --->
<tr>
<th><strong><font color="#993300" size="2" face="Verdana, Arial,
Helvetica,
sans-serif"><cfoutput>#mid(indexstring,X,1)#</cfoutput></font></strong></th>
<cfloop index="Y" from="1" to="36">
<!--- column loop --->
<cfset CombString = Mid(indexstring, x, 1) & Mid(indexstring, y, 1)>

<td><strong><font color="#993300" size="2" face="Verdana, Arial,
Helvetica, sans-serif">

<cfoutput>
<a
href="PID2PCN.cfm?prefix=#mid(indexstring,x,1)##mi d(indexstring,y,1)#">#mid(inde
xstring,x,1)##mid(indexstring,y,1)#</a><br>
<cfif qGetPCNcountDirect.recordcount eq 1>
#qGetPCNcountDirect.pcncount#
<cfelse>
0
</cfif>
</cfoutput></font></strong></td>
</cfloop>
<!--- end column loop --->
</tr>
</cfloop>
<!--- end row loop --->
</table>


</form>

</body>
</html>

[FusionRed]

A question for the Universe, if my SQL statements are producing the correct
number, then why is it, the correct calculation is not being put into the
correct cell of the table????? My test db is small, 9, so I know the sum is
correct; however, 9 is put in all cells as the pcncount. What am I missing
seeing??????

Red;-<

[MikerRoo]

Well the missing piece was buried in there. Next time, when posting code,
please use the "Attach code" button.
It makes it so much easier to decipher.

Anyway, the same count was returned because only the 1st row of the query was
being accessed (1296 times!).

You do need a QofQ after all.

Change the code, inside the last <cfoutput> block to the code I've attached
below.

BTW, there is a way to do this kind of thing all in SQL -- but that exercise
is left for another day.

Regards,
-- MikeR


<a href="PID2PCN.cfm?prefix=#CombString#">#CombString #</a><br>

<cfquery dbtype="query" name="getPCNCountForThisComb">
SELECT pcncount
FROM qGetPCNcountDirect
WHERE Combstring = '#Combstring#'
</cfquery>

<cfif getPCNCountForThisComb.recordcount eq 1>
#getPCNCountForThisComb.pcncount#
<cfelse>
0
</cfif>

[FusionRed]

Mike:

Many, many thanks for your help and expertise. The code works like a charm.

Red;->>>>

[MikerRoo]

You're welcome.
Thanks for the feedback.
-- MikeR