Query of Queries

[kodemonki]

Apparently you can't use

<cfquery name="client_files" dbtype="query">
SELECT name
FROM dealer
WHERE left(name, 3) = 'BP_'
</cfquery>

you have to use

<cfquery name="client_files" dbtype="query">
SELECT name
FROM dealer
WHERE name like 'BP_%'
</cfquery>

what SQL syntax is CF using? I use Oracle, so I'm having difficulty figuring
out how to do what I need. Now that I've found how to get the first three
characters to match, I need to find the max of the last 14 characters
(timestampe) to get the most recent one. Something tells me I can't use
something like max(substr(name,length(name)-10,10)) inside the query.

Any ideas?

Thanks!

[Dan Bracuk]

Q of Q has limited functionality. When it does use a function, it uses an sql
function, not a cf function. For example, if you want to select something in
upper case, you do this

select upper(fieldname)

not this

select ucase(fieldname)

Having said that, there are lots of things you can do with cfloop and
querysetcell.

[-==cfSearching==-]

, I need to find the max of the last 14 characters (timestampe) to
get the most recent one

Is this a date/time field? If it is, use the aggregate max function.

[kodemonki]

If Q of Q uses SQL instead of CF functions, why can't I use substr() or
substring() in the WHERE clause?

The NAME Q of Q column is a string, in the format (in this case)
'BP_YYYYMMDDHHMMSS.pdf' where I need to find the most recent file.

[kodemonki]

SELECT max(name) AS file_name
FROM dealer
WHERE name like 'BP_%'

Does what I want, but that doesn't answer my question about being able to use SQL functions in WHERE clauses.

[-==cfSearching==-]

Post by kodemonki
If Q of Q uses SQL instead of CF functions, why can't I
use substr() or substring() in the WHERE clause?

Because you are not using a database or ansi sql. QoQ are executed in
ColdFusion. ColdFusion provides a certain set of functions for use in regular
CF code, and another for use in QoQ's only. The QoQ functions are similar to
those found in ansi sql, but the are still ColdFusion functions. So you are
limited to whatever ColdFusion provides. There is no substring function for
QoQ's.

[kodemonki]

Where can one find this list?

[-==cfSearching==-]

Post by kodemonki
Where can one find this list?

The documentation:
http://livedocs.adobe.com/coldfusion/8/htmldocs/using_recordsets_1.html

[-==cfSearching==-]

SELECT max(name) AS file_name
Does what I want

... As long as the file extension is the same for all of the files.

[kodemonki]

I actually read the links from that link before posting here and nowhere could I find a list of functions that could be used.

[Adam Cameron]

Post by kodemonki
I actually read the links from that link before posting here and nowhere could I find a list of functions that could be used.

It's poorly documented.

upper()
lower()
count()
max()
min()
sum()
avg()

cast()

I think that's about it.

I believe QoQ is very limited the way it is currently implemented.

I also know there is another version of CF in the works at present. So
this would be a very good time for people to raise any enhancements to QoQ
they might think of.

[-==cfSearching==-]

Post by Adam Cameron
It's poorly documented.

The older documentation was more thorough. Probably because it was a new
feature back then.

http://livedocs.adobe.com/coldfusion/6.1/htmldocs/using_29.htm
http://livedocs.adobe.com/coldfusion/6.1/htmldocs/using_re.htm
http://livedocs.adobe.com/coldfusion/6.1/htmldocs/using_39.htm#wp1175636