On Saturday, February 21, 2004, 4:17:42 PM, you wrote:
SK> One thing that may be causing it is one datasource is in SQL and
SK> the other is in access.
I don't think it should matter that they're different DBMSs. I would
guess that QoQ just can't do this type of query. Also make sure your
version of CF supports QoQ (I think it starts at CF5).
SK> <cfquery datasource="ds1" username="u" password="p" name="main">
SK> SELECT common.id, common.full_name
SK> FROM csusa_common
SK> </cfquery>
SK> <CFQUERY datasource="ds2" name="mine">
SK> SELECT filename, id FROM table1 ORDER BY id
SK> </CFQUERY>
SK> <cfquery dbtype="ds3" name="final">
SK> select common.id, common.full_name, id, filename
SK> from main, mine
SK> where main.csusa_common.id = mine.id
SK> </cfquery>
When you use a QoQ, the original query results are treated as if they
are tables, so I believe it's incorrect to reference the original
table names in your QoQ. All the QoQ needs to know is the source query
name (used as a table name) and the column(s).
Also, if I understand your needs here, the use of UNION in the QoQ is
not what you need (I don't recall if it's even supported) as each
table has a common ID that you are trying to use to match a given user
to their file(s).
A UNION requires that both source tables/queries feature the exact
same column names (I believe you can use aliases to make sure they
match):
select id, name from foobar
union
select id, name from foobar2
Say foobar has the following records:
1 Bob
3 Jane
And foobar2 has:
3 Gordon
44 Elizabeth
A UNION would return:
1 Bob
3 Jane
3 Gordon
44 Elizabeth
If the join I mentioned earlier doesn't work in QoQ, perhaps you can
query the remote data, dump it into a local table, and then do a
standard (i.e., non-QoQ) SQL query joining your original local data
with the data you just imported.
~ Ubqtous ~
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
