I'm pretty sure in SQL Server you can do this...
> <CFQUERY NAME="GetDocs"...>
> SELECT
> '"' + ISNULL(RTRIM(CATEGORY),'*') + '"' + ',' +
> '"' + ISNULL(RTRIM(newc2),'*') + '"' + ',' +
> ...
> '"' + ISNULL(RTRIM(C_Alias),'*') + '"'
> AS line
> FROM table
> </CFQUERY>
this will cause any NULL entries to be *... so you would get this...
a,b,c,*,d
if the fourth column is NULL
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
-----Original Message-----
From: Dave Watts [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 22, 2002 2:10 PM
To: CF-Talk
Subject: RE: 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.
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
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.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
