This may not be the right place to post this (man, CF-TALK has changed a lot
in the last 5 or so years ;)).
I have a simple SQL query that is showing up as running slow. When I run it
via the Management Studio it is sometimes fast 0.1 seconds and sometimes,
seemingly randomly slow 1.5 minutes
-
From: Brook Davies [mailto:cft...@logiforms.com]
Sent: Thursday, December 05, 2013 11:26 AM
To: cf-talk
Subject: Simple SQL Query sometimes really Slow?
This may not be the right place to post this (man, CF-TALK has changed a lot
in the last 5 or so years ;)).
I have a simple SQL query
are happening:
http://msdn.microsoft.com/en-us/library/tcbchxcb%28VS.80%29.aspx
HTH,
Jon
On Dec 5, 2013, at 12:26 PM, Brook Davies cft...@logiforms.com wrote:
This may not be the right place to post this (man, CF-TALK has changed a lot
in the last 5 or so years ;)).
I have a simple SQL
-to-force-ind
ex-query-hints-index-hint-part2/
Hope this helps,
-- Jeff
Original Message
From: Brook Davies cft...@logiforms.com
Sent: Thursday, December 05, 2013 10:27 AM
To: cf-talk cf-talk@houseoffusion.com
Subject: Simple SQL Query sometimes really Slow?
This may
[mailto:cft...@logiforms.com]
Sent: Thursday, December 05, 2013 11:26 AM
To: cf-talk
Subject: Simple SQL Query sometimes really Slow?
This may not be the right place to post this (man, CF-TALK has changed a
lot
in the last 5 or so years ;)).
I have a simple SQL query that is showing up
:
This may not be the right place to post this (man, CF-TALK has changed a
lot
in the last 5 or so years ;)).
I have a simple SQL query that is showing up as running slow. When I run it
via the Management Studio it is sometimes fast 0.1 seconds and sometimes,
seemingly randomly slow 1.5 minutes
, I'll try them!
Whohoo! Cftalk is alive!!
Brook
-Original Message-
From: Byron Mann [mailto:byronos...@gmail.com]
Sent: December-05-13 10:22 AM
To: cf-talk
Subject: Re: Simple SQL Query sometimes really Slow?
Could never figure this out, but we had a similar issue on 2005 with a date
Hi all,
Here is a little background... CF: ColdFusion Server Enterprise 8,0,1,195765
SQL: Microsoft SQL Server 2008 R2
I have a stored procedure that when I run from SSMS it runs perfectly and
returns 2000 rows. When I run it from a cfstoredproc tag it returns no rows at
all. I just changed
Is it returning multiple recordsets perhaps? Or possibly returning a
cursor to the recordset?
Judah
On Wed, Nov 30, 2011 at 11:18 AM, DURETTE, STEVEN J sd1...@att.com wrote:
Hi all,
Here is a little background... CF: ColdFusion Server Enterprise 8,0,1,195765
SQL: Microsoft SQL Server 2008
: Wednesday, November 30, 2011 2:28 PM
To: cf-talk
Subject: Re: Weird SQL Query happenings...
Is it returning multiple recordsets perhaps? Or possibly returning a
cursor to the recordset?
Judah
~|
Order the Adobe Coldfusion Anthology
Anything unusual about the procedure like optional parameters?
(I know you said the input values are exactly the same, but ...) did you
compare the debug information from cfquery and cfstoredproc? Just to verify
everything truly is the same (values, data types, parameter position).
Another
don't return the same record set.
Hm.. on second thought I may have read that wrong. If the resultset you are
getting back contains the correct column names just no records - then ignore my
comment about NOCOUNT. It does not apply.
-Leigh
I checked, everything is exactly the same for both calls. Yes we do set NOCOUNT
ON.
Yes it returns the proper columns just no data.
Steve
-Original Message-
From: Leigh [mailto:cfsearch...@yahoo.com]
Sent: Wednesday, November 30, 2011 3:51 PM
To: cf-talk
Subject: Re: Weird SQL Query
Other than that the only thing I can think of would be tracing it or using the
profiler to see what is happening.at a db level. Because it does not make sense
the same parameters would return different results.
-Leigh
~|
If you run the profiler and it says that it is returning the correct
number of records in the sp that was run, you could also take a look
at FusionReactor and use their JDBC wrappers to see if something is
going on at the JDBC level.
Judah
On Wed, Nov 30, 2011 at 1:33 PM, Leigh
my memory is a little hazy on the subject, but I do recall some odd
issues like this with cfstoredproc.
Are you using cfstoredprocparam ?
if so, make sure you pass the params in in the exact same order as
they are defined in the stored proc itself, if I recall this is one of
the causes of such
On 12/1/2011 2:18 AM, DURETTE, STEVEN J wrote:
I have a stored procedure that when I run from SSMS it runs perfectly and
returns 2000 rows. When I run it from a cfstoredproc tag it returns no rows
seen something somewhat similar w/sql server 2008 datadirect drivers but this
was always w/sp
Two tables each containing a shared primary key ID.
I am trying to create a query that lists records from table B that are not
in table A.
Many thanks,
Jenny
~|
Order the Adobe Coldfusion Anthology now!
select * from b where id not in (select id from a)
On Tue, Jun 21, 2011 at 12:37 PM, Jenny Gavin-Wear
jenn...@fasttrackonline.co.uk wrote:
Two tables each containing a shared primary key ID.
I am trying to create a query that lists records from table B that are not
in table A.
Many
if your tables are large, you'll probably see a better performance from
select id from TableA a
where not exists
(select 1 from TableB b
where a.id = b.id)
On Tue, Jun 21, 2011 at 12:41 PM, John M Bliss bliss.j...@gmail.com wrote:
select * from b where id not in (select id from a)
On Tue,
Thanks John and Greg :)
-Original Message-
From: Greg Morphis [mailto:gmorp...@gmail.com]
Sent: 21 June 2011 18:45
To: cf-talk
Subject: Re: SQL Query Problem
if your tables are large, you'll probably see a better performance from
select id from TableA a
where not exists
(select 1
How about:
select b.*
from b
left outer join a on b.id = a.id
where a.id is null
Carl
On 6/21/2011 10:37 AM, Jenny Gavin-Wear wrote:
Two tables each containing a shared primary key ID.
I am trying to create a query that lists records from table B that are not
in table A.
Many thanks,
I would do it this way :
select b.*
from b
where b.id not in (select a.id from a)
How about:
select b.*
from b
left outer join a on b.id = a.id
where a.id is null
Carl
On 6/21/2011 10:37 AM, Jenny Gavin-Wear wrote:
~|
That looks familiar! :-)
On Tue, Jun 21, 2011 at 1:09 PM, Stephane Vantroyen s...@emakina.com wrote:
I would do it this way :
select b.*
from b
where b.id not in (select a.id from a)
How about:
select b.*
from b
left outer join a on b.id = a.id
where a.id is null
Carl
On
Looks like I went with the vote, lol
Many thanks for all replies, and fast too :)
Some payments from Paypal transactions, some manually entered on profiles.
Legacy code :/
Jenny
select * from tbl_members
where
(datepart(m,paid) = #session.month# and datepart(,paid) = #session.year#
AND
Off topic, but the Select * made me shudder.
On Tue, Jun 21, 2011 at 2:25 PM, Jenny Gavin-Wear
jenn...@fasttrackonline.co.uk wrote:
Looks like I went with the vote, lol
Many thanks for all replies, and fast too :)
Some payments from Paypal transactions, some manually entered on
+420
On Tue, Jun 21, 2011 at 2:45 PM, Michael Grant mgr...@modus.bz wrote:
Off topic, but the Select * made me shudder.
On Tue, Jun 21, 2011 at 2:25 PM, Jenny Gavin-Wear
jenn...@fasttrackonline.co.uk wrote:
Looks like I went with the vote, lol
Many thanks for all replies, and fast
She didn't provide column names...
On Tue, Jun 21, 2011 at 1:45 PM, Michael Grant mgr...@modus.bz wrote:
Off topic, but the Select * made me shudder.
On Tue, Jun 21, 2011 at 2:25 PM, Jenny Gavin-Wear
jenn...@fasttrackonline.co.uk wrote:
Looks like I went with the vote, lol
Many
I was waiting for a comment on that.
It's a very small table :)
-Original Message-
From: Michael Grant [mailto:mgr...@modus.bz]
Sent: 21 June 2011 19:46
To: cf-talk
Subject: Re: SQL Query Problem
Off topic, but the Select * made me shudder
, Jenny Gavin-Wear
jenn...@fasttrackonline.co.uk wrote:
I was waiting for a comment on that.
It's a very small table :)
-Original Message-
From: Michael Grant [mailto:mgr...@modus.bz]
Sent: 21 June 2011 19:46
To: cf-talk
Subject: Re: SQL Query Problem
Off topic, but the Select
Hi Michael,
The (very old) web site is about to be completely redeveloped, so I'm really
not too worried.
Appreciate your concern though :)
Jenny
-Original Message-
From: Michael Grant [mailto:mgr...@modus.bz]
Sent: 21 June 2011 23:27
To: cf-talk
Subject: Re: SQL Query Problem
: Michael Grant [mailto:mgr...@modus.bz]
Sent: 21 June 2011 23:27
To: cf-talk
Subject: Re: SQL Query Problem
Right, but if that table grows and columns are added, the overhead on
your
query will grow. Having a small table makes it even easier to define the
columns in your select list
With ColdFusion 9, we ended up replacing all the wildcard selects with actual
column names, among other fortifications. We did not experience this issue on
6.1.
~|
Order the Adobe Coldfusion Anthology now!
Since everyone should be in a SQL Join state of mind...here's another one.
I have a weird issue that I haven't been able to narrow down yet. I'm trying to
add a new field to one of my tables to store some additional information, but
once I add the column, my previously working query breaks.
This might be an issue I've had to deal with before.
Do something to change the fingerprint (no idea what the correct term
would be) of the query - or restart cf. By change the fingerprint, it
could be something as simple ad swapping p.* and pt.* (swapping as
in their ordinal position in the
On 2/1/2011 1:23 PM, Debbie Morris wrote:
What am I overlooking?
The evil of using * in SELECT clauses.
When that is done, database drivers are know to cache the columns and
datatypes of the SQL queries. Then somebody comes along and changes the
database structure, like you adding a field.
On 2/1/2011 2:21 PM, Charlie Stell wrote:
I assume this is something on CF's side -
as restarting the CF service also fixes it.
Not ColdFusion itself, but the database drivers used by ColdFusion and
the cached (pooled) data source settings.
Changing the Datasource to not used pooled settings
The evil of using * in SELECT clauses.
I'm with Ian on this 100%. Often times developers think that using * will be
faster, and easier and allow more flexibility. However that couldn't be
further from the truth as you are seeing now. Take Ian's advice and define
each column you want from your
, so I'll tackle this
again in the morning. Thanks for the help!
Debbie
-Original Message-
From: Michael Grant [mailto:mgr...@modus.bz]
Sent: Tuesday, February 01, 2011 5:41 PM
To: cf-talk
Subject: Re: SQL query question
The evil of using * in SELECT clauses.
I'm with Ian on this 100
Hello,
I was wondering if there were any good sql query builders that I can add to my
application. I found a jquery plugin called Sqlquerybuilder
(http://plugins.jquery.com/project/SQL_QUERY_BUILDER). It's almost what I need
but the saved queries will break if the schema changes since all
Hello,
I was wondering if there were any good sql query builders that I can add to my
application. I found a jquery plugin called Sqlquerybuilder
(http://plugins.jquery.com/project/SQL_QUERY_BUILDER). It's almost what I need
but the saved queries will break if the schema changes since all
Hello,
I was wondering if there were any good sql query builders that I can add to my
application. I found a jquery plugin called Sqlquerybuilder
(http://plugins.jquery.com/project/SQL_QUERY_BUILDER). It's almost what I need
but the saved queries will break if the schema changes since all
Hello,
I was wondering if there were any good sql query builders that I can add to my
application. I found a jquery plugin called Sqlquerybuilder
(http://plugins.jquery.com/project/SQL_QUERY_BUILDER). It's almost what I need
but the saved queries will break if the schema changes since all
Hello,
I was wondering if there were any good sql query builders that I can add to my
application. I found a jquery plugin called Sqlquerybuilder
(http://plugins.jquery.com/project/SQL_QUERY_BUILDER). It's almost what I need
but the saved queries will break if the schema changes since all
My apologies for posting an OT message to a ColdFusion list. RDMS are so
closely tied to our day-to-day job in writing CF, that perhaps the list will be
tolerant of such a question.
I'm writing a View for our CRM package to use. The SQL works as I've written
it, but Management Studio keeps
Hi Jason,
I would recommended creating the view from a query window, and not the
design view that SQL Management studio uses
Simply add a 'Create View dbo. AS' before the statement that works and
run it in the query window
Hope this helps
-Dave
On Tue, Dec 22, 2009 at 11:34 AM, Jason
Subject: Re: (ot) SQL query aliases in SQL Management Studio
Hi Jason,
I would recommended creating the view from a query window, and not the
design view that SQL Management studio uses
Simply add a 'Create View dbo. AS' before the statement that works and
run it in the query window
Hope
I would recommended creating the view from a query window,
and not the
design view that SQL Management studio uses
+1 . Traditionally design views and wizards do unspeakable things to your
nicely formatted sql.
-Leigh
It looks like the reason for the original rewrite was due to subquerying
the same table twice. Nothing wrong with that, but the wizard decides to
ensure that every reference is unique, thus the aliasing of the second
reference. You could get around that by adding your own table aliases in
and email columns populated, while new comments
have the userid populated instead.
users
userid int
username varchar
email varchar
...
comments
commentid int
userid int
name varchar
email varchar
commentvarchar
...
Is there a single SQL query
Can you update the comments table when a new registration happens, with the
userid that matches the email address?
William
-Original Message-
From: Jim McAtee [mailto:jmca...@mediaodyssey.com]
Sent: Tuesday, February 24, 2009 6:26 PM
To: cf-talk
Subject: Can I do this in a single SQL
On Tue, Feb 24, 2009 at 9:25 PM, Jim McAtee jmca...@mediaodyssey.com wrote:
The new system requires user registration and new comments are now tied
directly to a user account. What I'd like to do is link any newly
registered users to their old comments by using the email address. It's
- Original Message -
From: Matt Williams mgw...@gmail.com
To: cf-talk cf-talk@houseoffusion.com
Sent: Tuesday, February 24, 2009 7:47 PM
Subject: Re: Can I do this in a single SQL query?
UPDATE comments
SET userID = (
SELECT userID
FROM users
WHERE users.email = comments.email
The proper solution would be a separate table that contains the state
codes with their desired sort order. That table is joined to the main
table. This is probably best if you have to list all 50+ states.
If you only need to list three states, then I would use CASE as Azadi
suggested.
In
DBMS is MySQL 5. I have a report that is generated for cities within
several states. The states are designated by their two letter postal
abbreviations and are in a certain order. For example:
1. NY
2. CA
3. FL
I would like the records of the report sorted by the original state order,
then
Mike,
the way i read OP's question is that the order of states is not fixed,
but user-defined: i.e. a user selects several states in several select
lists and the data returned must be in the order of selected states. but
in case of pre-defined order of states, your suggestion will be perfect.
that's very neat, chris! i didn't think one could use FIND_IN_SET in
ORDER BY clause - live and learn!
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Chris Blackwell wrote:
You can do this in a very easy and elegant manner with MySQL. No need
for join or nasty case statements
SELECT *
Michael,
Thanks. That worked perfectly.
Jim
- Original Message -
From: Dawson, Michael m...@evansville.edu
To: cf-talk cf-talk@houseoffusion.com
Sent: Monday, January 26, 2009 8:07 AM
Subject: RE: SQL query sorting problem
The proper solution would be a separate table that contains
DBMS is MySQL 5. I have a report that is generated for cities within
several states. The states are designated by their two letter postal
abbreviations and are in a certain order. For example:
1. NY
2. CA
3. FL
I would like the records of the report sorted by the original state order,
then
rty a UNION query generated inside a cfloop:
cfquery ...
cfloop from=1 to=#listlen(states)# index=j
(SELECT *, #j# AS sortcol
FROM cities
WHERE state = '#listgetat(states, j)#')
cfif j lt listlen(states)UNION /cfif
/cfloop
ORDER BY sortcol, city
/cfquery
you could also probably build a dynamic
Cool, yeah I never remember until I do it when an aggregate query is going to
want HAVING vs WHERE. Glad it's working for you!
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free
- Original Message -
From: Jason Fisher [EMAIL PROTECTED]
Cool, yeah I never remember until I do it when an aggregate query is going
to want HAVING vs WHERE. Glad it's working for you!
This bites me too when I'm not paying attention. Just remember that the
WHERE applies to the
In MS SQL Server it's ISNULL(), but can't speak for other platforms. Can't
recall what it is in Oracle, might just be NULL().
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free
In MS SQL Server it's ISNULL(), but can't speak for other platforms.
Can't recall what it is in Oracle, might just be NULL().
IIRC in Oracle it is NVL. There is also COALESCE, which is usually a safe bet
with most databases.
didn't work, got an error. changed the isnull to ifnull, got a invalid use of
a group function error... I don't even know how to fix that...=(
Try this, I think it's what you're looking for:
SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points, p.
short_description, p.quantity,
NM, got it... changed the where clause to having and moved it below the group
by... seems to be working so far!
Thanks!
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
I have a MySQL query to pull all products from the database, no problem. I am
trying to get it to only display prodcuts that are in stock, the value should
be sold p.quantity in the following query. however, when sold appears as
null (very often) it removes the full record, which I don't
Try this, I think it's what you're looking for:
SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points,
p.short_description, p.quantity, p.image
FROM tblproducts as p LEFT JOIN
tblorder_list as o ON p.sku = o.sku #can_afford#
WHERE SUM(ISNULL(o.qty, 0)) p.quantity
GROUP BY p.sku
i believe the correct function to use is IFNULL(), not ISNULL()...
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Jason Fisher wrote:
Try this, I think it's what you're looking for:
SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points,
p.short_description, p.quantity,
I have a sql query (SQL Server 2000) for CFMX:
SELECT @Col_with_No_Pound = @Col_with_No_Pound + CASE
@Col_with_No_Pound WHEN '' THEN '' ELSE ',' END + COLUMN_NAME,
@Col_with_Pound= @Col_with_Pound + CASE @Col_with_Pound
WHEN '' THEN '#' ELSE '#,#' END + COLUMN_NAME
I have a sql query (SQL Server 2000) for CFMX:
SELECT @Col_with_No_Pound = @Col_with_No_Pound + CASE
@Col_with_No_Pound WHEN '' THEN '' ELSE ',' END + COLUMN_NAME,
@Col_with_Pound= @Col_with_Pound + CASE @Col_with_Pound
WHEN '' THEN '#' ELSE '#,#' END + COLUMN_NAME
.
Obviously
demo_Col_with_No_Pound = DemoCol.Col_with_No_Pound
37 : cfset demo_Col_with_Pound = replace(#DemoCol.Col_with_Pound#,
%, #, ALL)
Thx much
On Jan 26, 2008 11:07 AM, Tech Gate [EMAIL PROTECTED] wrote:
I have a sql query (SQL Server 2000) for CFMX:
SELECT @Col_with_No_Pound = @Col_with_No_Pound
If ever you need the literal string # and it's in a place where ColdFusion
will try to evaluate it (which is the case inside cfquery), use two ##.
Adrian
http://www.adrianlynch.co.uk/
-Original Message-
From: Tech Gate
Sent: 26 January 2008 16:29
To: CF-Talk
Subject: Re: # issue SQL
Very difficult to read your sql stmt. One option would be like this (aliasing
column name containing # sign).
e.g.
Select [column a with # sign] as col1
From myTBL
e.g. -- s step further with the above idea
using CASE ... WHEN ... ELSE ... END as well
I have a sql query (SQL Server 2000
Stivn .. wrote:
i want to create a SQL query above 2 tablespaces.
A tablespace is an implementation detail of your SQL server that does
not influence the logical database stucture so you would query it the
same way as you would a single tablespace.
Jochem
Hi all,
i want to create a SQL query with a LIMIT option but it will produce an error.
What is the correct syntax for a SQL query with LIMIT option, i tryed it like
this:
SELECT LABEL FROM table.name WHERE (STATUS = '1') AND (XY = 'AB') LIMIT 1,3
Thanks in advance for your help with best
On Wednesday 10 Oct 2007, [EMAIL PROTECTED] wrote:
i want to create a SQL query with a LIMIT option but it will produce an
error.
What error ?
On what database ?
--
Tom Chiverton
Helping to proactively iterate scalable markets
on: http://thefalken.livejournal.com
Stivn .. wrote:
i want to create a SQL query with a LIMIT option but it will produce an error.
What is the correct syntax for a SQL query with LIMIT option
That depends on your database. With MS databases you need to use TOP,
with Oracle rownum, with MySQL and PostgreSQL LIMIT. The syntax
Hi all,
i want to create a SQL query above 2 tablespaces. In the query i want to
compare if the value i searched fore is in tsA or tsB.
I tryed it with something like this:
SELECT * FROM tableA, tableB WHERE tableA.ts IN
(#preserveSingleQuotes(myDocumentsA)#) OR tableB.ts
So, i want to check if the value is in A or in B. But the result is
all entrys from tableA.
I don't understand.
How can you select records from tableA on the only fact that a field
from another table is in a list of values?
--
___
REUSE CODE! Use custom
Hi all,
i want to create a SQL query above 2 tablespaces. In the query i want
to compare if the value i searched fore is in tsA or tsB.
I tryed it with something like this:
SELECT * FROM tableA, tableB WHERE tableA.ts IN
(#preserveSingleQuotes(myDocumentsA)#) OR tableB.ts
How can I get at the actual query that is sent to the database. I can see it
on the debug page, but how would I get it in a variable so I could stick it in
the database or display it? Thanks
~|
Create robust enterprise, web
Subject: Get the SQL Query
How can I get at the actual query that is sent to the database. I can
see it on the debug page, but how would I get it in a variable so I
could stick it in the database or display it? Thanks
Just FYI, that only works in CF7.
-Original Message-
From: Peterson, Chris [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 16, 2007 7:21 AM
To: CF-Talk
Subject: RE: Get the SQL Query
Set the result attribute of your query, ie:
cfquery name=getData result=getDataResult
Then just dump
Thanks, That worked like a charm!
Jerome
-Original Message-
From: Peterson, Chris [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 16, 2007 8:21 AM
To: CF-Talk
Subject: RE: Get the SQL Query
Set the result attribute of your query, ie:
cfquery name=getData result=getDataResult
. wrote:
Thanks, That worked like a charm!
Jerome
-Original Message-
From: Peterson, Chris [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 16, 2007 8:21 AM
To: CF-Talk
Subject: RE: Get the SQL Query
Set the result attribute of your query, ie:
cfquery name=getData result
Message-
From: Peterson, Chris [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 16, 2007 8:21 AM
To: CF-Talk
Subject: RE: Get the SQL Query
Set the result attribute of your query, ie:
cfquery name=getData result=getDataResult
Then just dump the #getDataResult# That will give
thanks to everyone who replied, problem solved.
Jenny
-Original Message-
From: Rafael Marquez [mailto:[EMAIL PROTECTED]
Sent: 04 April 2007 21:06
To: CF-Talk
Subject: RE: SQL Query Help
Well, it's not a brain fart. That query is kinda kinky.
Use this as an example, replacing your
having brain fart here ... ughh
scenario: customer table, invoice table
I'd like to do a query to list the last invoice for each customer.
Can someone point me in the right direction please?
TIA,
Jenny
~|
ColdFusion MX7 by
Developer
www.bennadel.com
Need ColdFusion Help?
www.bennadel.com/ask-ben/
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 04, 2007 8:58 AM
To: CF-Talk
Subject: SQL Query Help
having brain fart here ... ughh
scenario: customer table, invoice
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 04, 2007 7:58 AM
To: CF-Talk
Subject: SQL Query Help
having brain fart here ... ughh
scenario: customer table, invoice table
I'd like to do a query to list the last invoice for each customer.
Can
Need more info: What would the query be to return the last invoice for a
single customer?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 04, 2007 8:58 AM
To: CF-Talk
Subject: SQL Query Help
having brain fart here ... ughh
scenario
(T.Deleted = 0) AND (T.Status 'Pending') AND (T.AccountType =
'Customer')
GROUP BY T.CustomerID, T.TransactionDate, T.ID
Hope this helps.
-Original Message-
From: Gaulin, Mark [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 04, 2007 3:39 PM
To: CF-Talk
Subject: RE: SQL Query Help
Hey all,
I'm having trouble trying to write a sql server query grouped on a datetime
field. The problem is that since the time is included in the field, the date
isn't really grouping well. I know of a datepart function but get an error when
I try it. Any help?
This sql statement groups all
Mik Muller wrote:
select left(datetime,11), count(*) as dt
from sitelog
where datetime = '2007-02-03 00:00'
group by left(datetime,11)
order by left(datetime,11) desc
Well, now that I look at it, the order by is ordering by jan 1 2006 etc in
This is how I'm doing it for now...
select count(*) as dt cfif url.stats eq bar-day, left(datetime,11) as
datetime/cfif
from sitelog
where datepart(year,datetime) = '#theyear#'
and datepart(month,datetime) = '#themonth#'
cfif listLen(url.datestat,/) eq 2
and datepart(year,datetime) =
I wish to store the actual text of a SQL query to a logfile.
At the risk of belaboring the obvious, from:
cfquery ...
Select * from blah
/cfquery
I wish to actual put select * from blah into a text variable and write
it to a logfile or data table.
Can someone suggest an easy way to do
To: CF-Talk
Subject: How to dump a sql query to text
I wish to store the actual text of a SQL query to a logfile.
At the risk of belaboring the obvious, from:
cfquery ...
Select * from blah
/cfquery
I wish to actual put select * from blah into a text variable and write it
to a logfile or data table
Well, you could start by throwing the SQL query code into a var to begin
with:
cfset SQLVar = Select * from table
Or build it up as you would build it within the cfquery tags.
Then execute it:
cfquery etc etc
#SQLVar#
/cfquery
And since it's already in a var, you can write it to your DB
cfqueryparams, then
you have to get rid of those in the version that goes to the log file.
If anyones got a better idea, I'd like to hear it too. :o)
Cheers,
Chris
Richard Colman wrote:
I wish to store the actual text of a SQL query to a logfile.
At the risk of belaboring the obvious, from
1 - 100 of 608 matches
Mail list logo
