Skip to main content
S
suluclac
Known Participant
October 20, 2010
Question

SQL function support within cfquery

  • October 20, 2010
  • 2 replies
  • 1343 views

Are all SQL statements supported by <cfquery>?

I'm tring to use FORMAT() with no luck.

p.s. using MySQL db

This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
October 20, 2010

In addition to Adam's answer, it depends on whether the function is native or user defined.  If the latter, there may be permissions to be granted.

What problem are you having anyhow?

S
suluclacAuthor
Known Participant
October 21, 2010

I am doing simple math within a query of query.

Specifically:

<cfquery dbtype="query" name="qClubRecord">

SELECT

SUM(WIN) AS WINS,

SUM(LOSS) AS LOSSES,

SUM(TIE) AS TIES,

SUM(WIN)/(SUM(WIN) + SUM(LOSS) + SUM(TIE)) AS WIN_PERCENTAGE

FROM

qGetRecords

</cfquery>

This works.

My problem arises when displaying. I would like to show only 2 decimal places.

Now, I'm using <cfgrid> with format="html", so I can't use numberFormat.

I should be able to solve this problem within SQL. After a quick look I found the FORMAT() statement.

Works as advertised from the command line, but incorporating into <cfquery> fails (whether QoQ or not).

So I'm stuck.

<cfquery dbtype="query" name="qClubRecord">

SELECT

SUM(WIN) AS WINS,

SUM(LOSS) AS LOSSES,

SUM(TIE) AS TIES,

FORMAT(SUM(WIN)/(SUM(WIN) + SUM(LOSS) + SUM(TIE)),2) AS WIN_PERCENTAGE

FROM

qGetRecords

</cfquery>

Thank you both for answering.

A
Adam Cameron.
Inspiring
October 21, 2010

I should be able to solve this problem within SQL. After a quick look I found the FORMAT() statement.

Works as advertised from the command line, but incorporating into <cfquery> fails (whether QoQ or not).

So I'm stuck.

<cfquery dbtype="query" name="qClubRecord">

SELECT

SUM(WIN) AS WINS,

SUM(LOSS) AS LOSSES,

SUM(TIE) AS TIES,

FORMAT(SUM(WIN)/(SUM(WIN) + SUM(LOSS) + SUM(TIE)),2) AS WIN_PERCENTAGE

FROM

qGetRecords

</cfquery>

Thank you both for answering.

Did you read the docs I pointed you to regarding QoQ, and does it indicate it supports format()?

--

Adam

A
Adam Cameron.
Inspiring
October 20, 2010

It depends on what you mean.

<cfquery> doesn't support any SQL statements.  All <cfquery> does is pass the string within it (along with any params) to the DB driver, which passes it on to the DB.  <cfquery> knows nothing about SQL.

The only situation in which "<cfquery>" might be expected to know something about SQL is in a query-of-query situation, although even then <cfquery> just passes the SQL to the IMQ (I am guessing that stands for In-Memory Query?)... um... driver / engine / system / whatever-one-might-call-it.

The SQL that IMQ supports is very minimalist, and is detailed - in full - in the docs:

Query of Queries user guide

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html

But for normal queries, CF just passes the SQL to your database, and that's where the SQL is executed.  The SQL is just a string, as far as CF is concerned.

--

Adam

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices