Subquery syntax

[enterpulsejd]

I'm trying to work out a simple subquery in Btrieve. I'm getting an error. This is just an example, I know I can do this query differently. I just need to know if there is a trick to subqueries in the FROM clause in Btrieve:

select aropen.*, ca.Customer_Name
from aropen, (select * from custadd) ca
where aropen.apply_to_number = 010630
and aropen.customer_number = ca.number

Gives me the error:

ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: select aropen.*,
ca.Customer_Name
from aropen,
(select<< ??? >> * from custadd) ca
where aropen.apply_to_number = 010630
and aropen.customer_number = ca.number

Which kinda tells me nothing (I don't know how to read that error message). Any clues? Your help would be much appreciated.

We're on Pervasive 8.

 

[mirtheil]

You can't do that kind of subquery in PSQL.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[mirtheil]

I replied too quickly. I did find that with PSQL V9 (it's what I've got installed) that it is possible. The following query, while not optimized, does work.
select First_Name,ca.Name from person, (select name from class where id <15) ca where person.state = 'TX'
It uses the PSQL DEMODATA.
A simpler test is:
create table sb1 (id identity, sbF2 char(10))#
create table sb2 (sbID identity, sb2F3 char(20))#
insert into sb1 (id, sbf2) values (0,'first')#
insert into sb2 (sbID, sb2f3) values (0,'second')#
select sb2.*, ca.sbf2 from sb2, (select sbf2 from sb1) ca#

Do those queries work with V8?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[enterpulsejd]

Thanks for the reply mirtheil.

Those statement DO NOT in fact work, but we are on v8 so I assumed they would not.

Do you know the proper syntax for a subquery in v8 PSQL? Or does v8 not support subqueries at all?

What about temporary tables, like in SQL Server and Oracle. Are they possible?

As you can tell I am new to Btrieve for the current project I am on. Your code has "#" signs at the end of it, what does this do syntactically? Does it terminate the current statement?

Thanks for your help!

 

[mirtheil]

I don't believe V8 supports subqueries like you are trying. Same goes for Temp tables. V9 supports them though.
Yes in the example given, the "#" is a statement separator in the Pervasive Control Center.
Normally I would suggest contacting Pervasive for a definitive answer on the subquery question but V8 is unsupported and opening a ticket would cost $500 USD per hour.



Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com