Difference of COUNT(*) between Query of Query(QoQ) and normal SQL Query

Before explaining the exact situation let me show you some code which we come across most of the time in our daily work life.

In above code here I'm displaying:

1. Total artists records.
2. COUNT of total no of artists by a condition which will return 0 record.
3. COUNT of total no of artists by a condition on Query of Query which also returns 0 record.

Lets see the output:

Here by first dump we get total no of records and in 2nd dump we are getting 1 record with ArtistCount column and value is 0, as the condition of the query doesn't matches any record. The 3rd dump where we again calculates the artist count but by Query of Query we get 0 no of rows.


I have tested the same thing in Oracle, SQL server and this built-in database with ColdFusion Admin application, all these databases returns 1 row when we evaluate COUNT(*) and the value of that field varies as per the total no records. i,e - it may be 0 or more than that.

But, if we consider the same situation by ColdFusion Query of Query then it returns 0 record for record count field if there is no matching record and returns record count if it is more than 0.

Conclusion:
Here I'm not trying to explain if it is an issue with QoQ. And I'm not sure if it would be correct to compare ColdFusion Query of Query with a database but we should remember this thing as in most of the time while evaluating COUNT(*) we use the field name and if we do the same for Query of Query then there might be situation where your code will fail.

Happy Coding! :)

ColdFusion Query Of Queries and local scope

Few days ago I was working with  QueryOfQuery and I faced one situation where I got stucked for few hour . After few Googling I found the silly mistake I was making.

I am just trying to generate the same situation with some sample code below.

<cfset local.qryGetArtists = queryNew("") />
<cfset local.qryGetSelctedArtist = queryNew("") />

<cfquery name="local.qryGetArtists" datasource="cfartgallery">
    SELECT ARTISTID, FIRSTNAME, LASTNAME, EMAIL, PHONE FROM ARTISTS
</cfquery>
<cfdump var="#local.qryGetArtists#">

<cfquery name="local.qryGetSelctedArtist" dbtype="query">
    SELECT * FROM local.qryGetArtists WHERE LOWER(LASTNAME) LIKE '%#lCase("Buntel")#%'
</cfquery>
<cfdump var="#local.qryGetSelctedArtist#">

In this code, the first dump give the query Object which contains the list of artist details and after that I am just filtering that query object using Query Of Queries(QoQ) and dumping that result.

For the second dump, I got one big error message:

"Query Of Queries syntax error. Encountered "local"

Where and Why We Got This Error?
The error is due to the local scope as per the error message then why this error. In ColdFusion there are some reserved keywords are there and we can't use that reserve keyword  inside Query Of Queries directly.

This is also mentioned in the ColdFusion Documentation here.

How To Solve This Issue?

The solution is escape the reserve keyword like this:

"SELECT * FROM [local].qryGetArtists WHERE LOWER(LASTNAME) LIKE '%#lCase("Buntel")#%'"


So, the final Code for the Query Of Queries will be like below.

<cfquery name="local.qryGetSelctedArtist" dbtype="query">
    SELECT * FROM [local].qryGetArtists WHERE LOWER(LASTNAME) LIKE '%#lCase("Buntel")#%'
</cfquery>
<cfdump var="#local.qryGetSelctedArtist#">

For more details about Query Of Queries go to the ColdFusion live document. (ColdFusion Query Of Queries)

Case In-Sensitive String matching in Query Of Query(QoQ ColdFusion - 9)

We know that for string matching in database we use "LIKE" Operator. We can also do the string matching operation on a query object by Query Of Query(QoQ) in ColdFusion like the following way.


/*In this example I am calling a stored procedure myProc and passing the argument as ntMLSID then it returns all the field details . In the next query I am filtering the result by matching the string to a returned field "vcFieldName"*/

<cfstoredproc datasource="#application.dsn#" procedure="myProc">
  <cfprocresult name="fieldDetails" />
  <cfprocparam type="in" cfsqltype="CF_SQL_INTEGER" value="#variables.ntMLSID#" />

</cfstoredproc>

<cfquery dbtype="query" name="filterResult">
    SELECT *
    FROM
         fieldDetails
    WHERE
        vcFieldName LIKE '%#variables.vcSearchedField#%'

</cfquery>

Here in the above query everything is seems good. But the issue is that the above set of code only can able to match(comparison) a case sensitive string .

e.g- If vcFieldName in the Query is "India" and we pass the search string as "ind" then it will not return any value. By default the string matching by the LIKE operator of QoQ is case sensitive. 

So, how we will make it as case insensitive ???

We can do that by following ways:

<cfquery dbtype="query" name="filterResult">
    SELECT *
    FROM
         fieldDetails
    WHERE
        LOWER(vcFieldName) LIKE '%#LCASE(variables.vcSearchedField)#%'

</cfquery>

In first case we use LOWER and LCASE functions . Similarly we can also use UPPER and UCASE to  do  the same as follow:

<cfquery dbtype="query" name="filterResult">
    SELECT *
    FROM
         fieldDetails
    WHERE
        UPPER(vcFieldName) LIKE '%#UCASE(variables.vcSearchedField)#%'

</cfquery>