Query of Query Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FusionRed - 28 Jun 2005 21:09 GMT

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;->

Reply to this Message

MikerRoo - 29 Jun 2005 14:04 GMT

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

Reply to this Message

FusionRed - 29 Jun 2005 15:20 GMT

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:


<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;->

Reply to this Message

MikerRoo - 29 Jun 2005 16:16 GMT

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>

Reply to this Message

FusionRed - 29 Jun 2005 18:25 GMT

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.


<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">

<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">

<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">

        <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)##mid(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>

</tr>
</cfloop>

</table>

</form>

</body>
</html>

Reply to this Message

FusionRed - 29 Jun 2005 21:14 GMT

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;-<

Reply to this Message

MikerRoo - 30 Jun 2005 00:48 GMT

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>

Reply to this Message

FusionRed - 30 Jun 2005 14:03 GMT

Mike:

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

Red;->>>>

Reply to this Message

MikerRoo - 30 Jun 2005 19:46 GMT

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

Reply to this Message

Webmaster Forum / ColdFusion / Getting Started / June 2005

Query of Query Problem