be to create a (SQL) function that returns the proper value depending on
the media type.
Pascal
> -----Original Message-----
> From: James Smith [mailto:[EMAIL PROTECTED]
> Sent: 04 August 2004 17:02
> To: CF-Talk
> Subject: Referencing a query column while still in the query
>
> 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]
