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:344815
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
