I spoke with our DBA and this is what he came up with.
SELECT RowNo = 'Category ' + convert(varchar(10), (select count(*)
from category c2
where c2.category_title
= c1.category_title)),
IS there a way to do get the current row as part of a select statement in a
similar fashion as the coldfusion #queryname.currentrow# property functions
in a CFOUTPUT context?
What I need are results that look like:
Category 1 Sports
Category 2 Languages
Category 3 Reference
Category 4
Not in standard SQL. This operation would be at odds with
the relational view of data, in which data is intrinsically
not ordered.
So if there is a way of doing it that would work in a single
CFQUERY, it's going to be be DBMS-specific. If you happen
to be using PostgreSQL you might be able to to
The problem is that any row number associated with a row isn't going to
be in order. What you call row 1 may be stored in the database as row
1567. It only becomes row 1 because your SQL statement selects it as row
1. So you're forced to use properties of the result set, i.e.
I am using SQL Server 7.
I can use CFOUTPUT I suppose. What I need to do is set the results to a
variable and write that as part of a file.
I am a little unclear on how I would even use CFOUTPUT in this fashion --
can I somehow
set the output to a variable?
CFOUTPUT query="getcategories"
This is a multi-part message in MIME format.
--=_NextPart_000_01B0_01C02310.14845680
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Re: Current Row in SQL as opposed to CF? [CF-Talk]
Use CFLOOP instead of CFOUTPUT combined with your CFFILE
You chould use a cursor but it's slow..
Justin
- Original Message -
From: "PC" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, September 20, 2000 3:40 PM
Subject: Re: Current Row in SQL as opposed to CF? [CF-Talk]
I am using SQL Server 7.
I can use CFOUTPUT I suppose.
If I use CFLOOP -- do I just keep appending to the string I am creating ...
Ie.,
CFLOOP query="getcategory"
CFSET myvar = myvar "Category " getcategory.currentrow
getcategory.title
/CFLOOP
...or is there a more efficient append function to use with the variable?
Thanks for the help!
I see two choices:
1. CFLOOP over the query
cfset #tempVar# = ""
CFLOOP query="getcategories"
cfset #tempVar# = #tempVar# "Category" #currentrow#
#Category_Title#
/cfloop
You said you couldn't use CFLOOP in your script, but I'm not sure why not.
2. Use SQL Temp tables. Where you do a
9 matches
Mail list logo
