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?
pwigintonAsked:
Who is Participating?
 
mirtheilConnect With a Mentor Commented:
According to the PSQL v9 docs, ORDER BY clauses are not supported in the subquery.  
0
 
mirtheilCommented:
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?  
0
 
pwigintonAuthor Commented:
I have tried both with the same results.
0
 
pwigintonAuthor Commented:
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.
0
 
pwigintonAuthor Commented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.