Hi Scott:
Couldn't you use a TRIM function in the original query to eliminate the
spaces?
In SQL Server, it would actually be something like: SELECT
RTRIM(LTRIM(col_with_spaces)) FROM TABLE.
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
If I may offer some CONSTRUCTIVE answers like John,
Query of Queries are very limited in their abilities
compared to what modern databases will do. I believe
they are even behind the basic mySQL so I would
recommend using stored procedures or functions instead
of QoQ. In addition, I would
How would Trim() remove an embedded space?
- Original Message
From: McTure, Greg [EMAIL PROTECTED]
To: discussion@acfug.org
Sent: Friday, April 18, 2008 8:54:51 AM
Subject: RE: [ACFUG Discuss] QoQ question
!--a:link
{}
span.MSOHYPERLINK
{}
a:visited
{}
Also, Trim() will operate on the content of the column, not the column name
itself.
-Cameron
2008/4/18 shawn gorrell [EMAIL PROTECTED]:
How would Trim() remove an embedded space?
--
Cameron Childress
Sumo Consulting Inc
http://www.sumoc.com
---
cell: 678.637.5072
aim: cameroncf
email:
I think there's misunderstanding.
The column names are what had the spaces in them. The guy who said
SELECT [column name] as ColumnName
probably has the best solution. Trim function likely won't help in this
situation at all.
Allen
From: [EMAIL PROTECTED]
To be fair, QoQ solves an entirely different problem than Stored Procs. QoQ
was also not designed to be an entire DB server, and hopefully never will
be.
QoQ is quirky as hell, yes, but for the newbies out there on the list it
would be wrong (IMHO) to make a blanket recommendation about using
I actually didn't catch the embedded spaces but if you really wanted to
removed embedded spaces you could use a REPLACE substring function in the query
to do that.
In SQL Server it would be something like this:
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
Also, if it is the column name in question, I also agree that it would
be easier to just SELECT column AS 'DESIRED COLUMN NAME'
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of McTure, Greg
Sent: Friday, April 18, 2008 8:55 AM
To:
Hey Scott long time man. Hope you're well.
Hey this is just a thought. I really don't have much experience doing QoQ
but couldn't you do something like creating a column alias in one of those
queries so it doesn't have a space in it in the query itself? Again I may be
way off but just thought I
I just wanted to say thanks to everyone who offered advice and suggestions
related to this topic of doing CF on MAC. I am making plans now to purchase
a MacBook Pro and also decided to purchase the lease on the dell gear I have
which also included licensed versions of the CS2 web bundle double
Thanks to all for comments. I should have been more detailed. Yes, spaces
in field names is very bad. I am working with a process that uploads an
Excel Spreadsheet that is created by a client. Yes, I could go back to them
and tell them no spaces, but I wanted to try to find a solution that
Scott,
Have you thought about using the POI stuff to read the Excel sheet? There's
a lot of info on a CFC implementation at
http://www.bennadel.com/blog/474-ColdFusion-Component-Wrapper-For-POI-To-Read-And-Write-Excel-Files.htm.
True, that info then comes in as a struct but you could loop over
Shot in the dark...
Have you tried putting quotes around the column name?
SELECT column name, column 2 from myQuery
?
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Councill
Sent: Friday, April 18, 2008 2:25 PM
To: discussion@acfug.org
ok, not sure if this is what you need, but can you use the Replace()
function?
On Fri, Apr 18, 2008 at 2:52 PM, Howard Fore [EMAIL PROTECTED] wrote:
Scott,
Have you thought about using the POI stuff to read the Excel sheet?
There's a lot of info on a CFC implementation at
Hey,
When you say field name in your email, are you referring to the column? Or
is it some calculated, not really a column value in the spreadsheet?
And secondly, what are you using to create the JDBC connection. I ask not
out of concern that it is the flaw in your process, but out of morbid
Scott,
One last suggestion - why not save the data into a new query object with the
fixed column names and then use the new query:
cfquery name=qryTest datasource=mydsn
select top 10
[first name],
[last name]
from some_table
/cfquery
cfset newQuery=QueryNew(Replace(qryTest.columnList, , _,
General defense of QofQ:
Keep in mind the main strength it has - which is it resides in memory. So
you can pull the main data with one SQL statement and then play with it all
you want using QofQ's which saves trips to the database and is MUCH faster
when dealing with large numbers of records.
Just to add to the tips out there. Yes dumping a set of records into a QoQ
is great but it will decline with larger and larger datasets. The JVM heap
will only take so much.
If you're using SQL Server or Oracle and have thousands of records or more.
The one thing I see people forget or simply
18 matches
Mail list logo
