SQL Quandary
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:344815 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: scoping
From my [private] point of view, the scoping of 100% variables is a programming extremism. When some scope is implied, there is no reason to scope variables, unless it is absolutely necessary, like in CFQUERY-related loops, or using functions with side effect(s). Or if this is a policy of your organization (strange places may have strange rules). This is similar to requiring the use of parenthesizes in arithmetic expressions in all cases, instead of relying on defined functions precedence rules. Or to prohibit the use of a++, allowing only the use of a=a+1 in C code for readability reasons. Moreover, I always thought that for a default scope, there is no performance gain, if I use fully qualified references, because the default namespace is always checked first, regradless of what some people say that, if you don't use scoping, some overhead is aways involved. So, I wrote a simple test below. No functions, no CFCs, just plain page code: cfset a=1 cfset b=2 !--- This is how most people would write this --- cfset c=0 cfset stTime=GetTickCount() cfloop index=i from=1 to=10 cfset c=a+b /cfloop cfoutputNo scope: c=#c#, time=#GetTickCount()-stTime#br/cfoutput !--- This suppose to be more readable and safe --- cfset c=0 cfset stTime=GetTickCount() cfloop index=i from=1 to=10 cfset variables.c=variables.a+variables.b /cfloop cfoutputScope: c=#c#, time=#GetTickCount()-stTime#br/cfoutput I would expect exactly the same or, at least, very close results. But, results are so unexpected, that I was clicking the Refresh button of my browser for 10 minutes, like crazy. Non-scoped version ALWAYS runs 5-15 times FASTER than the scoped version! This is on CF8, 32-bit, 4 CPU Dell Server, Windows 2003. Could somebody run this on CF9 32/64 bit? Any ideas, how it can be? Remembering scope precedence rules in CF8, I wrote another test for a function with a side effect. Therefore, the explicit scoping of external references suppose to increase the performance. Right? So, this is the test: cffunction name=test1 cfset var i=0 cfset var stTime=GetTickCount() cfloop index=i from=1 to=10 cfset variables.c=a+b /cfloop cfoutputFunction no scope: #GetTickCount()-stTime#br/cfoutput /cffunction cffunction name=test2 cfset var i=0 cfset var stTime=GetTickCount() cfloop index=i from=1 to=10 cfset variables.c=variables.a+variables.b /cfloop cfoutputFunction scope: #GetTickCount()-stTime#br/cfoutput /cffunction cfset a=1 cfset b=2 cfset c=0 cfset test1() cfoutputc=#c#br/cfoutput cfset c=0 cfset test2() cfoutputc=#c#br/cfoutput Non-scoped version ALWAYS runs 2-3 times FASTER than the scoped one. So, at least on my server, the claim that scoping not only increases readability, but also gives a performance gain looks like not entirely accurate (c). So, scope as less, as you can??! ~| 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:344818 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
Scrate it - 301
Ok - figures I always ask a question and find the answer within SECONDS after I ask. I would help to remove the cflocation url=#SomeNewURL# addtoken=false above the redirect I was working on. It's working now... Paul Giesenhagen Quill Design http://www.quilldesign.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:344821 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
301 Redirects - showing as 302's
I'm doing the following: cfheader statuscode=301 statustext=Moved permanently cfheader name=Location value=#SomeNewURL# It produces a 302 status code So I tried cfheader statuscode=301 statustext=Moved permanently#Chr(13)##Chr(10)#Location:#SomeNewURL# cfheader name=Location value=#SomeNewURL# Finally I tried cflocation url=#SomeNewURL# statuscode=301 addtoken=false ALL produced 302 status codes .. has anyone figured this out - did some searching without luck. Thanks in advance Paul Giesenhagen Quill Design http://www.quilldesign.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:344822 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: scoping
Non-scoped version ALWAYS runs 5-15 times FASTER than the scoped version! This is on CF8, 32-bit, 4 CPU Dell Server, Windows 2003. Could somebody run this on CF9 32/64 bit? Exactly the same here under CF9. I inverted the two loops ie: scope first and non-scope second, just in case, and the results are the same : about 250 for scoped and 15 for non-scoped. Any ideas, how it can be? This is weird indeed, I would have expected exacly the same time. The only explanation I can see could be poor programing. I've written or modified at least half a dozen compilers in my career, and I've never seen symbols stored by scope (or type or any sort of other characteristic) first, then by name. Doing so could indeed make scoping a little more efficient. But this is not the way compilers (or interpreters like in the case of CF) are designed. They have an array of all symbols by name, and each symbol has characteristics. Then looking for variables.mySymbol would actually mean look for mySymbol first, there may be several of them, each with a different scope, and then look for the one having scope variables. If the compiler is well designed, symbols will be sorted by name first, then by scope in the order they are searched. Your test prove one thing however: Programing ayatollahs just prescribe great principle they feel are logical, but they don't really know what they are talking about. ;-) ~| 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:344823 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
Debugging SOAP
Hello everyone. I appreciate the help you have given me recently. Now I have a new challenge, and I am sure some of you have faced it before. I have written an application that gets and puts data to a SOAP .asmx web service running on a windows server across the internet. I am using cfinvoke. All my gets are working fine. I am getting the expected data. However all my puts are failing, and the guy at the other end says my soap xml must be wrong. Hey, it might be, but I have not been able to come up with a way to see it. I did a google search and found Fiddler and Charles, and installed them both on the dev server which is running my application. But no luck. Each program seems to monitor the traffic between my browser and local dev server, but I need to monitor traffic betwen dev server and the other server across the internet (on a non-standard ssl port). Would one of you kind souls tell me how to configure one of these programs (or some other program) so I can see my SOAP going out and see the response? RR ~| 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:344825 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
Linking Access to Outlook
I have linked to an external source in Access 2007 which is My Outlook Calendar. I can read the records in the table, but cannot modify the table in any way. I would like to insert an autonumber field to index on, but have been unable to Find a way. I plan on using this table in a query with other tables ie: Outlook contacts linked the same way and then a third table of my own but neither of the tables created by Access linked to Outlook have a way for me to key on a unique record. I am writing a flow chart app in CF8. Any ideas, or links to any help would be greatly appreciated. Terry ~| 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:344827 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
