No good, already considered this but I need to be able to sort on the final
AvgMargin Column so it must have the correct value in to start with.
What about a query of a query to get the final sort?
Bryan Stevenson B.Comm.
VP & Director of E-Commerce Development
Electric Edge Systems Group Inc.
t. 250.920.8830
e. [EMAIL PROTECTED]
----- Original Message -----
From: James Smith
To: CF-Talk
Sent: Wednesday, August 04, 2004 8:15 AM
Subject: RE: Referencing a query column while still in the query
> No, you can't reference the value of a column while building
> the query.
> The column doesn't *have* a value in it until the query is
> built, sent, and the results returned.
That is what I figured.
> However, you could create a helper table to hold the
> appropriate information, and grab it that way.
This may be the way to go.
> Or you could march through the query a line at a time and
> manipulate that column.
No good, already considered this but I need to be able to sort on the final
AvgMargin Column so it must have the correct value in to start with.
> HTH.
>
> --Ben
>
> James Smith wrote:
> > OK, this time I really am stuck. I have removed quite a
> lot from this
> > query to make it easier to read, normally selects more
> fields and has
> > WHERE, GROUP BY and ORDER BY clauses.
> >
> > <cfquery name="BestSellers" datasource="Intranet" maxrows="100">
> > SELECT d.ItemID, d.Title, d.MediaType,
> > (avg(i.BuyersPrice/i.Quantity)
> > -avg(q.Cost)*1.175
> > -avg(i.BuyersPrice/i.Quantity)*0.15
> >
> >
> >
> +#listgetat("1.1,1,1.74,1.83,1.63,2.33,4.51,4.51,3.57",MediaType)#) AS
> > AvgMargin
> > FROM SomeTables
> > </cfquery>
> >
> > The query above needs to know the media type to calculate
> the margin
> > as postage costs vary depending on this. The MediaType is a number
> > from 1-9 so it made sense to use
> >
> listgetat("1.1,1,1.74,1.83,1.63,2.33,4.51,4.51,3.57",MediaType
> ) to get
> > the relevant costing however the MediaType variable is not
> defined as
> > the query is not finished.
> >
> > Is there anyway to reference this field in this way?
> >
> > --
> > James Smith
> > [EMAIL PROTECTED]
> >
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
