SELECT Combstring AS Expr1, SUM(Recordcount) AS pcncount FROM qGetPCNcount GROUP BY Combstring 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;-> [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => FusionRed [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 9 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> 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 PID Chart
Suffix #mid(indexstring,x,1)#
#mid(indexstring,X,1)# #mid(inde xstring,x,1)##mid(indexstring,y,1)#
#qGetPCNcountDirect.pcncount# 0
[allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => FusionRed [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 11 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Query of Query problem - Macromedia ColdFusion

Query of Query problem - Macromedia ColdFusion

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

  1. #1

    Default Query of Query problem

    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


    JakeFlynn Guest

  2. #2

    Default Re: Query of Query problem

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

    Phil
    paross1 Guest

  3. #3

    Default Re: Query of Query problem

    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.

    Lupus 23 Guest

  4. #4

    Default Re: Query of Query problem

    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.

    JakeFlynn Guest

  5. #5

    Default Re: Query of Query problem

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

  6. #6

    Default Re: Query of Query problem

    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.

    kyle969 Guest

  7. #7

    Default Query of Query Problem

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



    FusionRed Guest

  8. #8

    Default Re: Query of Query Problem

    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


    MikerRoo Guest

  9. #9

    Default Re: Query of Query Problem

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

    FusionRed Guest

  10. #10

    Default Re: Query of Query Problem

    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>

    MikerRoo Guest

  11. #11

    Default Re: Query of Query Problem

    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; cht=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 Guest

  12. #12

    Default Re: Query of Query Problem

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

    FusionRed Guest

  13. #13

    Default Re: Query of Query Problem

    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>

    MikerRoo Guest

  14. #14

    Default Re: Query of Query Problem

    Mike:

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

    Red;->>>>
    FusionRed Guest

  15. #15

    Default Re: Query of Query Problem

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

  16. #16

    Default Query of Query problem

    Here is my query:

    <CFQUERY name="numbymonth" dbtype="query">
    select MONTH(opened_date) as the_month, YEAR(opened_date) as the_year,
    COUNT(*) as thecount
    from Session.cq_query
    group by MONTH(opened_date), YEAR(opened_date)
    order by the_year, the_month
    </CFQUERY>

    Here is the error:
    Query Of Queries syntax error.
    Encountered "MONTH" at line 0, column 0. Incorrect Select List, Incorrect
    select column,

    anyone have any ideas on how i can get this to work?

    JakeFlynn Guest

  17. #17

    Default Re: Query of Query problem

    I lean towards using the group by columns in the order by. Why not move
    those functions (Month and Year) into the original query? ... that way this
    QoQ should not have as many issues. hth

    -brian

    "JakeFlynn" <com> wrote in message
    news:dcqgil$54o$macromedia.com... 


    Brian Guest

  18. #18

    Default Re: Query of Query problem

    Yea, I just moved it into the original query and that solved my problem
    Month(date) was looking for the sql server function I do believe but since it
    was never hitting the database it did not know what to do with the function.

    JakeFlynn Guest

  19. #19

    Default Re: Query of Query problem

    I have similar problem to this. I have a page (the caller page) that calls a
    query page via cfmodule two times (with different attributes in each call):

    <cfmodule template="query.cfm" Q_NAME="Query1" style="curly">
    <cfmodule template="query.cfm" Q_NAME="Query2" style="straight">


    The query page then returns the results (everytime it is called) like this:
    <cfset "Caller.Q_#Attributes.Q_Name#" = Q_Query>

    I am then trying to combine those two results into one by doing as follows:
    <cfquery name="Tones" dbtype="query">
    SELECT DISTINCT * FROM Q_Query1, Q_Query2;
    </cfquery>

    But it gives me an error:
    Query Of Queries runtime error. Table named "Q_Query1" was not found in
    Memory. It is misspelled, or the table is not defined.

    If I access the query result separately, they seem to work fine. But not when
    I try to do query of queries on both of them.

    Is the problem caused by the 'different page' issue? Is there a way out of
    this?

    scrypton Guest

Similar Threads

  1. Query on Query and CF casting problem
    By obxlefty in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: May 18th, 12:44 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •