Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Having issues querying a query

Posted on 2004-11-24
11
Medium Priority
?
225 Views
Last Modified: 2013-12-24
Cold Fusion 5

OK, I'm creating a report with a few different sections which require data to be compiled differently.  I have one main query that retrieves data using a stored procedure, then a few queries of that query to order the data as needed.  For some reason I'm having issues with adding WHERE clauses or ORDER BY clauses to a couple of the sub-queries.

Main Query:
      <cfquery name="qryMainSource" datasource="theDB">
            EXECUTE
                  sm_spGetDASSImpressions
                        @in__dt_StartDate      = '#variables.StartDate#',
                        @in__dt_EndDate            = '#variables.EndDate#',
                        @in__int_BrandID      = #variables.BrandID#,
                        @in__int_ClusterID  = #variables.ClusterID#
      </cfquery>


Sub Queries I'm having Issues With:

      <cfquery name="qrySubCounts" DBTYPE="query" MAXROWS=10>
            SELECT CustomerClassificationName, Count(*) as theCount, Sum(TotalWebSite) as theSum
            FROM qryMainSource
            WHERE TotalWebSite > -1
            GROUP BY CustomerClassificationName
            ORDER BY theSum DESC
      </cfquery>
First issue I'm having is that the WHERE clause is causes the error: "Invalid SQL" to be thrown.  I thought maybe this has something to do with the aggregate function being used here so I created an addition sub query to work around this.

Additional Sub Query:
      <cfquery name="qrySubCountsRoot" DBTYPE="query">
            SELECT CustomerClassificationName, TotalWebSite
            FROM qryDASS_Source
            WHERE TotalWebSite > -1
      </cfquery>

Then modified the first sub-query to use this as the base data:
      <cfquery name="qrySubCounts" DBTYPE="query" MAXROWS=10>
            SELECT CustomerClassificationName, Count(*) as theCount, Sum(TotalWebSite) as theSum
            FROM qrySubCountsRoot
            GROUP BY CustomerClassificationName
            ORDER BY theSum
      </cfquery>

Now this throws the error:
THESUM is not a column that can be sorted on.

Now I know that TotalWebSite can be summed and ordered by because I do it with some of my other sub-queries that do not require a where clause (where totalwebsite > -1).  What the heck is going on here????
0
Comment
Question by:scottd78
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 21

Assisted Solution

by:pinaldave
pinaldave earned 200 total points
ID: 12668427
Hi scottd78,
   SELECT CustomerClassificationName, Count(*) as theCount, Sum(TotalWebSite)
 as theSum
           FROM qryMainSource
           GROUP BY CustomerClassificationName
           ORDER BY theSum DESC
           HAVING TotalWebSite > -1

try this.



Regards,
---Pinal
0
 
LVL 5

Assisted Solution

by:kkhipple
kkhipple earned 200 total points
ID: 12668445
    <cfquery name="qrySubCounts" DBTYPE="query" MAXROWS=10>
          SELECT CustomerClassificationName, Count(*) as theCount, Sum(TotalWebSite) as theSum
          FROM qryMainSource
          WHERE TotalWebSite > -1
          GROUP BY CustomerClassificationName
          ORDER BY theSum DESC
     </cfquery>


aggregate functions should always be listed first.. try this instead

     <cfquery name="qrySubCounts" DBTYPE="query" MAXROWS=10>
          SELECT Count(*) as theCount, Sum(TotalWebSite) as theSum, CustomerClassificationName
          FROM qryMainSource
          WHERE TotalWebSite > -1
          GROUP BY CustomerClassificationName
          ORDER BY theSum DESC
     </cfquery>



    <cfquery name="qrySubCountsRoot" DBTYPE="query">
          SELECT CustomerClassificationName, TotalWebSite
          FROM qryDASS_Source
          WHERE TotalWebSite > -1
     </cfquery>

is TotalWebSite a varchar or int datatype?   if its a varchar datatype then try this

    <cfquery name="qrySubCountsRoot" DBTYPE="query">
          SELECT CustomerClassificationName, TotalWebSite
          FROM qryDASS_Source
          WHERE TotalWebSite > '-1'
     </cfquery>




why you doing a query on a query if you have WHERE TotalWebSite > -1 specified in both
0
 
LVL 5

Expert Comment

by:kkhipple
ID: 12668457
wouldn't it be:

   SELECT CustomerClassificationName, Count(*) as theCount, Sum(TotalWebSite) as theSum
           FROM qryMainSource
           GROUP BY CustomerClassificationName
           ORDER BY theSum DESC
           HAVING thesum > -1
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 21

Expert Comment

by:pinaldave
ID: 12668473
Hi kkhipple,
i think that is not valid. you can do having on original column not the created one. I may be wrong though. I still think, my query is correct.

Regards,
---Pinal
0
 

Author Comment

by:scottd78
ID: 12668550
Wow, thanks for all the quick responses.  I'll address them in order.

1)  CF appears to ignore the HAVING clause listed after the ORDER BY clause, but no longer causes an error.  It's also not eliminating the records that aren't > -1.
2)  Reordering the select list did not help.
3)  TotalWebSite is of type integer.
4)  Their are other sub-queries that do not require the TotalWebSite to be > -1.  This particluar data set should not include those records.
5)  I'm not interested in putting a clause on the Sum of TotalWebSite rather eliminating the records which aren't > -1 before summing the records.

Thanks again!
0
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 200 total points
ID: 12669790
HAVING should always come before an ORDER BY so if that solution will work it would be:


 SELECT CustomerClassificationName, Count(*) as theCount, Sum(TotalWebSite) as theSum
          FROM qryMainSource
          GROUP BY CustomerClassificationName
          HAVING TotalWebSite > -1
          ORDER BY theSum DESC
0
 
LVL 2

Assisted Solution

by:aftershox
aftershox earned 200 total points
ID: 12669823
This is a wild one... but I've had problems when using QOQ SQL functions when one of the columns contained a null value. Try cfdumping your query structures just to make sure the data is what you expected. To get around that the original query needs to convert nulls to a space (or perhaps a number in your case).
0
 
LVL 9

Accepted Solution

by:
Jerry_Pang earned 200 total points
ID: 12678467

Hi,

what database are you using?
what do you mean subqueries? do you have a main query?
-see, you have the storedprocedure

>>then a few queries of that query to order the data as needed.
how do you do this?

is this a standalone query that does not get any values from the main query?
i tried running you sql statement on MsSQL server, and it is running ok.

need more info, i guess
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 12933968
split between all the experts as I think everybody has contributed to reach to the answer.
Though, asker has not reponded I believe the answer is correct.
0
 

Author Comment

by:scottd78
ID: 12934578
never found a solution.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question