Query of Queries problems

[Liam O'Brien]

I have revised my queries from before.
The goal is still the same, to count up all instances of the same zip codes.  The problem is the zip codes are either stored in the 9 digit format, or in the 5 digit format.  I try to account for this by cutting all zip codes to 5 digits, and then counting them up, but with not luck.
Here is the information:

ZIP is stored as an nvarchar

<cfquery name="qGetInfo" datasource="PURL">
SELECT * FROM table
</cfquery>

<cfquery name="qGetDay" dbtype="query">
SELECT ZIP, LEFT(ZIP, 5) AS shortZIP, COUNT(LEFT(ZIP,5)) AS ZIPCount
FROM qGetInfo
GROUP BY shortZIP
</cfquery>

I realize that the second query is now exclusively being run using coldfusion, and I found a livedoc site (http://livedocs.adobe.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm)
for coldfusion mx that lists the LEFT(), COUNT() functions and the AS keyword for aliasing as valid, except I am using coldfusion 8.  I did not think that it would make a difference, but I get the following error:
Encountered "LEFT.
Incorrect Select List,
Incorrect select column,

Any help would be fantastic.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3088
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Brad Comer]

I have been working with CF since 1994 and I must say that QoQ has always
had issues at some level. You should really try to accomplish this using
TSQL against your database directly instead of trying to do it with CF's
QoQ. Just too many idiosyncrasies.

2 cents...

-----Original Message-----
From: Liam O'Brien [mailto:the.lob@...]
Sent: Tuesday, June 10, 2008 2:56 PM
To: SQL
Subject: Query of Queries problems

I have revised my queries from before.
The goal is still the same, to count up all instances of the same zip codes.
The problem is the zip codes are either stored in the 9 digit format, or in
the 5 digit format.  I try to account for this by cutting all zip codes to 5
digits, and then counting them up, but with not luck.
Here is the information:

ZIP is stored as an nvarchar

<cfquery name="qGetInfo" datasource="PURL"> SELECT * FROM table </cfquery>

<cfquery name="qGetDay" dbtype="query">
SELECT ZIP, LEFT(ZIP, 5) AS shortZIP, COUNT(LEFT(ZIP,5)) AS ZIPCount FROM
qGetInfo GROUP BY shortZIP </cfquery>

I realize that the second query is now exclusively being run using
coldfusion, and I found a livedoc site
(http://livedocs.adobe.com/coldfusion/6/Developing_ColdFusion_MX_Application
s_with_CFML/using_recordsets4.htm)
for coldfusion mx that lists the LEFT(), COUNT() functions and the AS
keyword for aliasing as valid, except I am using coldfusion 8.  I did not
think that it would make a difference, but I get the following error:
Encountered "LEFT.
Incorrect Select List,
Incorrect select column,

Any help would be fantastic.




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3089
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Ian Skinner-3]

Liam O'Brien wrote:
> Any help would be fantastic.
>  

Does this have to be done with a query of query?  Can you do this
against the original database to have all it's capability?

Secondly, I'm not sure, but I have a distinct memory where one can not
use an alias in either the group by clause or maybe it was the order by
clause.  But I remember doing something somewhat like this and having to
use the full function in this part of the SQL.

I.E. something like

GROUP BY LEFT(zip,5)





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3090
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Peter Boughton]

> The goal is still the same, to count up all instances of the same zip codes.  The problem is the zip codes are either stored in the 9 digit format, or in the 5 digit format.  I try to account for this by cutting all zip codes to 5 digits, and then counting them up, but with not luck.

Can you update the database to store the first 5 and last 4 digits separately?

Then you can simply group on the first 5 for this and similar things,
and concatenate for places when you need the whole thing?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3091
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Adrian Moreno]

Skip the SELECT *. Right now you're doing all this string manipulation and grouping in CF and AFTER you've gotten the entire record set. You could do all of that directly in the database and only have the smaller number of records returned that contain the unique list of zip codes and their counts.

Wouldn't this do what you need directly in SQL?

SELECT LEFT( ZIP, 5 ), COUNT( LEFT( ZIP, 5 ) ) AS theCount

FROM table

-- Adrian

----------------------------------------
From: Liam O'Brien <the.lob@...>
Sent: Tuesday, June 10, 2008 3:57 PM
To: SQL <sql@...>
Subject: Query of Queries problems

I have revised my queries from before.
The goal is still the same, to count up all instances of the same zip codes.  The problem is the zip codes are either stored in the 9 digit format, or in the 5 digit format.  I try to account for this by cutting all zip codes to 5 digits, and then counting them up, but with not luck.
Here is the information:

ZIP is stored as an nvarchar

SELECT * FROM table

SELECT ZIP, LEFT(ZIP, 5) AS shortZIP, COUNT(LEFT(ZIP,5)) AS ZIPCount
FROM qGetInfo
GROUP BY shortZIP

I realize that the second query is now exclusively being run using coldfusion, and I found a livedoc site (http://livedocs.adobe.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets4.htm)
for coldfusion mx that lists the LEFT(), COUNT() functions and the AS keyword for aliasing as valid, except I am using coldfusion 8.  I did not think that it would make a difference, but I get the following error:
Encountered "LEFT.
Incorrect Select List,
Incorrect select column,

Any help would be fantastic.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3092
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Liam O'Brien]

Thanks, that GROUP BY LEFT(ZIP,5) was exactly what I needed.  It works exactly as I want it to now.

Thanks to everyone else who posted as well.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3094
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Liam O'Brien]

The recommendation for using GROUP BY LEFT(ZIP, 5) was exactly what I needed.
I also decided to just put it all in one SQL query. The reasoning behind using a query of a query is to reduce calls to the SQL server.

It all works exactly as I wanted it to now.

Thanks Everyone!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3093
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6