[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1316
  • Last Modified:

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?
0
pwiginton
Asked:
pwiginton
  • 3
  • 2
1 Solution
 
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
 
mirtheilCommented:
According to the PSQL v9 docs, ORDER BY clauses are not supported in the subquery.  
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now