Inspiring

Answered

Query of Queries

Forum|Forum|17 years ago
March 27, 2009
10 replies
5007 views

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!

Getting started

This topic has been closed for replies.

Correct answer Newsgroup_User

> 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

N

Newsgroup_UserCorrect answer

Inspiring

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

Inspiring

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.

-

-__cfSearching__-

Inspiring

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

K

kodemonkiAuthor

Inspiring

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

Inspiring

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.

K

kodemonkiAuthor

Inspiring

Where can one find this list?

-

-__cfSearching__-

Inspiring

> Where can one find this list?

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

-

-__cfSearching__-

Inspiring

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

K

kodemonkiAuthor

Inspiring

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.

K

kodemonkiAuthor

Inspiring

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.

-

-__cfSearching__-

Inspiring

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

D

Dan_Bracuk

Inspiring

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded