Chad
I dont think thats a good idea from a readability perspective. How can
you tell what the query does when you read the code if there a a SQL
fragments stored in variables all over the place. Plus then have to
manage your own escaping of params to the query. Debuggging nightmare.
Scott
You could use a stored proc for this query and it will give you
1. probably a bit faster exectuion.
2. a simpler cold fusion code block to get your data
but then the cons are
1. stored procs and TSQL if you are using mssql are a bugger to debug and
2. the code is damned ugly.
Id suggest this
look for where that sql statement is doing nested selects to compute
things like Max's etc. and also where its doing stuff like converting ID
numbers to strings with case statements etc. Then start creating views
to do this processing.
you will find your CF query will get a bit more simpler to manage and
you get a small performance increase.
as for getting this query over to crystal reports, after you have
optimised the query with views you might find that its a whole lot
simpler to convert to a stored proc.
my 2c
Pat
Chad Renando wrote:
Dude. That's huge!
Personally, I don't do my processing in the query itself. I create
variables along the way and then join them all up in my query, like
so:
<cfquery name="qryGetPresentationData">
SELECT #variables.sqlSelectFieldNames#
FROM
#variables.PreFROMLeftParentheses##qryGetPresentationFieldsToDisplay.ObjectTableName##variables..FROMLeftJoins#
</cfquery>
Not nearly as complex as yours, but it can scale. Probably not the
most efficient. Probably should just ignore the idea.
Chad
who thinks home builders forget about winter when they build houses in Australia
---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
