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)),
category_title
FROM Category c1
order by category_title
Then you would output it using RowNo as the variable name. The order by and
the where of the sub query MUST be the same column. Let me know if you have
any more questions.
HTH,
--K
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, September 20, 2000 4:21 PM
> To: CF-Talk
> Subject: RE: Current Row in SQL as opposed to CF? [CF-Talk]
>
>
> 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/cf-talk@houseoffusion.com/
> > To Unsubscribe visit
> >
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=list
s/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/cf-talk@houseoffusion.com/
> 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/cf-talk@houseoffusion.com/
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/cf-talk@houseoffusion.com/
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/cf-talk@houseoffusion.com/
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.
