Should be double quotes I think
Select SUM(1990)
Some DBs use brackets too I think
sum([1990])
Not sure how it would work undr the hood with query of query, can't you
pull this from the db directly?
On Wed, 2010-06-09 at 11:27 -0400, fun and learning wrote:
HI All -
I have a query which
SUM([1990])
Just use square brackets around column name.
On Wed, Jun 9, 2010 at 11:27 AM, fun and learning
funandlrnn...@gmail.comwrote:
HI All -
I have a query which returns the result set like below. The column names
are like 1990, 1991, 1992 and there are certain amount for each year
Your first issue is that that is not a good db practice naming columns as
numbers. If you put a prefix of y in front of the column name in your
table (or whatever you chose, i.e y1990, y1991, y1992) it wouldn't have this
issue, it would force your SQL to actual look for a table column rather
Your first issue is that that is not a good db practice naming columns as
numbers. If you put a prefix of y in front of the column name in your
table (or whatever you chose, i.e y1990, y1991, y1992) it wouldn't have this
issue, it would force your SQL to actual look for a table column rather
Ah, right QoQ can be bitchy. Can't you alias the column names in the initial
query?
SELECT my1990 = [1990], my1991 = [1991]
or
SELECT [1990] as my1990 etc etc
That way can reference them without any issues.
P.S. 1990 and the like are absolutely horrible column names.
On Wed, Jun 9, 2010 at
If you can't rename the columns as someone else suggested, you can
change your select to
select 1990 as y1990, 1991 as y1991, etc..
then in your query of querys you do
select sum(y1990) as sum1990
On Wed, Jun 9, 2010 at 8:27 AM, fun and learning
funandlrnn...@gmail.com wrote:
HI All -
I
Ah, right QoQ can be bitchy. Can't you alias the column names in the initial
query?
SELECT my1990 = [1990], my1991 = [1991]
or
SELECT [1990] as my1990 etc etc
That way can reference them without any issues.
P.S. 1990 and the like are absolutely horrible column names.
On Wed, Jun 9, 2010 at
I have a query which returns the result set like below. The column names are
like 1990, 1991, 1992 and there are certain amount
for each year
1990 1991 1992
1000 5000 6000
2000 2000 3000
This doesn't address your question directly, but again this is a very
poor database design. I
I would cache your initial result, and build a set of filters that are
applied to it each time the user updates their filter. So, you would
have cachedQuery as your initial base search, then filteredQuery that
you would not cache but would always be the result of a QoQ of your
cached query with
This is just a guess, but you can try putting either a \ or a '
before the characters that needs to be escaped??
LIKE 'foo\%bar'
Or maybe
LIKE 'foo'%bar'
The single quote is only a guess because single quote escaped another
single quote.
..
Ben Nadel
Certified Advanced
URLs and they don't contain tab characters.
Hope this helps someone else who runs into the same thing!
Dave
-Original Message-
From: Ben Nadel [mailto:[EMAIL PROTECTED]
Sent: Monday, February 26, 2007 10:36 AM
To: CF-Talk
Subject: RE: Query of Queries Question
This is just a guess
Does the result /have/ to be a query? Could you make a structure
instead, using your cfoutput method? That should be pretty quick.
On 1/5/07, Dave Phillips [EMAIL PROTECTED] wrote:
Does anyone know if it's possible to extract a 'subset' of records in a query
utilizing Query of Queries?
not sure what DB you're using.. but with Oracle you can do this in a subquery
select * from
(
select rownum as rn, n.*
From navmenu n
)
where rn = 5 and rn = 7
On 1/5/07, Dave Phillips [EMAIL PROTECTED] wrote:
Does anyone know if it's possible to extract a 'subset' of records in a query
The query is not coming from a DB. It is actually coming from a CFSEARCH tag,
so unfortunately, I can't use this method.
not sure what DB you're using.. but with Oracle you can do this in a subquery
select * from
(
select rownum as rn, n.*
From navmenu n
)
where rn = 5 and rn = 7
On 1/5/07,
You don't necessarily have to create a query from scratch. Instead, if there
is a column (with a simple datatype) that uniquely identifies each row, this
can be done with a list and a query of queries.
If qMyQuery.id is such a column, then you can get those all into a list:
cfset list_id = ''
Subject: Re: Query of Queries question
You don't necessarily have to create a query from scratch. Instead, if
there is a column (with a simple datatype) that uniquely identifies each
row, this can be done with a list and a query of queries.
If qMyQuery.id is such a column, then you can get those
You could also add a RowNumber column to your query:
cfset QueryAddColumn(qSomeQuery, RowNumber)
cfloop from=1 to=qSomeQuery.RecordCount index=RowNumber
cfset QuerySetCell(qSomeQuery, RowNumber, RowNumber, RowNumber)
/cfloop
Then, do:
cfquery name=qMyQuery dbtype=query
SELECT *
FROM
I don't think you can really measure it... It depends not necessarily on how
many records you select, but the amount of data you are using, and how much
RAM you have.
I did a Query of a Query on a 20mb table(about 140,000 records) and CF
grabbed 20 mb of RAM for a sec, and then free'd it up.
18 matches
Mail list logo
