> A while ago Marian Dumitrascu posted the following code to > create a csv file which is very nice and clean: > > <CFQUERY NAME="GetDocs"...> > SELECT > '"' + RTRIM(CATEGORY) + '"' + ',' + > '"' + RTRIM(newc2) + '"' + ',' + > ... > '"' + RTRIM(C_Alias) + '"' > AS line > FROM table > </CFQUERY> > <!--- create the file content ----> > <CFSET filecontent = ValueList(GetDocs.line,"#CHR(13)##CHR(10)#")> > <!--- and save it ---> > <cffile action="APPEND" file="#csvfile#" output="#filecontent#"> > > My problem comes in trying to do the select statement, I am > finding that it will not populate that "line" field if any of > the fields used to build it are null. Is there a way to get > around this in the select statement. Having to loop through > a query takes far too much time with 7000 records, where > ValueList() is much quicker.
If you're using SQL Server, you should be able to use the COALESCE function, which returns the first non-null argument: SELECT ... + COALESCE(RTRIM(CATEGORY), "") + ... If not, you can build a CASE statement. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
