Query of Query Error

[driggle]

This is the error I get -
Query Of Queries syntax error. Encountered ". Query Of Queries runtime error.
The Cold Fusion server is running MX 7,0,1,116466.
The sql is "Select * from Invlotquery2 where lotnum = '2011-61-12' ". (The
extra space after the single quote is for clarity.)
I have already built the query and the user is trying to filter on a lot
number. Whenever the lot number starts as numeric and is 10 digits
long(2011-61-12) the above error happens. A lotnumber thats starts as a
character or a lot number that starts numeric and has less than 10 characters
or more than 10 characters works ok. I still have code in place to create a
dummy record for the query that sets the query fields up as characters. I found
out that previous versions of Cold Fusion could create query fields as numeric
if not specified.
This really seems to be a user specific error but I thought I would post here
to see if anyone has had a similiar problem or knows of a solution.

Thanks,

Dale

[Dan Bracuk]

My guess is that Cold Fusion is interpreting the lotnum field as a date instead
of a string. To get around that, you might want to add an additional field to
the select clause of your initial query that prepends a letter to the lot
number. Then, in your Q of Q, use that field in your where clause.

[driggle]

That was it.

Thanks again.

[Adam Cameron]

> My guess is that Cold Fusion is interpreting the lotnum field as a date instead

> of a string. To get around that, you might want to add an additional field to
> the select clause of your initial query that prepends a letter to the lot
> number. Then, in your Q of Q, use that field in your where clause.

*OR* just use a <cfqueryparam> to tell CF what date type it actually is
(probably a varchar). That way you don't need to mung your data.

--
Adam

[driggle]

Does a cfqueryparam work with a query of query?

[Adam Cameron]

> Does a cfqueryparam work with a query of query?

Yes. I'm not so cruel as to suggest something that doesn't work ;-)

--
Adam

[Adam Cameron]

>> Does a cfqueryparam work with a query of query?

> Yes. I'm not so cruel as to suggest something that doesn't work ;-)

Hmmm.

Seems there's *degrees* of working. They certainly work when telling CF
that a value *is* a date, but seemingly not so much when telling CF that a
string *isn't* a date, even when it clearly isn't.

See this sample code:

<cfset q1 = queryNew("iCol,dCol","integer,VarChar")><!--- no dates here
--->
<cfset queryAddRow(q1)><cfset querySetCell(q1, "iCol", 1)><cfset
querySetCell(q1, "dCol", "2011-61-12")><!--- not a date --->
<cfset queryAddRow(q1)><cfset querySetCell(q1, "iCol", 2)><cfset
querySetCell(q1, "dCol", "2006-06-29")>
<cfset queryAddRow(q1)><cfset querySetCell(q1, "iCol", 3)><cfset
querySetCell(q1, "dCol", "2006-07-29")>
<cfset queryAddRow(q1)><cfset querySetCell(q1, "iCol", 4)><cfset
querySetCell(q1, "dCol", "2006-08-29")>
<cfset queryAddRow(q1)><cfset querySetCell(q1, "iCol", 5)><cfset
querySetCell(q1, "dCol", "2011-1-12")>

<cfquery name="q2" dbtype="query">
select iCol, dCol
from q1
where dCol = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#javacast('string', '2011-61-12')#"><!--- change '61' to '1' and it
works --->
</cfquery>

<cfdump var="#q2#">

Now... column dCol is CLEARLY not date data:
1) I'm specifying it as VARCHAR in the query definition.
2) The first value in it is definitely not a date.
3) Nowhere am I treating it like a date.

Although CF apparently knows better, and goes 'ooh... despite him saying
it's not a date, the data not being date data, and when he uses the data,
he specifically says "it's not a date" (and man... he's even saying that
TWICE!)... I reckon cos SOME of the date looks kinda like a date... I know
what I'll do!!" [and proceeds to collapse in a heap, because it decides I'm
using dates].

So. Looks like I'm gonna have to suck it up, and suggest you should listen
to Dan and not me!

--
Adam

(PS: I shall raise a bug with Adobe regarding this behaviour).

[driggle]

Thanks for trying this. I was trying to use cfqueryparam but was having syntax issues. Just as well since you found a problem.
Thanks for the informative post. :-)

[paross1]

Did anyone try using CAST() in this instance instead of using cfqueryparam?
According to [url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/00001270.htm[/url]
you can use CAST() in Q-of-Q to ensure the correct datatype.

In some cases, ColdFusion MX can guess a data type that is inappropriate
for your application. In particular, if you use columns in a WHERE clause or
other conditional expression, the data types must be compatible. If they are
not compatible, you must use the CAST function to recast one of the columns to
a compatible data type. For more information on casting, see Using the CAST
function. For more information on data type compatibility, see Understanding
Query of Queries processing.

Something like this perhaps? I haven't tried it myself, but it might be an
option.

Select *
from Invlotquery2
where lotnum = CAST('2011-61-12' AS varchar)

Phil

[Adam Cameron]

> CAST

<SLAPS HEAD>
It was blimin' me who instigated the request for Macrmoedia to put that
function into CFMX7 in the first place.
</SLAPS HEAD>

--
Adam

(suddenly feeling rather thick)

[paross1]

Good one, Adam! Senior moment? :grin;