6

I wanted to use count() function in ColdFusion Query object.

Here is my code and test:

<cfset x = querynew("id,name")>

<cfquery name="y" dbtype="query">
    select count(*) as total from x
</cfquery>

<cfoutput>Test1: #y.total#</cfoutput>

<cfset temp = QueryAddRow(x)>
<cfset Temp = QuerySetCell(x, "id", 1)>
<cfset Temp = QuerySetCell(x, "Name", "Vikas")>

<cfquery name="y" dbtype="query">
    select count(*) as total from x
</cfquery>

<cfoutput>Test2: #y.total#</cfoutput>

Should I use convert function? Like if total is [enpty string] then result should be 0. Or is there any other best/proper way to do that?

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Vikas
  • 24,082
  • 37
  • 117
  • 159
  • Basically not MATH function `count` but count number of row like `count(*)` in SQL server – Vikas Aug 16 '11 at 09:28

3 Answers3

10

It does seem like this is a bug, however there is an easy way around it. Simply wrap the y.total in val(), so it would read: 

<cfoutput>Test1: #val(y.total)#</cfoutput> 
<cfoutput>Test2: #val(y.total)#</cfoutput>

val() will return 0 if an empty string is passed to it.

Scott Stroz
  • 7,510
  • 2
  • 21
  • 25
2

I think you've found a bug in CF here (perhaps log it @ http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html).

The first query should return 0, not [nothing]!

In your simple example, I think just dispense with the QoQ entirely, and just use x.recordCount.

But obviously this is not much chop if you have a WHERE filter in your QoQ, in which case You're gonna need to do something like you suggest. I can't see a better approach here.

If you raise that bug with Adobe, let us know the bug ref so we can vote for it ;-)

-- Adam

Adam Cameron
  • 29,677
  • 4
  • 37
  • 78
  • Heh. I had a search around in the bugbase, and noticed actually *I* raised a bug for this back in 2008: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=73559. It's not exactly the same, but it's demonstrating the same problem. – Adam Cameron Feb 14 '12 at 13:42
  • Thanks. I now worked with a workaround, like all the others, who have had this problem. btw: is this link only working, if i am logged in? – Tobias Feb 15 '12 at 07:02
-1

If you just want the number of rows in de query-object use: #x.recordcount#

deepvanbinnen
  • 79
  • 2