Hi!

Ok, here's what I'm trying. I have and array that I'm storing all the letters
of the alphabet, and I also want to count how many times a users 1st letter
of their last name match that letter in the alphabet. I created the array
as follows:

<cfset alphaList=arraynew(2)>
<cfset alphaListLoop="A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R ,S,T,U,V,W,X,Y,Z">
<cfset alphaLoopCount=1>
<cfloop list="#alphaListLoop#" delimiters="," index="i">
<cfset alphaList[#alphaLoopCount#][1]=#i#>
<!--- send to udf to count each letter occurance and assign
to variable --->
<cfinvoke component="components.reccount" method="getinfo"
returnvariable="result">
<cfinvokeargument name="alpha" value="#i#">
</cfinvoke>
<cfset alphaList[#alphaLoopCount#][2]=#result#>
<cfset alphaLoopCount=alphaLoopCount+1>
</cfloop>

So each letter is assigned, and the automatic count is set to 0.

The problem comes when I'm using the <cfinvoke>, here's the code for that:


<cfcomponent displayname="reccount" hint="Counts all records with a specific
1st letter">
<!--- This function retrieves all customers from the database --->
<cffunction name="getinfo"
hint="Counts records" returntype="numeric" output="no">
<cfargument name="alpha" required="yes" type="string">
<cfset var result = 0>
<cftry>
<cfquery name="CountRec" datasource="SalleBoise">
SELECT Cust_Name FROM inforeq
</cfquery>

<cfloop query="CountRec">
<cfset tmpLname=(findnocase(" ",#CountRec.Cust_Name#) + 1)>
<cfset tmpLname2=left(mid(LastRegistered.Cust_Name,tmpLna me,(tmpLname-1)),1)>
<cfif tmpLname2 eq #arguments.alpha#>
<cfset result=result + 1>
</cfif>
</cfloop>

<cfcatch type="any">
<cfset result = false>
</cfcatch>
</cftry>
<cfreturn result />
</cffunction>
</cfcomponent>

When I try to run the code, I'm getting:

Complex object types cannot be converted to simple values.
The expression has requested a variable or an intermediate expression result
as a simple value, however, the result cannot be converted to a simple value.
Simple values are strings, numbers, boolean values, and date/time values.
Queries, arrays, and COM objects are examples of complex values.

Where did I go wrong?????

that's some code...

how about:

SELECT COUNT(Cust_Name) AS totalcount
FROM inforeq
WHERE UPPER(SUBSTRING(Cust_Name, LOCATE(' ', Cust_Name)+1, 1)) =
<cfqueryparam cfsqltype="cf_sql_varchar" value="#ucase(arguments.alpha)#">

[this is mysql syntax, but other DBs will have equivalent functions]

the query will return totalcount - number of records where the second
word in Cust_Name column begins with a given #alpha# letter.


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Hello Azadi,

Thanks....not sure if that's a good comment or not....

So, you're saying try your code if the CFC instead of what I have?

> that's some code...
>
> how about:
>
> SELECT COUNT(Cust_Name) AS totalcount
> FROM inforeq
> WHERE UPPER(SUBSTRING(Cust_Name, LOCATE(' ', Cust_Name)+1, 1)) =
> <cfqueryparam cfsqltype="cf_sql_varchar"
> value="#ucase(arguments.alpha)#">
> [this is mysql syntax, but other DBs will have equivalent functions]
>
> the query will return totalcount - number of records where the second
> word in Cust_Name column begins with a given #alpha# letter.
>
> Azadi Saryev
> Sabai-dee.com
> http://www.sabai-dee.com/

What db are you using? You might be able to do it with sql.

Hello Dan,

This is on a MySql database

> What db are you using? You might be able to do it with sql.
>

I don't use mySql, but this is the approach I would take. I'm stealing Azadi's
syntax for the part where you find the first letter in the last name.

letters = 'A,B,C,...Y';

SELECT TheLetter, COUNT(TheLetter) AS totalcount
FROM inforeq
left join
(<cfloop list = "#letters# index = "ThisLetter">
select distinct '#ThisLetter#' TheLetter
from SomeSmallTable
union
</cfloop>
select distinct 'Z' TheLetter
from SomeSmallTable) sq on TheLetter = UPPER(SUBSTRING(Cust_Name, LOCATE(' ',
Cust_Name)+1, 1))


You might have to change this:
COUNT(TheLetter)
to something like this
ifnull(COUNT(TheLetter), 0)

or you might not

good idea, Dan! i might use that someday instead of my usual concoction
of querynew(), structuses and lists...

just... shouldn't it be RIGHT JOIN so the query selects all letters of
the alphabet?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[q]Originally posted by: Newsgroup User
good idea, Dan! i might use that someday instead of my usual concoction
of querynew(), structuses and lists...

just... shouldn't it be RIGHT JOIN so the query selects all letters of
the alphabet?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
[/q]
It is right join. Newsgroup users don't get to see edits. Besides, it's
Steve's jobs to work out the details.

Hello Dan,

I did get it working. Thanks to the both of you for your help!

> [q]Originally posted by: Newsgroup User
> good idea, Dan! i might use that someday instead of my usual
> concoction
> of querynew(), structuses and lists...
> just... shouldn't it be RIGHT JOIN so the query selects all letters
> of
> the alphabet?
> Azadi Saryev
> Sabai-dee.com
> http://www.sabai-dee.com/
> [/q]
> It is right join. Newsgroup users don't get to see edits. Besides,
> it's
> Steve's jobs to work out the details.