Query of Query Missing Some Data

[g1zm0guy]

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]

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?

[CFRandy]

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

[Dan Bracuk]

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.

[g1zm0guy]

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? :)

[Dan Bracuk]

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.

[MikerRoo]

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?

[BKBK]

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