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 thisValue>and > >>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
