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 SELECT, change it to a SELECT INTO
#tempTable and in that table definition, create an IDENTITY field as the
first column. If your SELECT is ordered properly, the Identity field will
have an incremental numeric value 1..2..3.. This requires a stored
procedure, but if you're good at writing sp's, it should work for you.
Given the two choices, I prefer #1. But if you can't do it in CF and must
do it in SQL, #2 is probably your only other alternative.
--Doug
-----Original Message-----
From: PC [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 20, 2000 7:40 AM
To: [EMAIL PROTECTED]
Subject: Re: Current Row in SQL as opposed to CF? [CF-Talk]
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">
"Category" #currentrow# #Category_Title#
</cfoutput>
I need this in a variable that is part of a few variables concatenated and
written to a file using CFFILE write ....
Thanks for anymore insight... :)
> 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 create a
> user-defined function in 'C' to do it, but that would be
> crazy.
>
> But if you can't use CFOUTPUT, why does it matter what the
> results look like? What exactly are you trying to do?
>
> Nick
>
> -----Original Message-----
> From: PC [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, September 20, 2000 2:07 PM
> To: [EMAIL PROTECTED]
> Subject: Current Row in SQL as opposed to CF? [CF-Talk]
>
>
> 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 Politics
>
> from a query looking something like:
>
> SELECT TOP 4 Category + ?currentrow? + Category_title
>
> So what property exists, or technique for generating the ?currentrow? part
> of the statement above????
>
> Now, I cannot use the indentity field .. I don't want values like
> Category 2 ...
> Category 19 ...
>
> And ... I cannot use a CFOUTPUT or CFLOOP -- I want to do it all in the
SQL
> statement if possible!!
>
>
> --------------------------------------------------------------------------
--
> --
> Archives: http://www.mail-archive.com/[email protected]/
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
>
>
> **********************************************************************
> Information in this email is confidential and may be privileged.
> It is intended for the addressee only. If you have received it in error,
> please notify the sender immediately and delete it from your system.
> You should not otherwise copy it, retransmit it or use or disclose its
> contents to anyone.
> Thank you for your co-operation.
> **********************************************************************
> --------------------------------------------------------------------------
----
> Archives: http://www.mail-archive.com/[email protected]/
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
>
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
