Query of Queries Combinding Columns

[tjestus]

I have two text files that I am using a cfhttp tag to query. I then need to
combined the two queries to one query subtracting the "CHANGE" field from the
second query from the "CHANGE" field in the first query. I will then use the
combined query to create a new file. I can make the new file ok but cannot
figure out how to subtract the two columns while combined them into one query.
The two files have the same column names however the records form the first
file might not have a match in the second file. I need to combine the records
that match.

I tried looping through the query and doing a query on the second checking it
for a matching "PASS_NO" then subtracting the two "CHANGE" fields if there was
a match but it times out on me.

File one gets updated frequently and needs to be compared to file two each
time it is updated.

FILE STRUCTURE

File One:
FIRST_NAME,LAST_NAME,GUEST_NO,PASS_NO,ZIP,CHANGE

File Two
FIRST_NAME,LAST_NAME,GUEST_NO,PASS_NO,ZIP,CHANGE


RECORD EXAMPLES (There are less than 100 records in file two and several
hundred in file one)

File One
ALICIA,SMITH,55009,667020000,8364004000,7
TOM,JONES,55976,692020000,8460004000,8
ALLIE,JOHNSON,55419,703020000,119017000,5
SKYE,DOE,55053,3007004000,8452004000,24

File Two
ALICIA,SMITH,55009,667020000,8364004000,2
SKYE,DOE,55053,3007004000,8452004000,5


Any help will be appreciated.FILE STRUCTURE

[Dan Bracuk]

why don't you use query of queries? The substraction part would be:

select blah, blah, q1.change - q2.change as the change

[tjestus]

Dan

Thanks for the suggestion. I tried that once but kept getting and error using
the "-" operator. I tried it again and this time added in some cfqueryparam
tags to get it to read the fields as numbers in stead of characters (see code
below). Now my problem is if I get over a few hundred records it times out on
me. The estimate file sizes will be about 350 records in file two and 4,500 in
file one. OUCH! Any idea on how to do this more effiecently?



<cfquery name="q3" dbtype="query">
SELECT q1.FIRST_NAME,q1.LAST_NAME,q1.GUEST_NO,q1.PASS_NO, q1.ZIP,(<cfqueryparam
cfsqltype="cf_sql_numeric" value="#q1.CHANGE#"> - <cfqueryparam
cfsqltype="cf_sql_numeric" value="#q2.CHANGE#">) AS NEWCHANGE
FROM q1, q2
</cfquery>

[Dan Bracuk]

do the Q of Q, and include q1.change and q2.change in your select clause. Also
include a constant number, 1000 for example, in the select clause. Give all
these fields alias names.

something like
select q1.change as change1, q2.change as change2, 1000 as thedifference

Then, loop through your new query and use querysetcell to change the value of
the thedifference field.

You'll only have to do it 350 times at most if that's the size of your smaller
file, so it should be pretty quick.