×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Subquery syntax

Subquery syntax

Subquery syntax

(OP)
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.

RE: Subquery syntax

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

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Subquery syntax

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

RE: Subquery syntax

(OP)
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!

RE: Subquery syntax

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close