?
Solved

Pervasive SQL 9.5

Posted on 2011-02-16
5
Medium Priority
?
1,292 Views
Last Modified: 2012-08-13
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
Comment
Question by:pwiginton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 18

Expert Comment

by:mirtheil
ID: 34909808
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
 

Author Comment

by:pwiginton
ID: 34909858
I have tried both with the same results.
0
 

Author Comment

by:pwiginton
ID: 34909925
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
 
LVL 18

Accepted Solution

by:
mirtheil earned 1000 total points
ID: 34909959
According to the PSQL v9 docs, ORDER BY clauses are not supported in the subquery.  
0
 

Author Comment

by:pwiginton
ID: 34910025
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question