Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Subquery syntax

Status
Not open for further replies.
enterpulsejd

enterpulsejd

Programmer
Feb 1, 2007
2
US
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.
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mSportsman
  • Locked
  • Question
C# Pervasive Data Provider Update command causing Syntax error, but not on development machine.
Replies
5
Views
405
mirtheil
mirtheil
WinePaul
  • Locked
  • Question
Cast Syntax
Replies
2
Views
443
WinePaul
WinePaul
ShockValue
  • Locked
  • Question
subquery in a query using a case
Replies
2
Views
271
ShockValue
ShockValue
GigiClippy
  • Locked
  • Question
PERVASIVE.SQL COMMANDO SYNTAX
Replies
2
Views
197
DjangMan
DjangMan
WalterHeisenberg
  • Locked
  • Question
Update syntax for pSQL
Replies
1
Views
269
mirtheil
mirtheil

Part and Inventory Search

Sponsor

Back
Top