[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