PSQL and nested queries

[korzen]

I think what I'm referring to is a nested query. Please let me know if there's better lingo available than that.

My problem involved trying to execute a complicated query against a 2000i database. It would work in a database like MSSQL, but I'm a little less well versed in Pervasive. So I've stripped it down a bit for the forum. What's the correct way to construct this statement for use against said Pervasive database?

SELECT q1.* FROM (SELECT c1 FROM t1) As q1

Thank you!

Chris
Portland, Maine

 

[mirtheil]

What exactly are you trying to do in that query? The query looks like it's trying to get the table name dynamically? You can't do a nested query like that using Pervasive. You can create a second result set and get the information you're needing.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[korzen]

Well, like I said, I'm trying to create a complicated query. The goal is not so much to get a dynamic table name as to assign an alias to a nested SELECT statement, which I believe you need to do if you use a nested query like this. But I wanted to keep it simple for starters. Really what I need to do is join a table to a union query, something like this:

SELECT
cust.CustomerName,
Sum(sales.Amount) As SalesTotal
FROM
tblCustomers cust
INNER JOIN
(
SELECT
CustomerID, CustomerName, Amount FROM tblSalesOpen
UNION ALL
SELECT
CustomerID, CustomerName, Amount FROM tblSalesHistory
) As sales
ON
cust.CustomerID = sales.CustomerID
GROUP BY
cust.CustomerName


Second result set? Exactly how would that work. I've tried creating the nested statement as a view, but Pervasive won't allow union queries in view. Bummer.

Thanks!