LiveDocs Comments - coldfusion - 6 - Developing_ColdFusion_MX_Applications_with_CFML - using_recordsets4.htm

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

xiaoxiaoshijie — 2004-09-29T06:17:46

I have a table named Products, and a column in it named ExperEmailList.
For example,
productID expertemaillist
1 test.test@test.com, html@html.com
2 hyg@yahoo.com,no-replied@uconn.edu
...

I have another table Users, which contains all information about users,including experts.
For example,
userID Email FirstName LastName
1 hyg@yahoo.com hua wang
...

Now I want to know, for example, the last name of all experts in product 2.

I could have the following sql,
Select Lastname from users,products
where email in expertemaillist

But it does not work. If I have singlequote in expertemaillist, that would work. But I dont. By the way, the expertemaillist comes from a checkbox, so I could choose as many as I want.
What should I do now to implement my aim?
Thanks.

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

extdw_doc — 2004-09-02T10:39:00

If you believe that your issue might result from a ColdFusion bug, you should also submit a bug report on the Macromedia Feature Request/Bug Report Form. If it proves to be a documentation problem, it will be resolved through that process.

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

rrogers — 2004-09-01T13:55:44

re: "can't convert the string .... to java type DOUBLE "

This seems to occur on our servers, too, if the column's value in the first row of the recordset is something that is all numeric, like: 123456 When an all-numeric value like this is encountered as the first value, CF assumes that ALL values in that column are numeric... thus, it errors out if it comes accross a record containing both numbers and letters, since a mix of letters and numbers will be considered a string.

