Cheers
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:32 AM
Subject: RE: Referencing a query column while still in the query
I had also considered this but I was just looking for a more "elegant"
solution that got the job done in one go. I am just about to build a new
table with mediatype and shippingcosts in it so I can just join them.
--
Jay
> -----Original Message-----
> From: Bryan Stevenson [mailto:[EMAIL PROTECTED]
> Sent: 04 August 2004 16:25
> To: CF-Talk
> Subject: Re: Referencing a query column while still in the query
>
> James...you said:
> 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]
