Re: SQL Server and Nulls
dbfield = cfqueryparam cfsqltype=cf_sql_tinyint value=#mydatefield# null=#yesnoformat(mydatefield is '')# On Fri, Apr 17, 2015 at 3:58 PM, Robert Harrison rharri...@aimg.com wrote: In an update query, to a tinyint field which allows nulls, I have the update dbfield=#mydatefield# If mydatefield has no value, sql is throwing an error. The field allows nulls. I've never had to say if then NULL before. What the heck? Robert Harrison Full Stack Developer AIMG rharri...@aimg.com Main Office: 704-321-1234 ext.118 Direct Line: 516-302-4345 www.aimg.com ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:360471 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Server and Nulls
Dbfield=cfqueryparam value=#mydatefield# cfsqltype=CF_SQL_TINYINT null=#not len(mydatefield)# / Steve -Original Message- From: Robert Harrison [mailto:rharri...@aimg.com] Sent: Friday, April 17, 2015 3:58 PM To: cf-talk Subject: SQL Server and Nulls In an update query, to a tinyint field which allows nulls, I have the update dbfield=#mydatefield# If mydatefield has no value, sql is throwing an error. The field allows nulls. I've never had to say if then NULL before. What the heck? Robert Harrison Full Stack Developer AIMG rharri...@aimg.com Main Office: 704-321-1234 ext.118 Direct Line: 516-302-4345 www.aimg.com ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:360470 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
Thanks everyone for the suggestions. I've tested the one at this link: http://www.mssqltips.com/sqlservertip/1555/sql-server-find-and-replace-values-in-all-tables-and-all-text-columns/ and it works perfectly. I mentioned previously that I was hoping to run it in CF, and that was partially because some of the hosts don't like to give direct access to the data bases on their servers. Regardless, after testing I can see it's a heavy load and have to agree with Ben Forta that it really should be run as a query in Studio. I'll deal with getting the access I need to run directly. Thanks, Robert Robert Harrison Director of Interactive Services Austin Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austi ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357865 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Global String Replace
you could just used a stored procedure which will save it directly to the database, and then execute it from CF On Thu, Mar 6, 2014 at 9:05 PM, Robert Harrison rob...@austin-williams.comwrote: Thanks everyone for the suggestions. I've tested the one at this link: http://www.mssqltips.com/sqlservertip/1555/sql-server-find-and-replace-values-in-all-tables-and-all-text-columns/and it works perfectly. I mentioned previously that I was hoping to run it in CF, and that was partially because some of the hosts don't like to give direct access to the data bases on their servers. Regardless, after testing I can see it's a heavy load and have to agree with Ben Forta that it really should be run as a query in Studio. I'll deal with getting the access I need to run directly. Thanks, Robert Robert Harrison Director of Interactive Services Austin Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austi ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357866 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
you could just used a stored procedure which will save it directly to the database, and then execute it from CF That never even crossed my mind. Good idea. Thanks. Robert Harrison Director of Interactive Services Austin Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357867 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Global String Replace
you mean like this http://www.mssqltips.com/sqlservertip/1555/sql-server-find-and-replace-values-in-all-tables-and-all-text-columns/ On Tue, Mar 4, 2014 at 1:55 PM, Robert Harrison rob...@austin-williams.comwrote: Does anyone have an update program that can update a text string in all tables/rows/columns of an MS SQL data base? Need to do a global text string replace on several sites. Any help appreciated. Thanks, Robert Harrison Director of Interactive Services Austin Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_wi ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357833 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
Take a look at the sys.tables and sys.columns tables in your SQL database. You should be able to write a couple of cursors to loop over each and just print out the SQL to run separately (or you can get fancy and generate the SQL statement and run it via EXEC sp_executeSQL functions). I don't have my SQL server handy but can take a look later. -- Jeff -Original Message- From: Robert Harrison [mailto:rob...@austin-williams.com] Sent: Tuesday, March 04, 2014 6:56 AM To: cf-talk Subject: SQL Global String Replace Does anyone have an update program that can update a text string in all tables/rows/columns of an MS SQL data base? Need to do a global text string replace on several sites. Any help appreciated. Thanks, Robert Harrison Director of Interactive Services Austin Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_wi ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357834 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
Yes, I do mean like that, but I was really hoping someone had it already written up in CF with a tested procedure they would be willing to share. I was able to find several downloads for PHP, but nothing for CF. Thanks Robert Harrison Director of Interactive Services Austin Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357835 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
Actually, that's the kind of operation that you'd not want to perform in CF (or PHP or any other database client). Unless you truly need all that data within a CF page for some other reason, you shouldn't be sending it all back and forth between DBMS and CF. --- Ben (Sent from my newest Android device) On Mar 4, 2014 6:08 AM, Robert Harrison rob...@austin-williams.com wrote: Yes, I do mean like that, but I was really hoping someone had it already written up in CF with a tested procedure they would be willing to share. I was able to find several downloads for PHP, but nothing for CF. Thanks Robert Harrison Director of Interactive Services Austin Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357836 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
Actually, that's the kind of operation that you'd not want to perform in CF (or PHP or any other database client). Unless you truly need all that data within a CF page for some other reason, you shouldn't be sending it all back and forth between DBMS and CF. ... I'm going to do this locally, but it seems to be done in PHP as a matter of course. We use such a thing on our WordPress sites when to change the URLs we move from a staging URL to a live URL, and there are lots of PHP programs prewritten to do just that. What I'm doing here is the same thing... changing URLs embedded into the CMS pages. - Robert ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357837 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
I agree with Ben...this is something that you should be running in SQL Management Studio. ColdFusion is not meant to run this kind of stuff (unless you set the timeout on your templates to 0). The code attached below will loop over all of the user tables, and then loop over all of the text type columns for that table while writing out an update statement. Send your results to Text in the query window. Then you can cut and paste the text results into a new query window and run it. Make sure you change the text values to be changed and what they are going to be changed to. Also, if your tables follow a naming convention, you can add that to the first cursor declaration as an additional where clause... DECLARE @tableName NVARCHAR(255), @objectID BIGINT, @columnName NVARCHAR(255), @TextToReplace NVARCHAR(1000), @ReplaceTextWith NVARCHAR(1000) SET @TextToReplace = 'www.mysite.com' SET @replaceTextWith = 'www.mynewsite.com' DECLARE userTables CURSOR FOR SELECT name, object_id FROM sys.tables WHERE [type] = 'U' OPEN userTables FETCH NEXT FROM userTables INTO @tableName, @objectID WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'UPDATE ' + @tableName + ' SET ' /* Select the columns from the table where the data type is a text type column Text- 35 sql_variant - 98 ntext - 99 varchar - 167 char- 175 nvarchar- 231 nchar - 239 */ DECLARE userColumns CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @objectID AND system_type_id IN (35,98,99,167,175,231,239) OPEN userColumns FETCH NEXT FROM userColumns INTO @columnName WHILE @@FETCH_STATUS = 0 BEGIN PRINT '[' + @columnName + '] = REPLACE([' + @columnname + '], ''' + @TextToReplace + ''', ''' + @ReplaceTextWith + '''),' FETCH NEXT FROM userColumns INTO @columnName END CLOSE userColumns DEALLOCATE userColumns PRINT '1=1' PRINT 'GO' PRINT ' ' FETCH NEXT FROM userTables INTO @tableName, @columnName END CLOSE userTables DEALLOCATE userTables Original Message From: Robert Harrison rob...@austin-williams.com Sent: Tuesday, March 04, 2014 7:19 AM To: cf-talk cf-talk@houseoffusion.com Subject: RE: SQL Global String Replace Actually, that's the kind of operation that you'd not want to perform in CF (or PHP or any other database client). Unless you truly need all that data within a CF page for some other reason, you shouldn't be sending it all back and forth between DBMS and CF. ... I'm going to do this locally, but it seems to be done in PHP as a matter of course. We use such a thing on our WordPress sites when to change the URLs we move from a staging URL to a live URL, and there are lots of PHP programs prewritten to do just that. What I'm doing here is the same thing... changing URLs embedded into the CMS pages. - Robert ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357847 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
Yes indeed. We had some attempts to injection attack via a fake useragent variable in the CGI scope, as we were logging visiting useragents in a database table. Luckily they were not able to execute any code thanks to tight SQL permissions, but the code they were trying to execute was written to the table. I'd not even thought of that method till we saw it. But something to have an eye on. Regards, Ian. On 23/01/2013 19:09, Pete Freitag wrote: On Wed, Jan 23, 2013 at 12:57 PM, Rob Voylerobvo...@voyle.com wrote: Hi Greg As I continue to update my security processes, I'm curious Was this injection attempt at the url or at a form input. Keep in mind that vulnerabilites can come from any input that the attacker can manipulate, eg form, url, cgi, cookie variables are all game. -- Pete Freitag - Adobe Community Professional http://foundeo.com/ - ColdFusion Consulting Products http://hackmycf.com - Is your ColdFusion Server Secure? http://www.youtube.com/watch?v=ubESB87vl5U - FuseGuard your CFML in 10 minutes ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354042 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
Hi Greg As I continue to update my security processes, I'm curious Was this injection attempt at the url or at a form input. Thanks Rob On 22 Jan 2013 at 11:12, Greg Morphis wrote: I saw some request errors but what were they trying to do? This is what the onRequest error email showed declare @q varchar(8000) select @q = 0x57414954464F522044454C4159202730303A30303A313527 exec(@q) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag= houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:35 3998 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354030 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
It was attempted via the URL On Wed, Jan 23, 2013 at 11:57 AM, Rob Voyle robvo...@voyle.com wrote: Hi Greg As I continue to update my security processes, I'm curious Was this injection attempt at the url or at a form input. Thanks Rob On 22 Jan 2013 at 11:12, Greg Morphis wrote: I saw some request errors but what were they trying to do? This is what the onRequest error email showed declare @q varchar(8000) select @q = 0x57414954464F522044454C4159202730303A30303A313527 exec(@q) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag= houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:35 3998 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354031 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
On Wed, Jan 23, 2013 at 12:57 PM, Rob Voyle robvo...@voyle.com wrote: Hi Greg As I continue to update my security processes, I'm curious Was this injection attempt at the url or at a form input. Keep in mind that vulnerabilites can come from any input that the attacker can manipulate, eg form, url, cgi, cookie variables are all game. -- Pete Freitag - Adobe Community Professional http://foundeo.com/ - ColdFusion Consulting Products http://hackmycf.com - Is your ColdFusion Server Secure? http://www.youtube.com/watch?v=ubESB87vl5U - FuseGuard your CFML in 10 minutes ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354032 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
That's hex for, ?WAITFOR DELAY '00:00:15' On Tue, Jan 22, 2013 at 11:12 AM, Greg Morphis gmorp...@gmail.com wrote: 0x57414954464F522044454C4159202730303A30303A313527 -- John Bliss - http://about.me/jbliss ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353999 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
Ah so they were just checking to see if they could get something to work before possibly trying anything real. Thanks! On Tue, Jan 22, 2013 at 11:15 AM, John M Bliss bliss.j...@gmail.com wrote: That's hex for, ?WAITFOR DELAY '00:00:15' On Tue, Jan 22, 2013 at 11:12 AM, Greg Morphis gmorp...@gmail.com wrote: 0x57414954464F522044454C4159202730303A30303A313527 -- John Bliss - http://about.me/jbliss ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354000 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
Ah so they were just checking to see if they could get something to work before possibly trying anything real. That's a pretty standard approach. If they can get the response to delay then they can mark that URL as a potential entry point to come back and explore more later. -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354001 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Question, incrementing values
This gives you the error...? CASE WHEN cc.cc_type_ID = 1 THEN @careCB + 1 WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 ELSE 0 END AS myvar On Thu, Dec 13, 2012 at 3:45 PM, Scott Stewart webmas...@sstwebworks.comwrote: CASE WHEN cc.cc_type_ID = 1 THEN @careCB + 1 WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 ELSE 0 END -- John Bliss - http://about.me/jbliss ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353453 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Question, incrementing values
Except I need those values individually as part of the return.. On 12/13/2012 4:49 PM, John M Bliss wrote: This gives you the error...? CASE WHEN cc.cc_type_ID = 1 THEN @careCB + 1 WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 ELSE 0 END AS myvar On Thu, Dec 13, 2012 at 3:45 PM, Scott Stewart webmas...@sstwebworks.comwrote: CASE WHEN cc.cc_type_ID = 1 THEN @careCB + 1 WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 ELSE 0 END -- Scott Stewart Adobe Certified Expert / Instructor ColdFusion 8, 9 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353454 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Question, incrementing values
DOH!! end of the day, yeah... cc.cc_type_id isn't part of a group by or aggregate function.. and I need those end values as part of the returned record set On 12/13/2012 4:49 PM, John M Bliss wrote: This gives you the error...? CASE WHEN cc.cc_type_ID = 1 THEN @careCB + 1 WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 ELSE 0 END AS myvar On Thu, Dec 13, 2012 at 3:45 PM, Scott Stewart webmas...@sstwebworks.comwrote: CASE WHEN cc.cc_type_ID = 1 THEN @careCB + 1 WHEN cc.cc_type_ID = 2 THEN @careFBC + 1 WHEN cc.cc_type_ID = 3 THEN @careSBC + 1 WHEN cc.cc_type_ID = 4 THEN @careVACC + 1 WHEN cc.cc_type_ID = 5 THEN @careOFCC + 1 ELSE 0 END -- Scott Stewart Adobe Certified Expert / Instructor ColdFusion 8, 9 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353455 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Express and CF
I spoke too soon. The installer with tools provides limited replication support and SSMS, but not SSIS. -Carl V. On 11/15/2012 4:30 PM, Carl Von Stetten wrote: Starting with SQL Server Express 2008 R2 (and maybe some prior versions), you can download an installer that includes the SSMS tools, which I think includes SSIS as well. -Carl V. On 11/15/2012 1:32 PM, Mike Kear wrote: the things cut out of the express version are the kinds of things we use coldfusion for anyway. I havent found any issues at all in connecting SQLexpress versions and Coldfusion. The only issues I've had are to do with things like the lack of SSIS which makes things like moving data to online more difficult that's all. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Fri, Nov 16, 2012 at 6:32 AM, Pete Ruckelshaus pruckelsh...@gmail.comwrote: Works just like the full version, and it's what I use on my VPS. On Thu, Nov 15, 2012 at 5:23 AM, Kevin Parker tras...@internode.on.net wrote: Are there any issues using Express versions of SQL Server for development? Thank you ++ Kevin Parker M: 0418 815 527 ++ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353207 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Express and CF
You'll need to purchase the developer edition for SSIS. It's not free but has full standard level features and is pretty affordable $50 US at NewEgg http://www.newegg.com/Product/Product.aspx?Item=N82E16832416455Tpk=sql%20server%20developer Best Regards, Donnie Bachan Nitendo Vinces - By Striving You Shall Conquer == The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. On Fri, Nov 16, 2012 at 4:22 PM, Carl Von Stetten vonner.li...@vonner.netwrote: I spoke too soon. The installer with tools provides limited replication support and SSMS, but not SSIS. -Carl V. On 11/15/2012 4:30 PM, Carl Von Stetten wrote: Starting with SQL Server Express 2008 R2 (and maybe some prior versions), you can download an installer that includes the SSMS tools, which I think includes SSIS as well. -Carl V. On 11/15/2012 1:32 PM, Mike Kear wrote: the things cut out of the express version are the kinds of things we use coldfusion for anyway. I havent found any issues at all in connecting SQLexpress versions and Coldfusion. The only issues I've had are to do with things like the lack of SSIS which makes things like moving data to online more difficult that's all. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Fri, Nov 16, 2012 at 6:32 AM, Pete Ruckelshaus pruckelsh...@gmail.comwrote: Works just like the full version, and it's what I use on my VPS. On Thu, Nov 15, 2012 at 5:23 AM, Kevin Parker tras...@internode.on.net wrote: Are there any issues using Express versions of SQL Server for development? Thank you ++ Kevin Parker M: 0418 815 527 ++ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353208 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Express and CF
Works fine for me. (I think it has a 2GB database size limit.) It also doesn't support DTS and some other nice management functionality, but most of the features of Enterprise Manager are there. On 11/15/12 2:23 AM, Kevin Parker wrote: Are there any issues using Express versions of SQL Server for development? Thank you ++ Kevin Parker M: 0418 815 527 ++ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353169 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Express and CF
Works just like the full version, and it's what I use on my VPS. On Thu, Nov 15, 2012 at 5:23 AM, Kevin Parker tras...@internode.on.netwrote: Are there any issues using Express versions of SQL Server for development? Thank you ++ Kevin Parker M: 0418 815 527 ++ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353196 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Express and CF
the things cut out of the express version are the kinds of things we use coldfusion for anyway. I havent found any issues at all in connecting SQLexpress versions and Coldfusion. The only issues I've had are to do with things like the lack of SSIS which makes things like moving data to online more difficult that's all. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Fri, Nov 16, 2012 at 6:32 AM, Pete Ruckelshaus pruckelsh...@gmail.comwrote: Works just like the full version, and it's what I use on my VPS. On Thu, Nov 15, 2012 at 5:23 AM, Kevin Parker tras...@internode.on.net wrote: Are there any issues using Express versions of SQL Server for development? Thank you ++ Kevin Parker M: 0418 815 527 ++ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353200 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Express and CF
Oh, and I don't think you can run scheduled backups either. Which is an issue when using it in production. On 11/15/12 1:32 PM, Mike Kear wrote: the things cut out of the express version are the kinds of things we use coldfusion for anyway. I havent found any issues at all in connecting SQLexpress versions and Coldfusion. The only issues I've had are to do with things like the lack of SSIS which makes things like moving data to online more difficult that's all. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Fri, Nov 16, 2012 at 6:32 AM, Pete Ruckelshaus pruckelsh...@gmail.comwrote: Works just like the full version, and it's what I use on my VPS. On Thu, Nov 15, 2012 at 5:23 AM, Kevin Parker tras...@internode.on.net wrote: Are there any issues using Express versions of SQL Server for development? Thank you ++ Kevin Parker M: 0418 815 527 ++ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353201 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Express and CF
Oh, and I don't think you can run scheduled backups either. Which is an issue when using it in production. We use Tomahawk Backup on some of our web servers to back up the website code and images to both local and off-site storage. Tomahawk (and many other backup utilities) will interface with SQL Server (even the Express edition which we have deployed in production in a few places) and back up your databases locally and off-site as well. Works out pretty well. -Justin Scott ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353202 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Express and CF
You do get ssis and backups its just not in the sql management studio so you have to script it. Or downliad the full trial version with tools and get studio from there instead. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Nov 15, 2012 10:25 PM, Justin Scott leviat...@darktech.org wrote: Oh, and I don't think you can run scheduled backups either. Which is an issue when using it in production. We use Tomahawk Backup on some of our web servers to back up the website code and images to both local and off-site storage. Tomahawk (and many other backup utilities) will interface with SQL Server (even the Express edition which we have deployed in production in a few places) and back up your databases locally and off-site as well. Works out pretty well. -Justin Scott ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353203 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Express and CF
Or downliad the full trial version with tools and get studio from there instead. Yeah, what Russ said. I think the trial version is good for 6 months. IIRC you can also get the MSSQL developer edition for $40-$50. It is the equivalent of the Enterprise version. Not sure if the Licencing allows you to use Management Studio to push changes to production Databases. I don't see why not. You might be eligible to get the Web addition for free via the WebsiteSpark program http://www.microsoft.com/web/websitespark/ Web edition removes the Ram and Database size limitations. It might be worth a try. In any event, here is the feature matrix for the different versions. http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx HTH G! On Thu, Nov 15, 2012 at 6:03 PM, Russ Michaels r...@michaels.me.uk wrote: You do get ssis and backups its just not in the sql management studio so you have to script it. Or downliad the full trial version with tools and get studio from there instead. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Nov 15, 2012 10:25 PM, Justin Scott leviat...@darktech.org wrote: Oh, and I don't think you can run scheduled backups either. Which is an issue when using it in production. We use Tomahawk Backup on some of our web servers to back up the website code and images to both local and off-site storage. Tomahawk (and many other backup utilities) will interface with SQL Server (even the Express edition which we have deployed in production in a few places) and back up your databases locally and off-site as well. Works out pretty well. -Justin Scott ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353204 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Express and CF
When I last used that trick the management studio never stopped working, only sql server, so unlrss they have changed that, its a free way to get more features out of express edition. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Nov 15, 2012 11:54 PM, Gerald Guido gerald.gu...@gmail.com wrote: Or downliad the full trial version with tools and get studio from there instead. Yeah, what Russ said. I think the trial version is good for 6 months. IIRC you can also get the MSSQL developer edition for $40-$50. It is the equivalent of the Enterprise version. Not sure if the Licencing allows you to use Management Studio to push changes to production Databases. I don't see why not. You might be eligible to get the Web addition for free via the WebsiteSpark program http://www.microsoft.com/web/websitespark/ Web edition removes the Ram and Database size limitations. It might be worth a try. In any event, here is the feature matrix for the different versions. http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx HTH G! On Thu, Nov 15, 2012 at 6:03 PM, Russ Michaels r...@michaels.me.uk wrote: You do get ssis and backups its just not in the sql management studio so you have to script it. Or downliad the full trial version with tools and get studio from there instead. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Nov 15, 2012 10:25 PM, Justin Scott leviat...@darktech.org wrote: Oh, and I don't think you can run scheduled backups either. Which is an issue when using it in production. We use Tomahawk Backup on some of our web servers to back up the website code and images to both local and off-site storage. Tomahawk (and many other backup utilities) will interface with SQL Server (even the Express edition which we have deployed in production in a few places) and back up your databases locally and off-site as well. Works out pretty well. -Justin Scott ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353205 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Express and CF
Starting with SQL Server Express 2008 R2 (and maybe some prior versions), you can download an installer that includes the SSMS tools, which I think includes SSIS as well. -Carl V. On 11/15/2012 1:32 PM, Mike Kear wrote: the things cut out of the express version are the kinds of things we use coldfusion for anyway. I havent found any issues at all in connecting SQLexpress versions and Coldfusion. The only issues I've had are to do with things like the lack of SSIS which makes things like moving data to online more difficult that's all. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Fri, Nov 16, 2012 at 6:32 AM, Pete Ruckelshaus pruckelsh...@gmail.comwrote: Works just like the full version, and it's what I use on my VPS. On Thu, Nov 15, 2012 at 5:23 AM, Kevin Parker tras...@internode.on.net wrote: Are there any issues using Express versions of SQL Server for development? Thank you ++ Kevin Parker M: 0418 815 527 ++ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353206 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Injection
This is possibly from XRumer. It is link building/forum spamming software. On Thu, May 24, 2012 at 5:30 AM, Kevin Parker tras...@internode.on.netwrote: One of my sites that has some anti-injection script reported this today - does anyone know what this clown was trying to do. Thank you!! URL: /news_detail.cfm?NewsID=37+++Result:+no+post+sending +forms+are+found; ++ Kevin Parker ++ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:351323 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL need to return data even if specific where statement isnt matched
Switch your query around and join the answers to the questions, instead of the questions to the answers. Hopefully that makes sense. On Mon, Sep 26, 2011 at 11:43 AM, Adam Bourg adam.bo...@gmail.com wrote: 've built an extension to a employment application where we can easily add new questions to the form. I need to query to match on both which job they applied to and what application ID it is. I need to return both the answer and the question, the problem is it will return both if both are defined, but I need it to return the question, even if the application isn't defined, but right now it'll only return the question if the answer is defined. Please help! Code: (Note the where uses a Coldfusion variable, so nothing out of the normal) SELECT dbo.mod_employmentAppQuestionAnswers.questionID ,dbo.mod_employmentAppQuestionAnswers.questionDefinitionID ,dbo.mod_employmentAppQuestionAnswers.AppID ,dbo.mod_employmentAppQuestionAnswers.questionText ,dbo.mod_employmentAppQuestionAnswers.questionDate1 ,dbo.mod_employmentAppQuestionAnswers.questionDate2 ,dbo.mod_employmentAppQuestionAnswers.questionBit ,dbo.mod_employmentAppQuestionDefinitions.definitionID ,dbo.mod_employmentAppQuestionDefinitions.jobTitleID ,dbo.mod_employmentAppQuestionDefinitions.title AS QuestionTitle ,dbo.mod_employmentAppQuestionDefinitions.questionTypeID ,dbo.mod_employmentAppQuestionDefinitions.description ,dbo.mod_employmentAppQuestionDefinitions.isActive ,dbo.mod_employmentAppJobTitles.title AS JobTitle ,dbo.mod_employmentAppQuestionTypes.type AS QuestionType FROM dbo.mod_employmentAppQuestionAnswers FULL JOIN dbo.mod_employmentAppQuestionDefinitions ON dbo.mod_employmentAppQuestionAnswers.questionDefinitionID = dbo.mod_employmentAppQuestionDefinitions.definitionID INNER JOIN dbo.mod_employmentAppJobTitles ON dbo.mod_employmentAppQuestionDefinitions.jobTitleID = dbo.mod_employmentAppJobTitles.jobTitleID LEFT JOIN dbo.mod_employmentAppQuestionTypes ON dbo.mod_employmentAppQuestionDefinitions.questionTypeID = dbo.mod_employmentAppQuestionTypes.questionTypeID WHERE (dbo.mod_employmentAppQuestionDefinitions.jobTitleID = cfqueryparam cfsqltype=cf_sql_integer value=#jobTitleID# /) AND (dbo.mod_employmentAppQuestionAnswers.AppID = cfqueryparam cfsqltype=cf_sql_integer value=#applicationID# /) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347745 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL grrr
Something like... SELECT ID FROM MyTable WHERE value IN (cfqueryparam cfsqltype=cf_sql_varchar value=#myList# list=true /) ; ?? On Mon, Sep 26, 2011 at 12:18 PM, Richard White rich...@j7is.co.uk wrote: Hi, i know this is probably a simple answer and i probably drunk too much coffee! given the following sql data: ID value 1 A 1 B 1 C 2 A 2 B 3 A 3 B 3 C i need to run a query that says return me the ids that are linked to values A and B and C. so this query on the above data would return IDs 1 and 3 thanks :) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347713 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL grrr
ids that are linked to values A and B and C. If you mean only ID's linked to all three (3) values? Something like this cfset distinctValues = A,B,C ... SELECT ID, COUNT(Value) AS MatchCount FROM TableName WHERE ID IN ( cfqueryparam value=#distinctValues# list=true cfsqltype=cf_sql_varchar ) HAVING COUNT(Value) = cfqueryparam value=#listLen(distinctValues)# cfsqltype=cf_sql_ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347714 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL grrr
Richard, I think this will work (untested, assumes SQL Server): select t.ID from mytable t inner join mytable a on t.id = a.id and a.value = 'A' inner join mytable b on t.id = b.id and b.value = 'B' inner join mytable c on t.id = c.id and c.value = 'C' HTH, Carl ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347715 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL grrr
I'm sure there is a much more efficient way of doing this, but this would work: SELECT distinct ID from table where id in (select ID from table where val = 'A' and id in (select id from table where val = 'B' and ID in (select id from table where val = 'C'))) On Mon, Sep 26, 2011 at 1:18 PM, Richard White rich...@j7is.co.uk wrote: Hi, i know this is probably a simple answer and i probably drunk too much coffee! given the following sql data: ID value 1 A 1 B 1 C 2 A 2 B 3 A 3 B 3 C i need to run a query that says return me the ids that are linked to values A and B and C. so this query on the above data would return IDs 1 and 3 thanks :) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347716 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL grrr
Yup, I think Carl's is the best, though you'd probably want to throw a GROUP BY in there so you don't get multiple rows for the same ID. -- Josh On Mon, Sep 26, 2011 at 10:41 AM, Carl Von Stetten vonner.li...@vonner.netwrote: Richard, I think this will work (untested, assumes SQL Server): select t.ID from mytable t inner join mytable a on t.id = a.id and a.value = 'A' inner join mytable b on t.id = b.id and b.value = 'B' inner join mytable c on t.id = c.id and c.value = 'C' HTH, Carl ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347717 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL grrr
Or change the first line to: select distinct t.ID (again assumes SQL Server) Carl On 9/26/2011 10:44 AM, Josh Nathanson wrote: Yup, I think Carl's is the best, though you'd probably want to throw a GROUP BY in there so you don't get multiple rows for the same ID. -- Josh On Mon, Sep 26, 2011 at 10:41 AM, Carl Von Stetten vonner.li...@vonner.netwrote: Richard, I think this will work (untested, assumes SQL Server): select t.ID from mytable t inner join mytable a on t.id = a.id and a.value = 'A' inner join mytable b on t.id = b.id and b.value = 'B' inner join mytable c on t.id = c.id and c.value = 'C' HTH, Carl ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347718 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL grrr
thanks, works perfect! Hi, i know this is probably a simple answer and i probably drunk too much coffee! given the following sql data: ID value 1 A 1 B 1 C 2 A 2 B 3 A 3 B 3 C i need to run a query that says return me the ids that are linked to values A and B and C. so this query on the above data would return IDs 1 and 3 thanks :) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347720 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL grrr
If you mean only ID's linked to all three (3) values? Something like this Duh. Just noticed I left off the GROUP BY... cfset distinctValues = A,B,C ... SELECT ID, COUNT(Value) AS MatchCount FROM TableName WHERE ID IN ( cfqueryparam value=#distinctValues# list=true cfsqltype=cf_sql_varchar ) GROUP BY ID HAVING COUNT(Value) = cfqueryparam value=#listLen(distinctValues)# cfsqltype=cf_sql_integer ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347721 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
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 thanks, Jenny ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345494 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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 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, 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 thanks, Jenny ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345495 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Query Problem
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 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, 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 thanks, Jenny ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345496 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
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, Jenny ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345497 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
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: ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345498 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
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 6/21/2011 10:37 AM, Jenny Gavin-Wear wrote: ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345499 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Query Problem
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 memberID not in (select memberID from tbl_paypal where datepart(m, payment_date) = #session.month# and datepart(,payment_date) = #session.year# )) order by paid ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345500 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
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 profiles. Legacy code :/ Jenny select * from tbl_members where (datepart(m,paid) = #session.month# and datepart(,paid) = #session.year# AND memberID not in (select memberID from tbl_paypal where datepart(m, payment_date) = #session.month# and datepart(,payment_date) = #session.year# )) order by paid ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345501 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
+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 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 memberID not in (select memberID from tbl_paypal where datepart(m, payment_date) = #session.month# and datepart(,payment_date) = #session.year# )) order by paid ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345502 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
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 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 memberID not in (select memberID from tbl_paypal where datepart(m, payment_date) = #session.month# and datepart(,payment_date) = #session.year# )) order by paid ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345503 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Query Problem
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. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345507 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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. And, if you have any dynamic code that relies on your column list it will likely break if you end up adding columns to the table. Or if you have to hand off your code to another developer they can't simply look at your query statement and glean what's being returned without having access to the db or without dumping the query.columnList. There's just so many reasons why defining your column list is a good idea, and none where not defining it is. Not trying to preach, but this bit of convenience is just so unnecessary and has such potential for problems that it's not even worth considering in my opinion. It's just such a horrible habit. Anyway, as you were. :) /soapbox On Tue, Jun 21, 2011 at 5:15 PM, 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 * made me shudder. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345510 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Query Problem
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 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. And, if you have any dynamic code that relies on your column list it will likely break if you end up adding columns to the table. Or if you have to hand off your code to another developer they can't simply look at your query statement and glean what's being returned without having access to the db or without dumping the query.columnList. There's just so many reasons why defining your column list is a good idea, and none where not defining it is. Not trying to preach, but this bit of convenience is just so unnecessary and has such potential for problems that it's not even worth considering in my opinion. It's just such a horrible habit. Anyway, as you were. :) /soapbox On Tue, Jun 21, 2011 at 5:15 PM, 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 * made me shudder. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345511 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Query Problem
*thumbs up* On Tue, Jun 21, 2011 at 7:27 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: 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 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. And, if you have any dynamic code that relies on your column list it will likely break if you end up adding columns to the table. Or if you have to hand off your code to another developer they can't simply look at your query statement and glean what's being returned without having access to the db or without dumping the query.columnList. There's just so many reasons why defining your column list is a good idea, and none where not defining it is. Not trying to preach, but this bit of convenience is just so unnecessary and has such potential for problems that it's not even worth considering in my opinion. It's just such a horrible habit. Anyway, as you were. :) /soapbox On Tue, Jun 21, 2011 at 5:15 PM, 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 * made me shudder. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345512 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Quandary
That's perfect, thank you James! -Original Message- From: James Holmes [mailto:james.hol...@gmail.com] Sent: 23 May 2011 03:12 To: cf-talk Subject: Re: SQL Quandary It can be. Taking your last example: IF (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 select projected from tbl_stockItems where projected 10 else select projected from tbl_stockItems where projected 10 This can be written as: select projected from tbl_stockItems where ( (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 AND projected 10 ) OR ( (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) 1 AND projected 10 ) Since the subquery clause can only be true for one of the AND clauses, only one set of results will be returned. No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3654 - Release Date: 05/22/11 19:33:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344835 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
Simple and elegant. On Sun, May 22, 2011 at 10:11 PM, James Holmes james.hol...@gmail.comwrote: It can be. Taking your last example: IF (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 select projected from tbl_stockItems where projected 10 else select projected from tbl_stockItems where projected 10 This can be written as: select projected from tbl_stockItems where ( (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 AND projected 10 ) OR ( (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) 1 AND projected 10 ) Since the subquery clause can only be true for one of the AND clauses, only one set of results will be returned. -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 23 May 2011 09:29, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: because the intention is not a simple WHERE search expression. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344845 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
It would be easier to refer to this 3rd table if you supply the table.columnname so we know what were talking about. what is the relationship between this table and the other tables in the query, and from where does the value come that you want to compare it with. On Sun, May 22, 2011 at 4:46 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Scenario. I have a key query taking data from about 4 tables to produce a record set. I would like to be able to add a where clause to a column in a table not included in the query. Something like this much simplified breakdown: Three tables involved, two in the initial query, and a third table not in the query, but which has a parameter I need to use. Doing this by running the query to CF and then using CFIF's would be easy, but I'd to run all of the query in pure SQL. If it was done using CF it would like like this:- SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID where stockID 0 Cfif params.someField is thisValueand stockitems.Projected 0/cfif Hope I've explained myself clearly. I've tried using CASE, for example, but as soon as I add the params table it creates a cross join. The reason behind wanting to do it this way is for performance gain and to simplify use of the query when it gets to CF. Any ideas, please? Thanks in advance, Jenny Jenny Gavin-Wear Fast Track Online Tel: 01262 602013 http://www.fasttrackonline.co.uk/ No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344816 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
What Russ wrote regarding your parameters table. I've not got an SQL server box booted up to check, but the equivalent of the following sort of thing works fine in MySQL: SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID INNER JOIN dbo.tbl_parameters ON dbo.tbl_parameters.someKey = 'some value' where stockID 0 AND (dbo.tbl_parameters.someField != '#thisValue#' OR stockitems.Projected 0) As long as your parameters table join condition only ever matches one record, you should be fine. -- Pete Jordan Horus Web Engineering Ltd 90 Belvoir Street Hull HU5 3LR p: 01482 446471 m: 07973 725120 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344817 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Quandary
Hi Pete, Thanks for the reply ... If only it were that straightforward :) What I need to do is change the parameters of the search on the first tables depending on various conditions of a column in a table not otherwise included in the query. I have a feeling CASE will do it somehow, but I can't get the syntax. Jenny -Original Message- From: Pete Jordan [mailto:houseoffus...@skydancer.org.uk] Sent: 22 May 2011 19:00 To: cf-talk Subject: Re: SQL Quandary What Russ wrote regarding your parameters table. I've not got an SQL server box booted up to check, but the equivalent of the following sort of thing works fine in MySQL: SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID INNER JOIN dbo.tbl_parameters ON dbo.tbl_parameters.someKey = 'some value' where stockID 0 AND (dbo.tbl_parameters.someField != '#thisValue#' OR stockitems.Projected 0) As long as your parameters table join condition only ever matches one record, you should be fine. -- Pete Jordan Horus Web Engineering Ltd 90 Belvoir Street Hull HU5 3LR p: 01482 446471 m: 07973 725120 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344819 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Quandary
Hi Russ, Thanks for the reply ... 3 tables, for example. Table A, productsm Table B product options, linked on the stockID. Table C, some site parameters. So it's: Select stuff from Table and Table B, but if a value changes in a column in table C, run a different selection. IF table_C.column = this value, add something to the WHERE, but IF table_C.column = some other value, add something else to the WHERE. Like I say, doing it by returning all the results to CF and then using CFIF/where clauses works fine, I just want it all to happen in SQL, if possible. Jenny -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 22 May 2011 18:20 To: cf-talk Subject: Re: SQL Quandary It would be easier to refer to this 3rd table if you supply the table.columnname so we know what were talking about. what is the relationship between this table and the other tables in the query, and from where does the value come that you want to compare it with. On Sun, May 22, 2011 at 4:46 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Scenario. I have a key query taking data from about 4 tables to produce a record set. I would like to be able to add a where clause to a column in a table not included in the query. Something like this much simplified breakdown: Three tables involved, two in the initial query, and a third table not in the query, but which has a parameter I need to use. Doing this by running the query to CF and then using CFIF's would be easy, but I'd to run all of the query in pure SQL. If it was done using CF it would like like this:- SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID where stockID 0 Cfif params.someField is thisValueand stockitems.Projected 0/cfif Hope I've explained myself clearly. I've tried using CASE, for example, but as soon as I add the params table it creates a cross join. The reason behind wanting to do it this way is for performance gain and to simplify use of the query when it gets to CF. Any ideas, please? Thanks in advance, Jenny Jenny Gavin-Wear Fast Track Online Tel: 01262 602013 http://www.fasttrackonline.co.uk/ No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344820 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
if there is no relationship between tableC and the other tables, which seems to be the case, then there is no way to JOIN then thus I cannot see how you can directly influence the result set. If you are simply needing to change the query based on a single value, then you could do it as a stored procedure and then pass in the value from table C as a parameter to dynamically build your where clause using CASE statements this way. On Sun, May 22, 2011 at 8:37 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Hi Russ, Thanks for the reply ... 3 tables, for example. Table A, productsm Table B product options, linked on the stockID. Table C, some site parameters. So it's: Select stuff from Table and Table B, but if a value changes in a column in table C, run a different selection. IF table_C.column = this value, add something to the WHERE, but IF table_C.column = some other value, add something else to the WHERE. Like I say, doing it by returning all the results to CF and then using CFIF/where clauses works fine, I just want it all to happen in SQL, if possible. Jenny -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 22 May 2011 18:20 To: cf-talk Subject: Re: SQL Quandary It would be easier to refer to this 3rd table if you supply the table.columnname so we know what were talking about. what is the relationship between this table and the other tables in the query, and from where does the value come that you want to compare it with. On Sun, May 22, 2011 at 4:46 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Scenario. I have a key query taking data from about 4 tables to produce a record set. I would like to be able to add a where clause to a column in a table not included in the query. Something like this much simplified breakdown: Three tables involved, two in the initial query, and a third table not in the query, but which has a parameter I need to use. Doing this by running the query to CF and then using CFIF's would be easy, but I'd to run all of the query in pure SQL. If it was done using CF it would like like this:- SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID where stockID 0 Cfif params.someField is thisValueand stockitems.Projected 0/cfif Hope I've explained myself clearly. I've tried using CASE, for example, but as soon as I add the params table it creates a cross join. The reason behind wanting to do it this way is for performance gain and to simplify use of the query when it gets to CF. Any ideas, please? Thanks in advance, Jenny Jenny Gavin-Wear Fast Track Online Tel: 01262 602013 http://www.fasttrackonline.co.uk/ No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344824 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Quandary
Thanks Russ, I agree, I can't see a way around it using CASE or JOINS. The closest I can get to what I want is this: IF (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 select projected from tbl_stockItems where projected 10 else select projected from tbl_stockItems where projected 10 That SQL isn't exactly it, of course, but it demonstrates the aim. I have this nagging feeling it could be done in a better way, but at least this solution means the work is done by SQL and not after it gets to CF. I'm going to load up some test data and see if it actually runs faster then using CFIF's in CF after the initial query is run. Jenny -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 23 May 2011 00:14 To: cf-talk Subject: Re: SQL Quandary if there is no relationship between tableC and the other tables, which seems to be the case, then there is no way to JOIN then thus I cannot see how you can directly influence the result set. If you are simply needing to change the query based on a single value, then you could do it as a stored procedure and then pass in the value from table C as a parameter to dynamically build your where clause using CASE statements this way. On Sun, May 22, 2011 at 8:37 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Hi Russ, Thanks for the reply ... 3 tables, for example. Table A, productsm Table B product options, linked on the stockID. Table C, some site parameters. So it's: Select stuff from Table and Table B, but if a value changes in a column in table C, run a different selection. IF table_C.column = this value, add something to the WHERE, but IF table_C.column = some other value, add something else to the WHERE. Like I say, doing it by returning all the results to CF and then using CFIF/where clauses works fine, I just want it all to happen in SQL, if possible. Jenny -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 22 May 2011 18:20 To: cf-talk Subject: Re: SQL Quandary It would be easier to refer to this 3rd table if you supply the table.columnname so we know what were talking about. what is the relationship between this table and the other tables in the query, and from where does the value come that you want to compare it with. On Sun, May 22, 2011 at 4:46 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Scenario. I have a key query taking data from about 4 tables to produce a record set. I would like to be able to add a where clause to a column in a table not included in the query. Something like this much simplified breakdown: Three tables involved, two in the initial query, and a third table not in the query, but which has a parameter I need to use. Doing this by running the query to CF and then using CFIF's would be easy, but I'd to run all of the query in pure SQL. If it was done using CF it would like like this:- SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID where stockID 0 Cfif params.someField is thisValueand stockitems.Projected 0/cfif Hope I've explained myself clearly. I've tried using CASE, for example, but as soon as I add the params table it creates a cross join. The reason behind wanting to do it this way is for performance gain and to simplify use of the query when it gets to CF. Any ideas, please? Thanks in advance, Jenny Jenny Gavin-Wear Fast Track Online Tel: 01262 602013 http://www.fasttrackonline.co.uk/ No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344826 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
well don't forget you have query of queries, this works really well if you can cache the original query, then it is really fast. On Mon, May 23, 2011 at 12:54 AM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Thanks Russ, I agree, I can't see a way around it using CASE or JOINS. The closest I can get to what I want is this: IF (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 select projected from tbl_stockItems where projected 10 else select projected from tbl_stockItems where projected 10 That SQL isn't exactly it, of course, but it demonstrates the aim. I have this nagging feeling it could be done in a better way, but at least this solution means the work is done by SQL and not after it gets to CF. I'm going to load up some test data and see if it actually runs faster then using CFIF's in CF after the initial query is run. Jenny -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 23 May 2011 00:14 To: cf-talk Subject: Re: SQL Quandary if there is no relationship between tableC and the other tables, which seems to be the case, then there is no way to JOIN then thus I cannot see how you can directly influence the result set. If you are simply needing to change the query based on a single value, then you could do it as a stored procedure and then pass in the value from table C as a parameter to dynamically build your where clause using CASE statements this way. On Sun, May 22, 2011 at 8:37 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Hi Russ, Thanks for the reply ... 3 tables, for example. Table A, productsm Table B product options, linked on the stockID. Table C, some site parameters. So it's: Select stuff from Table and Table B, but if a value changes in a column in table C, run a different selection. IF table_C.column = this value, add something to the WHERE, but IF table_C.column = some other value, add something else to the WHERE. Like I say, doing it by returning all the results to CF and then using CFIF/where clauses works fine, I just want it all to happen in SQL, if possible. Jenny -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 22 May 2011 18:20 To: cf-talk Subject: Re: SQL Quandary It would be easier to refer to this 3rd table if you supply the table.columnname so we know what were talking about. what is the relationship between this table and the other tables in the query, and from where does the value come that you want to compare it with. On Sun, May 22, 2011 at 4:46 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Scenario. I have a key query taking data from about 4 tables to produce a record set. I would like to be able to add a where clause to a column in a table not included in the query. Something like this much simplified breakdown: Three tables involved, two in the initial query, and a third table not in the query, but which has a parameter I need to use. Doing this by running the query to CF and then using CFIF's would be easy, but I'd to run all of the query in pure SQL. If it was done using CF it would like like this:- SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID where stockID 0 Cfif params.someField is thisValueand stockitems.Projected 0/cfif Hope I've explained myself clearly. I've tried using CASE, for example, but as soon as I add the params table it creates a cross join. The reason behind wanting to do it this way is for performance gain and to simplify use of the query when it gets to CF. Any ideas, please? Thanks in advance, Jenny Jenny Gavin-Wear Fast Track Online Tel: 01262 602013 http://www.fasttrackonline.co.uk/ No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344828 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
Why not just add the subquery in as part of the where clause for each type of record you want? On Monday, 23 May 2011, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Thanks Russ, I agree, I can't see a way around it using CASE or JOINS. -- -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344829 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Quandary
Sure, I use QofQ a lot. What I wanted from this solution was to reduce the number of records being returned by the query before it even got to CF. -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 23 May 2011 01:22 To: cf-talk Subject: Re: SQL Quandary well don't forget you have query of queries, this works really well if you can cache the original query, then it is really fast. No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344830 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Quandary
because the intention is not a simple WHERE search expression. The idea is to change the search expression completely depending on a value in a table not in the actual query. -Original Message- From: James Holmes [mailto:james.hol...@gmail.com] Sent: 23 May 2011 01:47 To: cf-talk Subject: Re: SQL Quandary Why not just add the subquery in as part of the where clause for each type of record you want? On Monday, 23 May 2011, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Thanks Russ, I agree, I can't see a way around it using CASE or JOINS. -- -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344831 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
It can be. Taking your last example: IF (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 select projected from tbl_stockItems where projected 10 else select projected from tbl_stockItems where projected 10 This can be written as: select projected from tbl_stockItems where ( (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 AND projected 10 ) OR ( (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) 1 AND projected 10 ) Since the subquery clause can only be true for one of the AND clauses, only one set of results will be returned. -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 23 May 2011 09:29, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: because the intention is not a simple WHERE search expression. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344832 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL selecting distinct items by date?
You could add the fields you need to your query then do a GROUP on the CFOUTPUT. That will then be able to weed through the duplicates caused by differences in the data. Also could/should eliminate the need to add queries within your loop. On Sun, Feb 13, 2011 at 1:21 PM, wabba must...@wabba.net wrote: Here's what I ended up with SELECT distinct TOP 5 pc.nCategoryID, c.sCategory, YEAR(p.dPartDate) AS theyear, MONTH(p.dPartDate) AS themonth, DAY(p.dPartDate) AS theday FROM Category c INNER JOIN PartCat pc ON pc.nCategoryID=c.nCategoryID inner join part p on pc.npartid=p.npartid order by theyear DESC, themonth desc, theday desc Adding any part-specific fields to the select list results in duplicate CategoryIDs, and I don't fully understand why the DISTINCT can't remain exclusive to the CategoryID field, but so far I think it'll do what's needed. The results can be looped over to get more detail which means subqueries, but it functions. Combining TOP and DISTINCT with GROUP/ORDER remains somewhat uncertain... -Original Message- From: John M Bliss [mailto:bliss.j...@gmail.com] Sent: Sunday, February 13, 2011 10:55 AM To: cf-talk Subject: Re: SQL selecting distinct items by date? SELECT TOP 5 DISTINCT pivot.CategoryID, Category.Name, YEAR(Item.DateAdded) AS theyear, MONTH(Item.DateAdded) AS themonth, DAY(Item.DateAdded) AS theday On Sun, Feb 13, 2011 at 12:51 PM, wabba must...@wabba.net wrote: More on SQL-topic than CF (MS SQL), but hopefully is an easy one. I have items with a datetime field that stores when the items are added to the DB. There is a pivot table that links items to categories. I'm trying to pull out the top 5 unique categories with the newest-added items. This is what I'm trying to do even though the syntax doesn't work: SELECT TOP 5 DISTINCT pivot.CategoryID, Category.Name, Item.DateAdded FROM Category INNER JOIN pivot ON Category.CategoryID = pivot.CategoryID INNER JOIN Item ON pivot.ItemID = Item.ItemID ORDER BY Item.DateAdded DESC I can get close, but the DateAdded fields are always unique (sometimes only seconds apart, but unique) so no matter what I do it always thinks the result records are unique and won't give me unique CategoryIDs. Ideas? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342193 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL selecting distinct items by date?
Sorry I am a little late in the reply on this one, but did you try using the MAX function. SELECT DISTINCT TOP 5 pivot.CategoryID, Category.Name, MAX(Item.DateAdded) FROM Category INNER JOIN pivot ON Category.CategoryID = pivot.CategoryID INNER JOIN Item ON pivot.ItemID = Item.ItemID GROUP BY pivot.CategoryID, Category.Name ORDER BY MAX(Item.DateAdded) DESC ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342200 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL selecting distinct items by date?
SELECT TOP 5 DISTINCT pivot.CategoryID, Category.Name, YEAR(Item.DateAdded) AS theyear, MONTH(Item.DateAdded) AS themonth, DAY(Item.DateAdded) AS theday On Sun, Feb 13, 2011 at 12:51 PM, wabba must...@wabba.net wrote: More on SQL-topic than CF (MS SQL), but hopefully is an easy one. I have items with a datetime field that stores when the items are added to the DB. There is a pivot table that links items to categories. I'm trying to pull out the top 5 unique categories with the newest-added items. This is what I'm trying to do even though the syntax doesn't work: SELECT TOP 5 DISTINCT pivot.CategoryID, Category.Name, Item.DateAdded FROM Category INNER JOIN pivot ON Category.CategoryID = pivot.CategoryID INNER JOIN Item ON pivot.ItemID = Item.ItemID ORDER BY Item.DateAdded DESC I can get close, but the DateAdded fields are always unique (sometimes only seconds apart, but unique) so no matter what I do it always thinks the result records are unique and won't give me unique CategoryIDs. Ideas? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342179 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL selecting distinct items by date?
Here's what I ended up with SELECT distinct TOP 5 pc.nCategoryID, c.sCategory, YEAR(p.dPartDate) AS theyear, MONTH(p.dPartDate) AS themonth, DAY(p.dPartDate) AS theday FROM Category c INNER JOIN PartCat pc ON pc.nCategoryID=c.nCategoryID inner join part p on pc.npartid=p.npartid order by theyear DESC, themonth desc, theday desc Adding any part-specific fields to the select list results in duplicate CategoryIDs, and I don't fully understand why the DISTINCT can't remain exclusive to the CategoryID field, but so far I think it'll do what's needed. The results can be looped over to get more detail which means subqueries, but it functions. Combining TOP and DISTINCT with GROUP/ORDER remains somewhat uncertain... -Original Message- From: John M Bliss [mailto:bliss.j...@gmail.com] Sent: Sunday, February 13, 2011 10:55 AM To: cf-talk Subject: Re: SQL selecting distinct items by date? SELECT TOP 5 DISTINCT pivot.CategoryID, Category.Name, YEAR(Item.DateAdded) AS theyear, MONTH(Item.DateAdded) AS themonth, DAY(Item.DateAdded) AS theday On Sun, Feb 13, 2011 at 12:51 PM, wabba must...@wabba.net wrote: More on SQL-topic than CF (MS SQL), but hopefully is an easy one. I have items with a datetime field that stores when the items are added to the DB. There is a pivot table that links items to categories. I'm trying to pull out the top 5 unique categories with the newest-added items. This is what I'm trying to do even though the syntax doesn't work: SELECT TOP 5 DISTINCT pivot.CategoryID, Category.Name, Item.DateAdded FROM Category INNER JOIN pivot ON Category.CategoryID = pivot.CategoryID INNER JOIN Item ON pivot.ItemID = Item.ItemID ORDER BY Item.DateAdded DESC I can get close, but the DateAdded fields are always unique (sometimes only seconds apart, but unique) so no matter what I do it always thinks the result records are unique and won't give me unique CategoryIDs. Ideas? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342181 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL query question
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! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341821 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
On 2/1/2011 9:41 AM, Aaron Renfroe wrote: Hello All! SELECT DISTINCT PartNumber FROM GriffinDataRevised INNER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number WHERE Top200.part_number = GriffinDataRevised.PartNumber INNER JOIN will enforce a filter that will only return records in a given 'partnumber' is in BOTH tables. The return of 98 recrods would indicate that there are only 98 values of 'partnumber' that are in both tables. If that is expected and known behavior then what you want is an OUTER JOIN that says return all records from one table PLUS any records from the other table IF they match. IE FROM GriffinDataRevised LEFT OUTER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number This will return all the records from the table on the LEFT side of the JOIN 'GriffinDataRevised' OR FROM GriffinDataRevised RIGHT OUTER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number This will return all the recrods from the table on the RIGHT side of the JOIN, 'Top200' Some database management systems support the FULL OUTER JOIN that will return unmatched records from BOTH sides of the join. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341793 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
a standard join looks like this.. select foo from a join b on a.id = b.id So yours would look something like SELECT PartNumber FROM GriffinDataRevised d JOIN Top200 t on d.partnumber = t.part_number You're not technically doing a join, you're doing a sub query. On Tue, Feb 1, 2011 at 11:41 AM, Aaron Renfroe mossma...@gmail.com wrote: Hello All! I'm trying my first Join of two tables and not having the best of luck... One table holds just a part number off our top 200 products, the second table holds a part number along with all the information that accompanies that part. I'm trying to pull back all the data from the information table that has a matching part number in my top 200 table. Here are a few ways i have tried : SELECT DISTINCT PartNumber FROM GriffinDataRevised WHERE PartNumber IN (SELECT part_number FROM Top200) -- This one brings back 15k results with tons of duplicates SELECT * FROM GriffinDataRevised WHERE PartNumber = (SELECT Part_Number FROM Top200 WHERE Top200.part_number = 'GriffinDataRevised.PartNumber') -- This one, no results shown SELECT DISTINCT PartNumber FROM GriffinDataRevised INNER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number WHERE Top200.part_number = GriffinDataRevised.PartNumber -- This one returns 98 results when there should be 200. I'm still trying to confirm if there are duplicate PN's in the list. Thank you, Aaron ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341794 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
Hello Ian and thank you! I have tried both the left and right joins, the RIGHT join brought back the 15k results again, the LEFT join was bringing back so many that i killed the browser before it hurt something :) JOINS: SELECT * FROM GriffinDataRevised LEFT OUTER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number --This one seemed like a infinite loop was happening, crashed browser from results. SELECT * FROM GriffinDataRevised RIGHT OUTER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number 15k results again So if i know that its true that the Top200 table has 200 distinct part numbers and that the information table may have all 200 part numbers why how would i accomplish just getting the matching results for the 200 part numbers in the GriffinData table? It may be possible that the 98 results are the only parts that exist in the larger information table. But my query was still running wrong, correct? Thanks! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341795 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
On 2/1/2011 10:22 AM, Aaron Renfroe wrote: Hello Ian and thank you! But my query was still running wrong, correct? Not necessarily, maybe your data is wrong. You may need to provide some more description on what data is in each of these tables and how you are trying to utilize it before we can help much more. IS there a ONE TO ONE or a ONE TO MANY relationship between Top200 and GriffinDataRevised? If ONE TO MANY, do you not want all the record from the MANY side? IF not all the records, which record of the MANY is the one that you want? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341796 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
I guess it could be both. While some part numbers can fit multiple years, makes, and models of a vehicle others just fit one. The top 200 are our best selling radiators. In theory, i want to hit the master table, pull out all the information on the radiator based on the part number being supplied from the top 200. Here is a query with all the fields from the Master table, and the Top 200 table has nothing but a part number: SELECT GriffinDataRevised.PartNumber, GriffinDataRevised.Make, GriffinDataRevised.Model, GriffinDataRevised.Year, GriffinDataRevised.Engine, GriffinDataRevised.Edition, GriffinDataRevised.TransCooler, GriffinDataRevised.OilCooler, GriffinDataRevised.HorsePower, GriffinDataRevised.Comments, GriffinDataRevised.Outlets, GriffinDataRevised.TubeSize, GriffinDataRevised.Rows, GriffinDataRevised.CoreSize, GriffinDataRevised.Revision FROM GriffinDataRevised INNER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.part_number WHERE GriffinDataRevised.PartNumber = Top200.part_number ORDER BY Make DESC Does that help? Thank you so much for bearing with me. On another not, the query that brought back 98 results may have been working right, i found another 78 records in another table for race car radiators, i'm almost guessing that the last few are in another table that would make the total 200 records. But i'm now getting an error that the part_number field is ambiguous. Ugh Thank you! On Tue, Feb 1, 2011 at 1:47 PM, Ian Skinner h...@ilsweb.com wrote: On 2/1/2011 10:22 AM, Aaron Renfroe wrote: Hello Ian and thank you! But my query was still running wrong, correct? Not necessarily, maybe your data is wrong. You may need to provide some more description on what data is in each of these tables and how you are trying to utilize it before we can help much more. IS there a ONE TO ONE or a ONE TO MANY relationship between Top200 and GriffinDataRevised? If ONE TO MANY, do you not want all the record from the MANY side? IF not all the records, which record of the MANY is the one that you want? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341797 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
On 2/1/2011 11:35 AM, Aaron M Renfroe wrote: But i'm now getting an error that the part_number field is ambiguous. Ugh That just means that the field is in both (multiple) tables and the database wants you to tell it which table you want to use to get the value for this column to use in this record set. Just prepend that column name with a table name. You seem to be doing that in all the fields except the 'Make' field in the ORDER BY clause. Just add a table name to that field as well. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341798 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
On 2/1/2011 11:35 AM, Aaron M Renfroe wrote: On another note, the query that brought back 98 results may have been working right, i found another 78 records in another table for race car radiators, i'm almost guessing that the last few are in another table that would make the total 200 records. Then you are probably looking at three SELECT statements to return all the desired data for the record set. My first choice would probably to do all three SELECTS in a single query with a UNION clause to combine them into one record set. But this is not the only option available. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341799 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
Thank you! I will research the UNION Clause. You have been a great help! On Tue, Feb 1, 2011 at 3:13 PM, Ian Skinner h...@ilsweb.com wrote: On 2/1/2011 11:35 AM, Aaron M Renfroe wrote: On another note, the query that brought back 98 results may have been working right, i found another 78 records in another table for race car radiators, i'm almost guessing that the last few are in another table that would make the total 200 records. Then you are probably looking at three SELECT statements to return all the desired data for the record set. My first choice would probably to do all three SELECTS in a single query with a UNION clause to combine them into one record set. But this is not the only option available. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341800 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL query question
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 select clause). In the past, what has happened is I'll have some query with at least one * in the select statement followed by one or more columns and I add a column to whatever I was selecting * from. If the query stays the same, it uses the same result-set template (again, im making up words - no idea what its really called) It doesn't know to check the underlying structure of the table. But the newly added column gets returned by the db - offsetting all the columns in the rest of the result set. So as crazy as it sounds - just change something in the sql, and give it a try. It doesn't have to actually change anything about what the query does - adding ,getdate() as helloworld will fix it - and then you can undo the change after one successful run. I assume this is something on CF's side - as restarting the CF service also fixes it. On Tue, Feb 1, 2011 at 4:23 PM, Debbie Morris deb_mor...@comcast.netwrote: 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. Here's the query (obviously the person that originally wrote it is in the 'the fewer characters, the better' camp): SELECT p.*, pt.*, pm.Type AS mtype, s.fname AS sfname, s.lname AS slname, pa.DateActive AS PenAmountDate, pa.MonthlyAmount AS PenAmt, pa.Note AS PenAmtNote, s.SSN AS sssn, s.DOB AS sdob, spm.Type AS smt FROM PensionerMedicalType spm INNER JOIN PensionerSpouse s ON spm.MedicalID = s.MedicalType RIGHT OUTER JOIN PensionersActive p INNER JOIN PensionerType pt ON p.PensionerType = pt.PensionerTID LEFT OUTER JOIN PensionAmounts pa ON p.PenActID = pa.PenID LEFT OUTER JOIN PensionerMedicalType pm ON p.MedicalType = pm.MedicalID ON s.SpouseID = p.Spouse WHERE (p.PenActID = cfqueryparam cfsqltype=cf_sql_integer value=#id# AND pa.active = 1) As soon as I add a column named 'lifeInsType' to the PensionersActive (p) table, I get the following error when the same query runs: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type. What am I overlooking? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341803 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL query question
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. Now the database structure does not match the cached structure stored by the database driver and this type of disconnect occurs. The solution: Replace those p.* and pt.* short cuts with that actual columns you need in your record set. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341804 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL query question
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 might eliminate the caching of the database columns and types. But I have never tried it, having long ago accepted the better practice of not using * in my SQL. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341805 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL query question
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 query. The added bonus is that there's no extra overhead associated with returning columns you aren't using. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341806 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL query question
I'm 100% with you guys on this as well. Replacing the hundreds of select * from all the existing code here is one of my seemingly never ending tasks. I should have addressed that first before attempting to add anything else to the mix. I'm done installing my Windows updates for the evening, 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%. 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 query. The added bonus is that there's no extra overhead associated with returning columns you aren't using. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341807 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL 2008 standard vs. web
They're close, but not quite the same. The primary difference is the licensing . The other differences are around mirroring (web can only serve as a witness), publishing (web can only subscribe), and perf (web does not come with SQL Profiler). There are more differences when you get out of the SQL Engine and into SSIS, SSAS, and SSRS. Full comparison of all editions here: http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx Regards -- Russ Michaels www.cfmldeveloper.com - free CFML hosting for developers my blog: http://russ.michaels.me.uk/ skype: russmichaels ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340475 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL 2008 standard vs. web
There are a few different versions of SQL 2008. One of them is billed as SQL server web which is focused on being the backend for a data driven website. Has anyone used this and have they had any problems? The engine itself should be essentially the same between editions, the main differences will be in the cost and licensing (how many CPUs, memory it can use, etc.). My understanding is that the Web edition is targeted at larger web hosting companies that need to offer SQL server as a back-end, or for larger single customers who have a large web infrastructure. It is only available under a volume licensing plan, so if you just want one copy you're likely better off purchasing standard (assuming that SQL Express doesn't meet your needs for free). -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340476 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL 2008 standard vs. web
There are a few different versions of SQL 2008. One of them is billed as SQL server web which is focused on being the backend for a data driven website. Has anyone used this and have they had any problems? Any real differences between this and SQL 2008 standard? I'm inclined to go with the web version based on what I've read but first hand feedback is best. If you're just setting up a standalone web application, the Web edition will work fine. If you need replication, mirroring, etc, you need at least Standard. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340477 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL 2008 standard vs. web
A primary question to answer is whether you need the business intelligence tools that are part of the SQL Server platform, notably SSIS, which is not available in the Web edition. I use SSIS and SSRS extensively, so I have to use at least the Standard edition. You can always start with a cheap version and switch to the more expensive version later on, unless there is a feature of the Standard version that you know is essential, such as the disaster recovery features that only exist in the more expensive versions. -Mike Chabot On Wed, Jan 5, 2011 at 2:16 PM, Michael Dinowitz mdino...@houseoffusion.com wrote: There are a few different versions of SQL 2008. One of them is billed as SQL server web which is focused on being the backend for a data driven website. Has anyone used this and have they had any problems? Any real differences between this and SQL 2008 standard? I'm inclined to go with the web version based on what I've read but first hand feedback is best. Thanks Michael ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340485 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Azure and Coldfusion 9
This is actually incredibly easy to deal with this scenario, you just have to check for the existence of the query variable first. You would normally check the recordcount 0 anyway so you don't output nothing, so it really isn't any more work. That would be true except when recordcount isn't populated, the query itself may not be populated and, depending on why, will error out. Since isQuery() will error out if the var is unset after CFQUERY, that is not very useful either. CFQUERY name=myQry ... declare @tmp table (col1 int) -- step 1 insert into @tmp (col1) select sub.* from (select 1 as col1 where 1=2) sub -- step 2 select * from @tmp where 1=2 -- step 3 /CFQUERY CFIF isQuery(myQry) CFDUMP var=#myQry# CFDUMP var=#myQry.recordCount# CFDUMP var=#myQry.columnlist# /CFIF This will (still) bomb using MS JDBC 2.0/3.0 drivers against SQL Azure or SQL Server 2008 (SQL2K8). Not so using MS SQL Server (which is actually a MM JDBC driver in CF) against SQL2K8. Sean ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339639 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Azure and Coldfusion 9
So use isDefined(Variables.queryname) first. -Original Message- From: Sean Henderson [mailto:shender...@followup.net] Sent: Tuesday, November 30, 2010 4:14 PM To: cf-talk Subject: Re: SQL Azure and Coldfusion 9 This is actually incredibly easy to deal with this scenario, you just have to check for the existence of the query variable first. You would normally check the recordcount 0 anyway so you don't output nothing, so it really isn't any more work. That would be true except when recordcount isn't populated, the query itself may not be populated and, depending on why, will error out. Since isQuery() will error out if the var is unset after CFQUERY, that is not very useful either. CFQUERY name=myQry ... declare @tmp table (col1 int) -- step 1 insert into @tmp (col1) select sub.* from (select 1 as col1 where 1=2) sub -- step 2 select * from @tmp where 1=2 -- step 3 /CFQUERY CFIF isQuery(myQry) CFDUMP var=#myQry# CFDUMP var=#myQry.recordCount# CFDUMP var=#myQry.columnlist# /CFIF This will (still) bomb using MS JDBC 2.0/3.0 drivers against SQL Azure or SQL Server 2008 (SQL2K8). Not so using MS SQL Server (which is actually a MM JDBC driver in CF) against SQL2K8. Sean ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339640 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Azure and Coldfusion 9
Rather that CF9 and CFQUERY handle it better To my mind, if CFQUERY does not error out, then the queryname, recordcount and column list in all cases should be set, regardless of driver or target database. For those of us with sprawling apps to maintain (1,000s of .cfm files) and where a global edit across such code base is not possible, patching this is headache. So use isDefined(Variables.queryname) first. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339642 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Azure and Coldfusion 9
Sean, This is actually incredibly easy to deal with this scenario, you just have to check for the existence of the query variable first. You would normally check the recordcount 0 anyway so you don't output nothing, so it really isn't any more work. Russ -Original Message- From: Sean Henderson [mailto:shender...@followup.net] Sent: 26 November 2010 17:07 To: cf-talk Subject: SQL Azure and Coldfusion 9 FYI, SQL Azure is not part of the support matrix for ColdFusion. http://www.adobe.com/products/coldfusion/pdfs/cf9_support_matrix_4_ue.pdf There's an issue with SQL Azure where certain types of queries that when returning zero records, will not populate recordcount or set the query variable at all. After contacting Adobe about a patch for this for ColdFusion 9, Adobe indicated that SQL Azure is not supported and closed the support ticket. I have a ticket opon on the Microsoft side, and still trying to identify whether it is the JDBC driver itself or something specific with SQL Azure. For now, if considering SQL Azure, likely not an option for production at this time unless willing to write overly defensive code. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339544 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL 139 transaction error
Since MyISAM is a non-transactional storage engine, the error doesn't make too much sense to me. Are you sure your migration from InnoDB was successful, and that you are infact using MyISAM and not InnoDB on this table? -- Pete Freitag http://foundeo.com/ - ColdFusion Consulting Products http://petefreitag.com/ - My Blog http://hackmycf.com - Is your ColdFusion Server Secure? On Fri, Oct 15, 2010 at 10:34 AM, cfcom cf...@aceligent.com wrote: Is anyone familiar with MySql 139 transaction storage error. I've switched the engine from InnoDB to MyISAM but am still seeing issues. Am running MySql 5. Am wondering if I should move from open source to a different DB - Any suggestions or insight would be most appreciated. TIA ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338237 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL 139 transaction error
Error 139 appears to be some kind of bug http://bugs.mysql.com/bug.php?id=10035 Its occurring with a legacy form. Originally error would throw if upload attachment. Changed InnoDB to MyISAM and upload went fine Now if modify text box in form throws same error. Considering switching to MSSQL if cant resolve Any thoughts would be appreciated -Original Message- From: Pete Freitag [mailto:p...@foundeo.com] Sent: 2010-10-15 13:23 To: cf-talk Subject: Re: SQL 139 transaction error Since MyISAM is a non-transactional storage engine, the error doesn't make too much sense to me. Are you sure your migration from InnoDB was successful, and that you are infact using MyISAM and not InnoDB on this table? -- Pete Freitag http://foundeo.com/ - ColdFusion Consulting Products http://petefreitag.com/ - My Blog http://hackmycf.com - Is your ColdFusion Server Secure? On Fri, Oct 15, 2010 at 10:34 AM, cfcom cf...@aceligent.com wrote: Is anyone familiar with MySql 139 transaction storage error. I've switched the engine from InnoDB to MyISAM but am still seeing issues. Am running MySql 5. Am wondering if I should move from open source to a different DB - Any suggestions or insight would be most appreciated. TIA ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338240 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Server Data Archival - my solution
I find this intriguing as well. Almost like a poor mans historical archive system. A generator for the triggers would be cool also based on Illidium PU-36 -- Dan O'Keefe On Fri, Jul 30, 2010 at 9:08 AM, Pete Ruckelshaus pruckelsh...@gmail.com wrote: Feel free to pass on any enhancements or improvements! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335921 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Server Data Archival - my solution
Pete,
This is awesome! I'm going to consider using it for certain tables that
we have problems with. I might suggest that you add in the user name
and/or ip that made the change. That can help if you need to find out
who made specific changes!
Thanks,
Steve
-Original Message-
From: Pete Ruckelshaus [mailto:pruckelsh...@gmail.com]
Sent: Friday, July 30, 2010 10:20 AM
To: cf-talk
Subject: SOT: SQL Server Data Archival - my solution
I know a lot of time people (including myself) come here with questions,
and
don't have many opportunities to offer solutions to share. I had a need
to
store a record of changes to records in database tables for a content
management system. I wanted to keep things simple, without requiring a
bunch of additional code to my CF codebase, and I didn't want to make
SQL
Server management more complicated than it needed to be by adding a ton
of
tables.
Anyway, in a nutshell, I've got an archive table that stores changed
records
as an XML object in a field. An insert, update, delete trigger grabs
values
from the inserted or deleted tables, packs that info up, and saves it to
the
archive table. The beauty of this is its relative simplicity -- the
archive
table can store data from any table in the database.
Here's the write-up. I'm sure it's not perfect -- I'm far from an SQL
Server expert - but it's going to work very well for my needs.
Process - single archive table storing XML
The archive table would look something like this:
*tblArchive*
uid uniqueidentifier DEFAULT NEWID(),
timestamp datetime DEFAULT GETDATE(),
sourceTable varchar(100),
sourceID int,
action varchar(10),
xData xml
* UID would be a unique identifier for this archive table.
* Timestamp would set the time of archive and would allow to
sort
revisions based upon when they happened.
* sourceTable would be the name of the table that the data is
coming
from.
* sourceID would be the record identifier (presumably the
primary
key) of that record from the original table.
* Action would be whatever SQL action was performed on that
record,
i.e. insert, update, delete
* xData would be the XML object that stores the record that is
being
changed.
Assuming a fictitious (and rather simple) table named tblUsers with
the
following structure:
*tblUsers*
id (int, PK, ident, autoincrement),
fname (varchar(20)),
lname (varchar(30)),
employeeid (int),
extension (int)
Archive Trigger for Inserts, Updates and Deletes
For each table that is to have archived data, run the following trigger,
modifying table names where necessary:
CREATE TRIGGER[tgrArchiveRecord]
ON[tblUsers]
FOR INSERT, UPDATE, DELETE
AS
IF@@rowcount = 0
RETURN
DECLARE @table varchar(100);
DECLARE @sourceid int;
DECLARE @action varchar(10);
SET @table = 'tblUsers';
IF EXISTS (SELECT * FROM DELETED)
BEGIN
IFEXISTS (SELECT * FROM DELETED) AND
EXISTS (SELECT * FROM INSERTED)
BEGIN
SET @sourceid = (SELECT id FROM inserted);
SET @action = 'update';
INSERT INTO tblArchive(sourceTable, sourceid,
action, xData)
SELECT @table, id, @action, (SELECT *
FROM deleted AS record
WHERE deleted.id = record.id FOR XML AUTO)
FROM deleted;
RETURN
END
SET @sourceid = (SELECT id FROM deleted);
SET @action = 'delete';
INSERT INTO tblArchive (sourceTable, sourceid, action,
xData)
SELECT @table, id, @action, (SELECT *
FROM deleted AS record
WHERE deleted.id = record.id FOR XML AUTO)
FROM deleted;
END
ELSE
BEGIN
SET @sourceid = (SELECT id FROM inserted);
SET @action = 'insert';
INSERT INTO tblArchive (sourceTable, sourceid, action,
xData)
SELECT @table, id, @action, (SELECT *
FROM inserted AS record
WHERE inserted.id = record.id FOR XML
AUTO)
FROM inserted;
END
GO
Retrieving Data From Archive
The likely scenario for retrieving data from the archive table is to
either
display a history of a record, or to present previous changes so that a
user
can roll back changes to a previous version. Retrieving data from the
Archive table's XML column is fairly straightforward:
SELECT NULL as uid, u.id AS id, u.fname, u.lname, u.employeeid,
u.extension, getdate() AS [timestamp]
FROMtblUsers u
UNION
SELECT A.uid,
A.sourceID AS id,
A.xData.value('(/*/@fname)[1]', 'varchar(20)') AS fname,
A.xData.value('(/*/@lname)[1]', 'varchar(30)') AS lname,
Re: SQL Server Data Archival - my solution
Steve, to keep things simple(r), I would store that info at the source table level. Since it's a web site, there's a single generic user account at the database level, but I do track user and IP at the CF application level in the table that specific data is stored...it's just not shown in the tblUsers example table. Pete On Fri, Jul 30, 2010 at 10:26 AM, DURETTE, STEVEN J (ATTASIAIT) sd1...@att.com wrote: Pete, This is awesome! I'm going to consider using it for certain tables that we have problems with. I might suggest that you add in the user name and/or ip that made the change. That can help if you need to find out who made specific changes! Thanks, Steve ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335886 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
