Link to home
Start Free TrialLog in
Avatar of pwiginton
pwiginton

asked on

Pervasive SQL 9.5

I am trying to include a subquery in Pervasive SQL 9.5 and I am getting syntax error.  Below is my query:

SELECT Price1.* FROM mhfspric AS Price1
WHERE PriceDate = 20110131
OR PriceDate =
(SELECT MAX(PriceDate) AS PriceDate FROM mhfspric
WHERE mhfspric.Cusip = Price1.Cusip
AND mhfspric.PriceDate <= 20101231
ORDER BY mhfspric.PriceDate DESC)
ORDER BY Cusip

This is what is returned by Pervasive Control Center:

<<<<<<<<<<<<<<<<<<<<<<<<
[Pervasive][ODBC Engine Interface]Syntax Error: SELECT * FROM mhfspric AS Price1
WHERE PriceDate = 20110131
OR PriceDate IN
(SELECT TOP 1 * FROM mhfspric
WHERE mhfspric.Cusip = Price1.Cusip
AND mhfspric.PriceDate <= 20101231
ORDER<< ??? >> BY
>>>>>>>>>>>>>>>>>>>>>>>>

Could I get assistance determining the correct syntax?
Avatar of Mirtheil
Mirtheil
Flag of United States of America image

I'm confused.  In your statement you say you have:
OR PriceDate =
(SELECT MAX(PriceDate) AS PriceDate FROM mhfspric

but the error shows:

OR PriceDate IN
(SELECT TOP 1 * FROM mhfspric

Are you using the MAX or the TOP function?  
Avatar of pwiginton
pwiginton

ASKER

I have tried both with the same results.
I have a table of prices.  For example.

CUSIP PRICEDATE PRICE
11111 20110131 100.00
11111 20110115 99.83
11111 20110107 99.85
11111 20101231 85.00
11111 20101215 84.00
with records continuing back to 1992.

I what to get for all Cusips the 01/31 price and the most recent price 12/31 or older.
ASKER CERTIFIED SOLUTION
Avatar of Mirtheil
Mirtheil
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If I drop the ORDER BY from the subquery, use MAX(PRICEDATE) in the subquery, I should get the right results.  Is there a more efficient way to get the results I want?