If you are using MS SQL, there's an option that can be set to still have the
string built even if a NULL is encountered...
SET CONCAT_NULL_YIELDS_NULL {ON | OFF}
so you could do:
<CFQUERY NAME="GetDocs"...>
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT
'"' + RTRIM(CATEGORY) + '"' + ',' +
'"' + RTRIM(newc2) + '"' + ',' +
...
'"' + RTRIM(C_Alias) + '"'
AS line
FROM table
SET CONCAT_NULL_YIELDS_NULL ON
</CFQUERY>
> -----Original Message-----
> From: BEN MORRIS [mailto:[EMAIL PROTECTED]]
> Sent: 22 May 2002 21:52
> To: CF-Talk
> Subject: SQL: Creating a CSV in a select
>
>
> 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.
>
> TIA for any help.
>
> ---------------------------------------
>
> Ben Morris
>
> Web Site Developer
> American Federation of Government Employees, AFL-CIO
> (202) 639-6448
> www.afge.org
>
>
>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
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
