RE: Trouble with query...
Not "ifnull" ... I'ts "isnull" .. ISNULL(col,0) AS alias Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Will Tomlinson [mailto:w...@wtomlinson.com] Sent: Saturday, October 24, 2009 6:04 PM To: cf-talk Subject: Re: Trouble with query... > i'm not very familiar with sql server syntax, but would something like > this work? > > SELECT IFNULL(cpn.numCoupons,0) as numCoupons, couponcode, > THEORDERDATE Hmm. didn't work. SQL server didn't like it. :( I just simplified it and didn't break it down by the day. I got somethin that'll work for now. Thanks jessica! Will ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327653 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Trouble with query...
> i'm not very familiar with sql server syntax, but would something like > this work? > > SELECT IFNULL(cpn.numCoupons,0) as numCoupons, couponcode, > THEORDERDATE Hmm. didn't work. SQL server didn't like it. :( I just simplified it and didn't break it down by the day. I got somethin that'll work for now. Thanks jessica! Will ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327651 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Trouble with query...
i'm not very familiar with sql server syntax, but would something like this work? SELECT IFNULL(cpn.numCoupons,0) as numCoupons, couponcode, THEORDERDATE FROM tblOrders LEFT JOIN (SELECT count(couponCode) AS numCoupons, THEORDERDATE FROM tblOrders WHERE THEORDERDATE AND couponcode =AND orderpaid = 1) as cpn ON cpn.THEORDERDATE=tblOrders.THEORDERDATE WHERE THEORDERDATE > SQL Server > > I'm trying to report on the number of orders per day that has coupon > code associated with them. The problem is, we can have multiple > coupons running at the same time. My query just counts the number of > coupons used per day. It doesn't filter by couponcode. > > But the minute I add a WHERE clause, it filters out all the days where > coupons weren't used - not what I want. I want to even show the days > where coupons were not used. > > Here's what doesn't work. If I remove the WHERE couponcode clause, it > show all days of the couponcode. Which is almost correct, but I need > to tell it to show the totals for a particular couponcode. > > SELECT DATEADD(d,DATEDIFF(d,0,orderdate),0) AS orderdate, > count(couponCode) AS numCoupons, couponcode > FROM tblOrders > WHERE > > orderdate between #createODBCdate(getcouponData.couponstartdate)# and > #createODBCdate(getcouponData.couponenddate)# > > WHERE couponcode = > AND orderpaid = 1 > GROUP BY DATEADD(d,DATEDIFF(d,0,orderdate),0), couponcode > ORDER BY DATEADD(d,DATEDIFF(d,0,orderdate),0) > > > Thanks, > Will ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327632 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Trouble with query...
SQL Server I'm trying to report on the number of orders per day that has coupon code associated with them. The problem is, we can have multiple coupons running at the same time. My query just counts the number of coupons used per day. It doesn't filter by couponcode. But the minute I add a WHERE clause, it filters out all the days where coupons weren't used - not what I want. I want to even show the days where coupons were not used. Here's what doesn't work. If I remove the WHERE couponcode clause, it show all days of the couponcode. Which is almost correct, but I need to tell it to show the totals for a particular couponcode. SELECT DATEADD(d,DATEDIFF(d,0,orderdate),0) AS orderdate, count(couponCode) AS numCoupons, couponcode FROM tblOrders WHERE orderdate between #createODBCdate(getcouponData.couponstartdate)# and #createODBCdate(getcouponData.couponenddate)# WHERE couponcode = AND orderpaid = 1 GROUP BY DATEADD(d,DATEDIFF(d,0,orderdate),0), couponcode ORDER BY DATEADD(d,DATEDIFF(d,0,orderdate),0) Thanks, Will ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327621 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Trouble with Query Update
No, it was not a fluke. I ran the query multiple times with the same results each time while I was trying to figure out what made it so slow. I have a copy of it, you can look at it. I agree with you that a single db connection with a single SQL statement should run faster, but it doesn't always, and I have a case to prove the point. I'm just saying that there are ways and ways of writing code, and just because one way is more accepted and elegant, it doesn't mean that the other way is wrong or even less desirable. - Matt Small -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 6:53 PM To: CF-Talk Subject: RE: Trouble with Query Update That was a fluke. It probably took a long time because it was establishing a first connection. When you execute a cfquery the DB has to compile the statement then execute it... if you use the IN function then only one statement gets compiled and and only one request to the DB is made which in turn minimizes the effects of any network latency. Think of it this way: If you need to buy 20 grocery items do you drive to the store, buy one, and drive home 20 times or do you make a list, drive to the store, get everything you need, then drive home just once? Use IN. It was invented to avoid repeated calls to the db. +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 2:08 PM To: CF-Talk Subject: RE: Trouble with Query Update Hmmm... that's what I thought until I recently used "in" in a query that I wrote to pull data from a table based on a set of ids that I had pulled from another table. I'm not all that great at SQL but when I ran this query it took over 1 minute to just execute the query on an Access DB with 50 records in one table and 9000 in the other. When I nested a query inside a loop to achieve the same effect, the whole page took 80 ms... I'm not saying that it's always the right way or always faster, but there are times that SQL can be much slower than equivalent CF. - Matt Small -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 4:51 PM To: CF-Talk Subject: RE: Trouble with Query Update Putting queries inside loops is never a good idea. Try building the list then using IN to update them all at once. Much faster and far more scalable! -- UPDATE Events SET Level = 1 WHERE EventID IN (#nonApprovedList#) UPDATE Events SET Level = Level + 1 WHERE EventID IN (#approvedList#) +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 1:23 PM To: CF-Talk Subject: Trouble with Query Update For the life of me I can't figure why I get the following error when I trying update a table in my database, PLEASE HELP: ** Code ** Select * From Events Where EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# ** End of Code ** ** Error ** Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUE
RE: Trouble with Query Update
level is a reserved word in Oracle, but I didn't know it was in Access or
SQL Server...
Good catch!
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
-Original Message-
From: Jason Larson [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 5:10 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
For the indivuals that have been following this thread. Once I renamed
the colum (Level) in my database to (EventLevel) it worked fine. I
don't know why, the only reason is maybe the word level is a reserved
word in some type of way.
Thanks for all your help,
Jason Larson
[EMAIL PROTECTED]
www.larzz.com
P. 406.728.4422
F. 406.543.4266
"You think the Cubs have sore arms? What about the fans in left field?"
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 5:21 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
** numeric **
UPDATE events
SET level = level + 1
WHERE eventID IN (1,2,3,4,5,6,7)
** text **
UPDATE events
SET level = level + 1
WHERE eventName IN ('event1','event2','event3')
** subquery **
UPDATE events
SET level = level + 1
WHERE eventDate IN (SELECT holidayDate FROM holidays)
the column name used with IN should be indexed. If it's a primary key
(like
EventID) then it is already indexed.
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis
-Original Message-
From: Jason Larson [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 4:00 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
Do you have an example of how you update with IN?
Thanks,
Jason Larson
[EMAIL PROTECTED]
www.larzz.com
P. 406.728.4422
F. 406.543.4266
"You think the Cubs have sore arms? What about the fans in left field?"
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 4:53 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
That was a fluke. It probably took a long time because it was
establishing
a first connection. When you execute a cfquery the DB has to compile
the
statement then execute it... if you use the IN function then only one
statement gets compiled and and only one request to the DB is made which
in
turn minimizes the effects of any network latency.
Think of it this way:
If you need to buy 20 grocery items do you drive to the store, buy one,
and
drive home 20 times or do you make a list, drive to the store, get
everything you need, then drive home just once?
Use IN. It was invented to avoid repeated calls to the db.
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis
-Original Message-
From: Matthew R. Small [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 2:08 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
Hmmm... that's what I thought until I recently used "in" in a query that
I wrote to pull data from a table based on a set of ids that I had
pulled from another table. I'm not all that great at SQL but when I ran
this query it took over 1 minute to just execute the query on an Access
DB with 50 records in one table and 9000 in the other. When I nested a
query inside a loop to achieve the same effect, the whole page took 80
ms... I'm not saying that it's always the right way or always faster,
but there are times that SQL can be much slower than equivalent CF.
- Matt Small
-----Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 4:51 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
Putting queries inside loops is never a good idea. Try building the
list
then using IN to update them all at once. Much faster and far more
scalable!
--
UPDATE Events
SET Level = 1
RE: Trouble with Query Update
For the indivuals that have been following this thread. Once I renamed
the colum (Level) in my database to (EventLevel) it worked fine. I
don't know why, the only reason is maybe the word level is a reserved
word in some type of way.
Thanks for all your help,
Jason Larson
[EMAIL PROTECTED]
www.larzz.com
P. 406.728.4422
F. 406.543.4266
"You think the Cubs have sore arms? What about the fans in left field?"
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 5:21 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
** numeric **
UPDATE events
SET level = level + 1
WHERE eventID IN (1,2,3,4,5,6,7)
** text **
UPDATE events
SET level = level + 1
WHERE eventName IN ('event1','event2','event3')
** subquery **
UPDATE events
SET level = level + 1
WHERE eventDate IN (SELECT holidayDate FROM holidays)
the column name used with IN should be indexed. If it's a primary key
(like
EventID) then it is already indexed.
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis
-Original Message-
From: Jason Larson [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 4:00 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
Do you have an example of how you update with IN?
Thanks,
Jason Larson
[EMAIL PROTECTED]
www.larzz.com
P. 406.728.4422
F. 406.543.4266
"You think the Cubs have sore arms? What about the fans in left field?"
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 4:53 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
That was a fluke. It probably took a long time because it was
establishing
a first connection. When you execute a cfquery the DB has to compile
the
statement then execute it... if you use the IN function then only one
statement gets compiled and and only one request to the DB is made which
in
turn minimizes the effects of any network latency.
Think of it this way:
If you need to buy 20 grocery items do you drive to the store, buy one,
and
drive home 20 times or do you make a list, drive to the store, get
everything you need, then drive home just once?
Use IN. It was invented to avoid repeated calls to the db.
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis
-Original Message-
From: Matthew R. Small [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 2:08 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
Hmmm... that's what I thought until I recently used "in" in a query that
I wrote to pull data from a table based on a set of ids that I had
pulled from another table. I'm not all that great at SQL but when I ran
this query it took over 1 minute to just execute the query on an Access
DB with 50 records in one table and 9000 in the other. When I nested a
query inside a loop to achieve the same effect, the whole page took 80
ms... I'm not saying that it's always the right way or always faster,
but there are times that SQL can be much slower than equivalent CF.
- Matt Small
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 4:51 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
Putting queries inside loops is never a good idea. Try building the
list
then using IN to update them all at once. Much faster and far more
scalable!
--
UPDATE Events
SET Level = 1
WHERE EventID IN (#nonApprovedList#)
UPDATE Events
SET Level = Level + 1
WHERE EventID IN (#approvedList#)
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis
-Original Message-
From: J
RE: Trouble with Query Update
** numeric **
UPDATE events
SET level = level + 1
WHERE eventID IN (1,2,3,4,5,6,7)
** text **
UPDATE events
SET level = level + 1
WHERE eventName IN ('event1','event2','event3')
** subquery **
UPDATE events
SET level = level + 1
WHERE eventDate IN (SELECT holidayDate FROM holidays)
the column name used with IN should be indexed. If it's a primary key (like
EventID) then it is already indexed.
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
-Original Message-
From: Jason Larson [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 4:00 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
Do you have an example of how you update with IN?
Thanks,
Jason Larson
[EMAIL PROTECTED]
www.larzz.com
P. 406.728.4422
F. 406.543.4266
"You think the Cubs have sore arms? What about the fans in left field?"
-Original Message-
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 4:53 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
That was a fluke. It probably took a long time because it was
establishing
a first connection. When you execute a cfquery the DB has to compile
the
statement then execute it... if you use the IN function then only one
statement gets compiled and and only one request to the DB is made which
in
turn minimizes the effects of any network latency.
Think of it this way:
If you need to buy 20 grocery items do you drive to the store, buy one,
and
drive home 20 times or do you make a list, drive to the store, get
everything you need, then drive home just once?
Use IN. It was invented to avoid repeated calls to the db.
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis
-Original Message-
From: Matthew R. Small [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 2:08 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
Hmmm... that's what I thought until I recently used "in" in a query that
I wrote to pull data from a table based on a set of ids that I had
pulled from another table. I'm not all that great at SQL but when I ran
this query it took over 1 minute to just execute the query on an Access
DB with 50 records in one table and 9000 in the other. When I nested a
query inside a loop to achieve the same effect, the whole page took 80
ms... I'm not saying that it's always the right way or always faster,
but there are times that SQL can be much slower than equivalent CF.
- Matt Small
-Original Message-----
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 4:51 PM
To: CF-Talk
Subject: RE: Trouble with Query Update
Putting queries inside loops is never a good idea. Try building the
list
then using IN to update them all at once. Much faster and far more
scalable!
--
UPDATE Events
SET Level = 1
WHERE EventID IN (#nonApprovedList#)
UPDATE Events
SET Level = Level + 1
WHERE EventID IN (#approvedList#)
+---+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+---+
"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis
-Original Message-
From: Jason Larson [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 1:23 PM
To: CF-Talk
Subject: Trouble with Query Update
For the life of me I can't figure why I get the following error when I
trying update a table in my database, PLEASE HELP:
** Code **
Select *
From Events
Where EventID = #EventID#
RE: Trouble with Query Update
Do you have an example of how you update with IN? Thanks, Jason Larson [EMAIL PROTECTED] www.larzz.com P. 406.728.4422 F. 406.543.4266 "You think the Cubs have sore arms? What about the fans in left field?" -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 4:53 PM To: CF-Talk Subject: RE: Trouble with Query Update That was a fluke. It probably took a long time because it was establishing a first connection. When you execute a cfquery the DB has to compile the statement then execute it... if you use the IN function then only one statement gets compiled and and only one request to the DB is made which in turn minimizes the effects of any network latency. Think of it this way: If you need to buy 20 grocery items do you drive to the store, buy one, and drive home 20 times or do you make a list, drive to the store, get everything you need, then drive home just once? Use IN. It was invented to avoid repeated calls to the db. +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 2:08 PM To: CF-Talk Subject: RE: Trouble with Query Update Hmmm... that's what I thought until I recently used "in" in a query that I wrote to pull data from a table based on a set of ids that I had pulled from another table. I'm not all that great at SQL but when I ran this query it took over 1 minute to just execute the query on an Access DB with 50 records in one table and 9000 in the other. When I nested a query inside a loop to achieve the same effect, the whole page took 80 ms... I'm not saying that it's always the right way or always faster, but there are times that SQL can be much slower than equivalent CF. - Matt Small -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 4:51 PM To: CF-Talk Subject: RE: Trouble with Query Update Putting queries inside loops is never a good idea. Try building the list then using IN to update them all at once. Much faster and far more scalable! -- UPDATE Events SET Level = 1 WHERE EventID IN (#nonApprovedList#) UPDATE Events SET Level = Level + 1 WHERE EventID IN (#approvedList#) +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 1:23 PM To: CF-Talk Subject: Trouble with Query Update For the life of me I can't figure why I get the following error when I trying update a table in my database, PLEASE HELP: ** Code ** Select * From Events Where EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# ** End of Code ** ** Error ** Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (11:3) to (11:96). Date/Time: 01/30/02 14:14:21 Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461) Remote Address: 127.0.0.1 HTTP Referrer: http://localhost/schoolCalendar//index.cfm/action/eventsCalendar/mod/app rovalList ** End of Error ***
RE: Trouble with Query Update
That was a fluke. It probably took a long time because it was establishing a first connection. When you execute a cfquery the DB has to compile the statement then execute it... if you use the IN function then only one statement gets compiled and and only one request to the DB is made which in turn minimizes the effects of any network latency. Think of it this way: If you need to buy 20 grocery items do you drive to the store, buy one, and drive home 20 times or do you make a list, drive to the store, get everything you need, then drive home just once? Use IN. It was invented to avoid repeated calls to the db. +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 2:08 PM To: CF-Talk Subject: RE: Trouble with Query Update Hmmm... that's what I thought until I recently used "in" in a query that I wrote to pull data from a table based on a set of ids that I had pulled from another table. I'm not all that great at SQL but when I ran this query it took over 1 minute to just execute the query on an Access DB with 50 records in one table and 9000 in the other. When I nested a query inside a loop to achieve the same effect, the whole page took 80 ms... I'm not saying that it's always the right way or always faster, but there are times that SQL can be much slower than equivalent CF. - Matt Small -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 4:51 PM To: CF-Talk Subject: RE: Trouble with Query Update Putting queries inside loops is never a good idea. Try building the list then using IN to update them all at once. Much faster and far more scalable! -- UPDATE Events SET Level = 1 WHERE EventID IN (#nonApprovedList#) UPDATE Events SET Level = Level + 1 WHERE EventID IN (#approvedList#) +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 1:23 PM To: CF-Talk Subject: Trouble with Query Update For the life of me I can't figure why I get the following error when I trying update a table in my database, PLEASE HELP: ** Code ** Select * From Events Where EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# ** End of Code ** ** Error ** Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (11:3) to (11:96). Date/Time: 01/30/02 14:14:21 Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461) Remote Address: 127.0.0.1 HTTP Referrer: http://localhost/schoolCalendar//index.cfm/action/eventsCalendar/mod/app rovalList ** End of Error ** Any help would be very helpfull Thanks, Jason Larson [EMAIL PROTECTED] __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/col
RE: Trouble with Query Update
I tried double quotes and that doesn't work, I changed the datatype in
the database from a number to a text field, and still no luck. I have
compared the query to other query that I have and they look exactly the
same. The query look fine, I just don't understand.
Thanks,
Jason Larson
[EMAIL PROTECTED]
www.larzz.com
P. 406.728.4422
F. 406.543.4266
"You think the Cubs have sore arms? What about the fans in left field?"
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 2:56 PM
To: Jason Larson
Subject: RE: Trouble with Query Update
Are event and level fields number fields? If not you will need, I
believe,
double quotes around the values you are trying to assign to them to
designate them as character fields.
I copied this right out of access from SQL view and this is what the
update
query looked like.
UPDATE dbo_Eligible_EE
SET dbo_Eligible_EE.Region = "manitoba"
Just a thought.
CC
Jason Larson
cc:
Subject: RE: Trouble with
Query Update
01/30/02
03:38 PM
I still get the same error when I wrap output tags around it.
Thanks,
Jason Larson
[EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 2:39 PM
To: [EMAIL PROTECTED]
Subject: Re: Trouble with Query Update
Try putting output tags around your whole section of code there.
CC
Jason Larson
@larzz.com> cc:
Subject: Trouble with Query
Update
01/30/02
03:22 PM
Please
respond to
cf-talk
For the life of me I can't figure why I get the following error when I
trying update a table in my database, PLEASE HELP:
** Code **
Select *
From Events
Where EventID = #EventID#
UPDATE Events
SET Level = #NextLevel#
WHERE EventID = #EventID#
UPDATE Events
SET Level = #NextLevel#
WHERE EventID = #EventID#
** End of Code **
** Error **
Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
The error occurred while processing an element with a general identifier
of (CFQUERY), occupying document position (11:3) to (11:96).
Date/Time: 01/30/02 14:14:21
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461)
Remote Address: 127.0.0.1
HTTP Referrer:
http://localhost/schoolCalendar//index.cfm/action/eventsCalendar/mod/app
rovalList
** End of Error **
Any help would be very helpfull
Thanks,
Jason Larson
[EMAIL PROTECTED]
__
Why Share?
Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Trouble with Query Update
Hmmm... that's what I thought until I recently used "in" in a query that I wrote to pull data from a table based on a set of ids that I had pulled from another table. I'm not all that great at SQL but when I ran this query it took over 1 minute to just execute the query on an Access DB with 50 records in one table and 9000 in the other. When I nested a query inside a loop to achieve the same effect, the whole page took 80 ms... I'm not saying that it's always the right way or always faster, but there are times that SQL can be much slower than equivalent CF. - Matt Small -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 4:51 PM To: CF-Talk Subject: RE: Trouble with Query Update Putting queries inside loops is never a good idea. Try building the list then using IN to update them all at once. Much faster and far more scalable! -- UPDATE Events SET Level = 1 WHERE EventID IN (#nonApprovedList#) UPDATE Events SET Level = Level + 1 WHERE EventID IN (#approvedList#) +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 1:23 PM To: CF-Talk Subject: Trouble with Query Update For the life of me I can't figure why I get the following error when I trying update a table in my database, PLEASE HELP: ** Code ** Select * From Events Where EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# ** End of Code ** ** Error ** Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (11:3) to (11:96). Date/Time: 01/30/02 14:14:21 Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461) Remote Address: 127.0.0.1 HTTP Referrer: http://localhost/schoolCalendar//index.cfm/action/eventsCalendar/mod/app rovalList ** End of Error ** Any help would be very helpfull Thanks, Jason Larson [EMAIL PROTECTED] __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Trouble with Query Update
Putting queries inside loops is never a good idea. Try building the list then using IN to update them all at once. Much faster and far more scalable! -- UPDATE Events SET Level = 1 WHERE EventID IN (#nonApprovedList#) UPDATE Events SET Level = Level + 1 WHERE EventID IN (#approvedList#) +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 1:23 PM To: CF-Talk Subject: Trouble with Query Update For the life of me I can't figure why I get the following error when I trying update a table in my database, PLEASE HELP: ** Code ** Select * From Events Where EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# ** End of Code ** ** Error ** Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (11:3) to (11:96). Date/Time: 01/30/02 14:14:21 Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461) Remote Address: 127.0.0.1 HTTP Referrer: http://localhost/schoolCalendar//index.cfm/action/eventsCalendar/mod/app rovalList ** End of Error ** Any help would be very helpfull Thanks, Jason Larson [EMAIL PROTECTED] __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Trouble with Query Update
Build your SQL statement outside of your query tag and see what it looks li ke. This will show where your problem is. Clint -- Original Message -- from: "Jason Larson" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] date: Wed, 30 Jan 2002 14:22:40 -0700 For the life of me I can't figure why I get the following error when I trying update a table in my database, PLEASE HELP: ** Code ** Select * From Events Where EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# ** End of Code ** ** Error ** Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (11:3) to (11:96). Date/Time: 01/30/02 14:14:21 Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461) Remote Address: 127.0.0.1 HTTP Referrer: http://localhost/schoolCalendar//index.cfm/action/eventsCalendar/mod/app rovalList ** End of Error ** Any help would be very helpfull Thanks, Jason Larson [EMAIL PROTECTED] __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Trouble with Query Update
For the life of me I can't figure why I get the following error when I trying update a table in my database, PLEASE HELP: ** Code ** Select * From Events Where EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# UPDATE Events SET Level = #NextLevel# WHERE EventID = #EventID# ** End of Code ** ** Error ** Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (11:3) to (11:96). Date/Time: 01/30/02 14:14:21 Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461) Remote Address: 127.0.0.1 HTTP Referrer: http://localhost/schoolCalendar//index.cfm/action/eventsCalendar/mod/app rovalList ** End of Error ** Any help would be very helpfull Thanks, Jason Larson [EMAIL PROTECTED] __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