I think this error first popped up for us when we made the switch to CFMX, which was the first version to have a java backend. I think the java makes some un-safe assumptions when doing query-of-queries, which hopefully will get fixed in a future release. This might explain why it works for you in development, but not in production (if you're running 2 different versions of CF server).

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

I'mAndy — 2004-08-25T11:37:14

Column name alias in QofQ in CFC
-------------------------------------------------

In this code

SELECT q_B.MonthName as MaxMonthName , q_B.MaxTemp, q_B.MaxTempDay
FROM q_A, q_B
WHERE q_A.MaxTemp = q_B.MaxTemp

SELECT q_B.MonthName as MinMonthName, q_B.MinTemp, q_B.MinTempDay
FROM q_A, q_B
WHERE q_A.MinTemp = q_B.MinTemp

SELECT q_D.MinTemp, q_D.MinTempDay, q_C.MaxTemp, q_C.MaxTempDay,q_C.MaxMonthName,q_D.MinMonthName
FROM q_C, q_D

The return I obtain is only the second Month name i.e q_D.MinMonthName and it 's column name shows as MonthName

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

sinistral — 2004-08-24T09:57:34

One other comment... what about subqueries?

USING:

SELECT ThisID, Name, D_N, SUM(ThisNum) as Entered
FROM (SELECT ThisID, Name, D_N, 0 as ThisNum
FROM qryPossibleEmpParts
UNION
SELECT part_ID, Part_Name, D_N, 1 as ThisNum
FROM qryCurrentParts, qryPossibleEmpParts
WHERE part_ID = ThisID)
GROUP BY ThisID, Name, D_N
HAVING Entered = 0
ORDER BY Name

Note: I have both aliased and non-aliased the subquery into its own table name... error states a problem at line 0 column 0 regarding the '('

So. this leads me to believe subqueries are not allowed using the dbtype="query", although one can push precedence in multiple UNION queries (and where clauses) via the use of parens.

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

sinistral — 2004-08-24T09:43:34

It appears the cfquery with dbtype will only recognize INNER JOIN which means that a LEFT OUTER JOIN or [any] OUTER JOIN is not allowed. Is there a way to work around this, or fix this?

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

tedkatie — 2004-08-10T05:45:26

No one seems to understand why I am getting these errors. It work fine on my dev pc. Uploaded, it errors. My webhost and I are running the same software environment (I know something is different, Just can figure it out.).

Main Query:

select * from #prices#

Error Occurred While Processing Request
Error Executing Database Query.

Query Of Queries runtime error.

Query Of Queries runtime error.
Can't convert the string ZZZZ to java type DOUBLE

The error occurred in C:\Sites\accounts\tedciotti\buymagscheap.net\www\nav\search.cfm: line 15
Called from C:\Sites\accounts\tedciotti\buymagscheap.net\www\nav\search.cfm: line 7
Called from C:\Sites\accounts\tedciotti\buymagscheap.net\www\nav\search.cfm: line 1
Called from C:\Sites\accounts\tedciotti\buymagscheap.net\www\nav\search.cfm: line 15
Called from C:\Sites\accounts\tedciotti\buymagscheap.net\www\nav\search.cfm: line 7
Called from C:\Sites\accounts\tedciotti\buymagscheap.net\www\nav\search.cfm: line 1

13 : select *
14 : from search_mags
15 : where magazine like '#searchName#%'
16 : ORDER BY MAGAZINE,YEARS
17 :

--------------------------------------------------------------------------------

SQL select * from search_mags where magazine like 'R%' ORDER BY MAGAZINE,YEARS

______________________________________________
*****************************************************************
The code:

select *
from search_mags
where magazine like '#searchName#%'
ORDER BY MAGAZINE,YEARS

select *
from search_mags
where TRADECONSU = 'P' or comments1 = 'MAGAZINE OF THE MONTH'
ORDER BY MAGAZINE,YEARS

select *
from search_mags
where savings > 49.99
order by savings desc

select *
from search_mags
where magazine like '%#searchName#%'
ORDER BY MAGAZINE,YEARS

select *
from search_mags
where magazine like '%#searchName#%' or comments1 like '%#searchName#%'
ORDER BY MAGAZINE,YEARS

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

redeyedcfc — 2004-07-28T00:07:05

Can you delete with queries of queries.
For example, if I'm going to use the same parent query for many sub queries and I don't want the same row returned more than once.

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

jrunrandy — 2004-07-27T12:53:28

Server side. Your mileage may vary depending on the processing in your QofQ SELECT statement, the size of your result set, available JVM heap size, and your server processing speed.

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

2004-07-26T08:45:39

Are record sets created client side, or does the server do the work? If the server does the work, how does this increase response time?

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

rippo123456 — 2004-07-26T03:50:47

why does this inner join work?

select * from application.categories

select * from application.property

SELECT address, Number_of_Rooms_Description, rent, deposit
FROM qProp, qCat
where qProp.idCategory = qCat.idCategory

but this does not?

SELECT address, Number_of_Rooms_Description, rent, deposit
FROM application.property, application.categories
WHERE application.property.idCategory=application.categories.idCategory

I get this error

Query Of Queries syntax error.
Encountered "application . property ." at line 0, column 0. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

2004-05-23T21:09:43

<cfdirectory action="LIST" directory="#variables.backupFolder#"
name="qDirectoryListing" sort="datelastmodified">

<cfquery name="qTodaysFiles" dbtype="query">
select * from qDirectoryListing where datelastmodified = #now()#
</cfquery>

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

halL — 2004-04-28T09:05:48

Query of Queries does not allow aliases on tables, only columns, so you cannot do a self join.
We have entered an enhancement request, number 55265, for this feature.

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

2004-04-17T12:59:36

How can I use a inner join, to query a self-join? The Notes above mention that you can't use aliases on a table (only columns). I'm trying to pull geneological information so all people are listed in the same table. any ideas how this can be performed in CF?

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

jrunrandy — 2003-12-04T07:02:49

Try the BNF syntax on the next page. That seems to work for most people (although I think it's kind of dense).

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

clementi — 2003-12-03T14:45:58

I would like to see a complete QoQ reference, too. Like really bad.

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

Paul Murphy — 2003-09-02T14:03:28

Where is the full documentation on the full QofQ syntax?
i.e. I'd love to use the LEFT function in a query but there is no syntax documentation available.
I'd also love to know explicitly which functions are available and how to use them.

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

Wild_Cujo — 2003-07-02T19:50:00

Will outer joins ever be supported?

coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm

bhogue — 2003-03-13T10:07:00

This document does not EXPLICITLY say anything about Inner Joins on more than 2 tables.