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__-]

> 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 they 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__-]

> Where can one find this list?

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

[-__cfSearching__-]

kodemonki wrote:
> 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.

[-__cfSearching__-]

It is there: Query of Queries supports two string functions, UPPER() and LOWER(). That and conditional operators are pretty much it.

[Newsgroup_User · Accepted solution]

> 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.

--
Adam

[-__cfSearching__-]

Adam Cameron wrote:
> 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

> upper()
> lower()
> ...
> cast()
> I think that's about it.

Yes, I was referring to string functions only (not aggregates, etcetera). Sorry for any confusion.