[AccessD] Select 2 fields from a subquery

Thanks. Works really fast on a few records. I will have to check on
using it on more records.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, May 16, 2007 11:52 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Select 2 fields from a subquery

Hi Chester

The "quick" solution (it may run slow) is to repeat the subquery for the
other field:

SELECT T_PriceCurrent.SDate, 
(Select Price from T_PriceIndex as T1 where
T1.Pattern = T_PriceCurrent.Pattern And T1.SDate=(Select Max(SDate) from
T_PriceIndex AS T2 where T2.Pattern = T_PriceCurrent.Pattern And
T2.SDate<=T_PriceCurrent.SDate)) AS PriceCurrent,
(Select Price2 from T_PriceIndex as T1 where
T1.Pattern = T_PriceCurrent.Pattern And T1.SDate=(Select Max(SDate) from
T_PriceIndex AS T2 where T2.Pattern = T_PriceCurrent.Pattern And
T2.SDate<=T_PriceCurrent.SDate)) AS PriceCurrent2

FROM T_PriceCurrent;

/gustav

>>> Chester_Kaup at kindermorgan.com 16-05-2007 18:28:29 >>>
I have the following query that selects on field (Price) from a table
(T_PriceIndex) with a sub query. I would like to select 2 fields from
the table (T_PriceIndex) used in the sub query. The current query looks
like this.

 

SELECT T_PriceCurrent.SDate, (Select Price from T_PriceIndex as T1 where
T1.Pattern = T_PriceCurrent.Pattern And T1.SDate=(Select Max(SDate) from
T_PriceIndex AS T2 where T2.Pattern = T_PriceCurrent.Pattern And
T2.SDate<=T_PriceCurrent.SDate)) AS PriceCurrent

FROM T_PriceCurrent;

 

I changed the query as below. It now generates an error and wants an
exists in the query. I am lost here. Can it be done? Thanks

 

SELECT T_PriceCurrent.SDate, (Select Price, Price2 from T_PriceIndex as
T1 where T1.Pattern = T_PriceCurrent.Pattern And T1.SDate=(Select
Max(SDate) from T_PriceIndex AS T2 where T2.Pattern =
T_PriceCurrent.Pattern And  T2.SDate<=T_PriceCurrent.SDate)) AS
PriceCurrent

FROM T_PriceCurrent;

 

Chester Kaup

Engineering Technician

Kinder Morgan CO2 Company, LLP

Office (432) 688-3797

FAX (432) 688-3799



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com