Professional Web Applications Themes

Query of Query Missing Some Data - Coldfusion Database Access

Hey Folks, I'm not sure that my message title or topic summary make much sense, but I'll attempt to expound upon them here and with any luck someone out there will have already solved this problem and will be able to clue me in to the solution. Then again it may just be yet another limitation of the query of query functionality. :) I've got a CFC method where I am building what I refer to as a "Memory Table". That is, I'm building a query result set manually using QueryNew(), QueryAddRow(), and QuerySetCell(). I'll briefly explain why I'm doing ...

  1. #1

    Default Query of Query Missing Some Data

    Hey Folks, I'm not sure that my message title or topic summary make much sense,
    but I'll attempt to expound upon them here and with any luck someone out there
    will have already solved this problem and will be able to clue me in to the
    solution. Then again it may just be yet another limitation of the query of
    query functionality. :)

    I've got a CFC method where I am building what I refer to as a "Memory Table".
    That is, I'm building a query result set manually using QueryNew(),
    QueryAddRow(), and QuerySetCell(). I'll briefly explain why I'm doing this. I
    have a screen that shows rows of cost centers, and has dates as columns. Each
    cell in the grid, then displays the initials of who is working that day. So my
    result set needs to include the days that an employee isn't working as well as
    the days that they are working. Well, my query of the database will only
    return the days they are working so I build this memory table in order to
    insert the days where folks are not working.

    Okay, with me so far? Good. There may be a better way to do this and I think
    that I might try to populate an array in my cfc instead of a query result set,
    but for now I'd like to focus on building a query result set.

    So the specific problem is this. When my code is finished looping and creating
    my hand-built result set, I then run the following code:

    <CFDump var="#ReportTable#" label="Report Table Pre Sort">
    <CFQuery name="ReportTable" dbtype="query">
    SELECT * FROM ReportTable
    ORDER BY SortLastName,SortFirstName,emp_id,scheduled_date
    </CFQuery>
    <CFDump var="#ReportTable#" label="Sorted Result">

    This query of query is run specifically to sort the "memory table" by certain
    rows. I've got other code that does this flawlessly. The dumps before and after
    show me that the data in the record set has changed. Well, when I run the cfc
    method responsible for creating this result set using a period of six weeks
    (the max time span set by my client), the sorted result doesn't contain the
    same data that the pre-sorted result set contains. Specifically, the result set
    contains fields like CostCenterID, CostCenterName, ShiftID,StartTime,EndTime,
    etc. Fields such as LastName, FirstName and ShiftDate remain unaffected by this
    problem, but the five fields I mentioned just now, are all missing data where
    prior to the sort they contained valid data! So my report based on the result
    set obtained from this method shows up empty.

    But it gets better: If I run this same function for a period of one week the
    pre-sort result set and the sorted result set contain the same data (except for
    being correctly sorted, of course). I ran this same function for five weeks
    (broken), four weeks (broken), three weeks (broken), two weeks (broken), one
    week and six days (broken), one week and five days (broken), and on and on
    until the thing worked again which was at exactly one week. Initially, when the
    bug was reported to me, the user couldn't run a six week report but could run
    two three week reports covering the original six week time span. Now my
    testing indicates that even three weeks doesn't work.

    The plot thickens again, however. I have multiple classifications of
    employees, it doesn't matter what those classifications are right now, but each
    classification has an ID in my system. Oddly enough, if I run the same tests
    as I mentioned above (6 weeks, 5 weeks, etc.), on an employee classificaton id
    of 3 everything works brilliantly. If I run it on an employee classification
    id of 1 then I get the above results.

    Does anyone have any thoughts as to what I might be doing wrong? It seems
    random enough that I just feel like blaming the query of query and finding
    another way to do it. But, I thought I'd put a feeler out on the forum to see
    if anyone else has experienced this problem. Perhaps I've found yet another
    problem with QoQs?

    g1zm0guy Guest

  2. #2

    Default Query of Query Missing Some Data

    Hey Folks, I'm not sure that my message title or topic summary make much sense,
    but I'll attempt to expound upon them here and with any luck someone out there
    will have already solved this problem and will be able to clue me in to the
    solution. Then again it may just be yet another limitation of the query of
    query functionality. :)

    I've got a CFC method where I am building what I refer to as a "Memory Table".
    That is, I'm building a query result set manually using QueryNew(),
    QueryAddRow(), and QuerySetCell(). I'll briefly explain why I'm doing this. I
    have a screen that shows rows of cost centers, and has dates as columns. Each
    cell in the grid, then displays the initials of who is working that day. So my
    result set needs to include the days that an employee isn't working as well as
    the days that they are working. Well, my query of the database will only
    return the days they are working so I build this memory table in order to
    insert the days where folks are not working.

    Okay, with me so far? Good. There may be a better way to do this and I think
    that I might try to populate an array in my cfc instead of a query result set,
    but for now I'd like to focus on building a query result set.

    So the specific problem is this. When my code is finished looping and creating
    my hand-built result set, I then run the following code:

    <CFDump var="#ReportTable#" label="Report Table Pre Sort">
    <CFQuery name="ReportTable" dbtype="query">
    SELECT * FROM ReportTable
    ORDER BY SortLastName,SortFirstName,emp_id,scheduled_date
    </CFQuery>
    <CFDump var="#ReportTable#" label="Sorted Result">

    This query of query is run specifically to sort the "memory table" by certain
    rows. I've got other code that does this flawlessly. The dumps before and after
    show me that the data in the record set has changed. Well, when I run the cfc
    method responsible for creating this result set using a period of six weeks
    (the max time span set by my client), the sorted result doesn't contain the
    same data that the pre-sorted result set contains. Specifically, the result set
    contains fields like CostCenterID, CostCenterName, ShiftID,StartTime,EndTime,
    etc. Fields such as LastName, FirstName and ShiftDate remain unaffected by this
    problem, but the five fields I mentioned just now, are all missing data where
    prior to the sort they contained valid data! So my report based on the result
    set obtained from this method shows up empty.

    But it gets better: If I run this same function for a period of one week the
    pre-sort result set and the sorted result set contain the same data (except for
    being correctly sorted, of course). I ran this same function for five weeks
    (broken), four weeks (broken), three weeks (broken), two weeks (broken), one
    week and six days (broken), one week and five days (broken), and on and on
    until the thing worked again which was at exactly one week. Initially, when the
    bug was reported to me, the user couldn't run a six week report but could run
    two three week reports covering the original six week time span. Now my
    testing indicates that even three weeks doesn't work.

    The plot thickens again, however. I have multiple classifications of
    employees, it doesn't matter what those classifications are right now, but each
    classification has an ID in my system. Oddly enough, if I run the same tests
    as I mentioned above (6 weeks, 5 weeks, etc.), on an employee classificaton id
    of 3 everything works brilliantly. If I run it on an employee classification
    id of 1 then I get the above results.

    Does anyone have any thoughts as to what I might be doing wrong? It seems
    random enough that I just feel like blaming the query of query and finding
    another way to do it. But, I thought I'd put a feeler out on the forum to see
    if anyone else has experienced this problem. Perhaps I've found yet another
    problem with QoQs?

    g1zm0guy Guest

  3. #3

    Default Re: Query of Query Missing Some Data

    I have no idea really what might be going on, other than I would probably
    specify a different query name in between the CFdumps above. which from your
    other examples is not a problem because it works sometimes and not others.

    Is it possible that the dbquery is getting updated by another user or script
    somehow? Is it a systemwide application variable?

    I hope you are able to get a solution to this. I am interested as you are I
    am sure as to the solution.

    -CFRandy

    CFRandy Guest

  4. #4

    Default Re: Query of Query Missing Some Data

    I think you should change your entire approach and write a query that includes
    the days someone isn't working. You might have to change your database a bit,
    but things like this will work a lot better in the long run.

    Dan Bracuk Guest

  5. #5

    Default Re: Query of Query Missing Some Data

    I think that Query of Queries should just work better. I shouldn't have to
    expand the size of my database just to keep track of when someone isn't
    working, that just doesn't make sense, imho.

    Is there a way for me to take care of this in my query without having to store
    negative data in my table? I'm not a SQL master yet, so maybe there's a better
    way to accomplish this using SQL rather than what I'm doing now.

    Any SQL guru's out there wanna help? :)

    g1zm0guy Guest

  6. #6

    Default Re: Query of Query Missing Some Data

    Since you said you are not an sql expert, I have heard good things about this
    book.


    [url]http://www.amazon.com/gp/product/0672316641/104-2438928-3708712?v=glance&n=28315[/url]
    5

    Does AS400/DB2 support left joins. I'm not sure if a left join and full outer
    join are the same thing.

    Query of queries has limitations. For example, you can't use left joins there
    either.

    Dan Bracuk Guest

  7. #7

    Default Re: Query of Query Missing Some Data

    First, you can build the "memory table" entirely in your database without
    looping.
    [url]http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=2&thread[/url]
    id=1084623&highlight_key=y#3881838 uses related techniques. Or, you can
    lookup ranking and range queries on your SQL forum of choice.

    As for what is going on with your Q of Q, there are too many possibilities.
    Break the problem down to some small demo code and post that here.

    Here's some likely hints:
    1) Remember that structures and queries are passed around by reference. So
    you need to guard how you change these variables.

    Try this code:
    <CFDump var="#ReportTable#" label="Report Table Pre Sort">

    <CFSET qSafeLocalCopy = Duplicate (ReportTable)>

    <CFQuery name="ReportTable" dbtype="query">
    SELECT * FROM qSafeLocalCopy
    ORDER BY SortLastName, SortFirstName, emp_id, scheduled_date
    </CFQuery>

    <CFDump var="#ReportTable#" label="Sorted Result">
    <br>
    <CFDump var="#qSafeLocalCopy#" label="Tmp variable">


    Is it any better?


    MikerRoo Guest

  8. #8

    Default Re: Query of Query Missing Some Data

    You've given the query and the query-of-a-query the same name. Could that be the problem?


    BKBK Guest

Similar Threads

  1. #38976 [NEW]: missing last character when fetching query
    By zolfi at parspooyesh dot com in forum PHP Bugs
    Replies: 5
    Last Post: November 26th, 06:07 PM
  2. Fields missing in query
    By Robert10 in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 20th, 05:52 PM
  3. SQL query missing only date data elements
    By Jared13 in forum Coldfusion Database Access
    Replies: 1
    Last Post: January 12th, 04:21 PM
  4. Missing something in this query
    By quiero mas in forum Coldfusion Database Access
    Replies: 5
    Last Post: November 28th, 05:03 AM
  5. 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

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139