with the extra table now but worth knowing for next time this comes up.
--
Jay
> -----Original Message-----
> From: I-Lin Kuo [mailto:[EMAIL PROTECTED]
> Sent: 04 August 2004 16:37
> To: CF-Talk
> Subject: Re: Referencing a query column while still in the query
>
> SELECT d.ItemID, d.Title, d.MediaType,
> (avg(i.BuyersPrice/i.Quantity)
> -avg(q.Cost)*1.175
> -avg(i.BuyersPrice/i.Quantity)*0.15
>
> + CASE mediatype
> WHEN 1 then 1.1
> when 2 then 1
> ...
> END
> AS AvgMargin
> FROM SomeTables
> --- James Smith <[EMAIL PROTECTED]> 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]
