12 Replies Latest reply: Mar 27, 2009 6:13 PM by -==cfSearching==-

Query of Queries

kodemonki Mar 27, 2009 11:10 AM

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!

1. Re: Query of Queries

Dan Bracuk Mar 27, 2009 12:18 PM (in response to kodemonki)

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.
Like Show 0 Likes (0)

Actions
2. Re: Query of Queries

-==cfSearching==- Mar 27, 2009 12:22 PM (in response to kodemonki)

> , 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.
Like Show 0 Likes (0)

Actions
3. Re: Query of Queries

kodemonki Mar 27, 2009 12:31 PM (in response to 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.
Like Show 0 Likes (0)

Actions
4. Re: Query of Queries

kodemonki Mar 27, 2009 1:24 PM (in response to 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.
Like Show 0 Likes (0)

Actions
5. Query of Queries

-==cfSearching==- Mar 27, 2009 1:35 PM (in response to 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 they are still ColdFusion functions. So you are limited to whatever ColdFusion provides. There is no substring function for QoQ's.
Like Show 0 Likes (0)

Actions
6. Re: Query of Queries

kodemonki Mar 27, 2009 1:39 PM (in response to kodemonki)

Where can one find this list?
Like Show 0 Likes (0)

Actions
7. Re: Query of Queries

-==cfSearching==- Mar 27, 2009 1:42 PM (in response to kodemonki)

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.
Like Show 0 Likes (0)

Actions
8. Re: Query of Queries

-==cfSearching==- Mar 27, 2009 1:49 PM (in response to kodemonki)

> Where can one find this list?

The documentation:
http://livedocs.adobe.com/coldfusion/8/htmldocs/using_recordsets_1.html
Like Show 0 Likes (0)

Actions
9. Re: Query of Queries

kodemonki Mar 27, 2009 1:55 PM (in response to 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.
Like Show 0 Likes (0)

Actions
10. Re: Query of Queries

-==cfSearching==- Mar 27, 2009 2:01 PM (in response to kodemonki)

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

Actions
11. Re: Query of Queries

Newsgroup_User Mar 27, 2009 5:51 PM (in response to 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.

--
Adam
Like Show 0 Likes (0)

Actions
12. Query of Queries

-==cfSearching==- Mar 27, 2009 6:13 PM (in response to Newsgroup_User)

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.
Like Show 0 Likes (0)

Actions