N. Ziff N. Ziff - 3 months ago 13
SQL Question

How can I CFLoop a query in another query correctly?

Background/Purpose: I am creating a coldfusion document that contains SQL for grabbing values from my company's database. I am finding the conversion rate (Licenses Sold / Registrations) per Sales Rep on our team. Each Rep has an ID (

RegionalDirectorID
) tied to users to help keep track.

Question/Problem: The problem is that we have other workers that sell licenses here or there, such as our CEO, Head Developer etc. We have 8 sales reps with the
UserType
of 8. I am using another query to select those UserTypes, to differentiate from other people so that our data doesn't get mixed up selecting them too.

As you can see below, I am using a cfloop to loop thru the
getUsers
query, specifically to help the line
(Users.RegionalDirectorID = #getUsers.UserID#)
print out all of the ID's of our sales reps. When I don't have the cfloop entered, I only get one row showing one sales rep. If I do have the cfloop, I will get the last sales rep.

Code:



<cfset myQuery = QueryNew("ID, ConversionRate")>

<cfquery name="getUsers" datasource="#dsn#">
Select UserID FROM USERS WHERE
UserTypeID = 8
AND ISACTIVE = 1
</cfquery>

<cfquery name="getREGRD" datasource="#dsn#">
Select COUNT(DISTINCT(Users.UserID)) AS TOTALREG, RegionalDirectorID
FROM Users
WHERE UserTypeID = 3
<!--- <cfloop query="getUsers">
AND (Users.RegionalDirectorID = #getUsers.UserID#)
</cfloop>--->
AND (PARENTID IS NULL OR PARENTID = 0)
<cfif len(selectMonth)>
AND MONTH(Users.DateStamp) = #selectMonth#
<cfelse>
AND MONTH(Users.DateStamp) = #MONTH(NOW())#
</cfif>
GROUP BY Users.RegionalDirectorID
</cfquery>

<cfquery name="getREGRDSold" datasource="#dsn#">
Select COUNT(DISTINCT(UserTractLicense.UserID)) AS TOTALSOLD, Users.RegionalDirectorID
FROM Users, UserTractLicense
WHERE Users.UserID = UserTractLicense.UserID
AND Users.UserTypeID = 3 AND
(PARENTID IS NULL OR PARENTID = 0)
<cfif len(selectMonth)>
AND MONTH(Users.DateStamp) = #selectMonth#
<cfelse>
AND MONTH(Users.DateStamp) = #MONTH(NOW())#
</cfif>
GROUP BY Users.RegionalDirectorID
</cfquery>

<!---<cfset newRow = QueryAddRow(myQuery, #getREGRD.RecordCount#)>
<cfloop query="getREGRD">
<cfset QuerySetCell(myQuery, "ID", #getREGRD.RegionalDirectorID#, getREGRD.currentRow) />
</cfloop>
<cfloop query="getREGRDSold">
<cfset QuerySetCell(myQuery, "ConversionRate", #getREGRDSold.TOTALSOLD#/#getREGRD.TOTALREG#, getREGRDSold.currentRow) />
</cfloop>
--->

<!---<cfdump var="#myQuery#">--->
<cfdump var="#getREGRD#">
<cfdump var="#getREGRDSold#">

<cfoutput query="getREGRDSold">
#getREGRDSold.RegionalDirectorID#
</cfoutput>


The cfloop was causing the newQuery to shoot out the error below. That is why some of it is commented out:


An error occurred while evaluating the QueryAddRow function: Parameter> 2, 0, of the QueryAddRow function must be a positive integer.
The
error occurred on line 70.


The resulting data has some RegionalDirectorID's that do not have the UserTypeID of 8, such as the NULL cells, which I would like to remove from the results of the query.

Sample Results

Answer

It sounds like all you need is a single JOIN, instead of all the extra looping and queries.

If the User table stores both customers and sales reps, you will need a self join. In other words, join the User table to itself by using aliases. Then filter each one on the appropriate user types:

SELECT  cust.RegionalDirectorID
        , COUNT(DISTINCT(lic.UserID))  AS TOTALSOLD
FROM    Users cust
           INNER JOIN User sales ON sales.UserID = cust.RegionalDirectorID
           INNER JOIN UserTractLicense lic ON lic.UserID = cust.UserID
<!--- customers --->
WHERE   cust.UserTypeID = 3 
<!--- sales reps --->
AND     sales.UserTypeID = 8
... etcetera 
GROUP BY cust.RegionalDirectorID

A few other comments:

  1. While not always technically required, it is a good practice to fully qualify all column names whenever a query involves multiple tables.

  2. Along the same lines, be sure to scope all variables. For example, use FORM.selectMonth or URL.selectMonth instead of just selectMonth.

  3. Always use cfqueryparam on all variable query parameters. The most important reason is to protect your database from sql injection. It also helps improves performance for queries executed multiple times.

MONTH(Users.DateStamp) = #selectMonth#

  1. I noticed the query only filters on month number (not month and year). So if selectedMonth = 8, the query will return results for the month of "August" - in any year. Was that your intention? If not, you will obviously want add a year filter as well. However, using functions on an indexed column usually prevents the database from utilizing indexes. So you might consider using this paradigm which is more index friendly:

     WHERE  TheDateColumn >= TheStartDateAtMidnight         
     AND    TheDateColumn < TheDayAfterEndDateAtMidnight