×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

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

SELECT within SELECT Statement. Question

thread318-1322517

SELECT within SELECT Statement. Question

SELECT within SELECT Statement. Question

(OP)
I'm looking for a way of doing something like this.

Getting a very specific field from a different SQL Statement.

I know in SQL Server and INFORMIX is posible to do this... but just can't find the way to do it in Pervasive.   (new to the engine).

SELECT Name,   (SELECT  max(InvoiceDate) FROM invoice where Customer_id = Customer.Customer_id )
FROM Customer

PLEASE HELP !!!!

My best

Carlos G.


RE: SELECT within SELECT Statement. Question

Have you tried the statement in PSQL?  Did it give an error?  If so, what error?  What version of PSQL are you using?

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

RE: SELECT within SELECT Statement. Question

(OP)
What we have is... Pervasive.SQL 2000i

And I open the Pervasive.SQL Control Center and open a query window over my selected database

and then when i try to execute the statement i get an ODBC Syntax Error, at this moment I don't have access to the system... but  do you think is possible to do this in pervasive ?  the statement Select F1 , ( select F3 from T2 )  from T1

Actually where i really need this query to be working is in a Report File...

Thanks for your advice !

Carlos G.

RE: SELECT within SELECT Statement. Question

Well, PSQL 2000i is fairly old.  I don't have it and Pervasive doesn't support it. I tried the following with PSQL V9.5 (current version) and it worked:

create table T1 (F1 char(10))#
insert into T1 (F1) values ('t1')#
create table T2 (F3 char(20))#
insert into T2 (F3) values ('t2')#
Select F1 , ( select F3 from T2 )  from T1

The result is:
"f1", "EXPR_1"
"t1        ", "t2                  "

I did it in ODBC Test but it'll work the same in PCC.  

One thing to be aware of is that the sub select can return one and only one record.  Anything more and it'll give a "row count" type error.  

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

RE: SELECT within SELECT Statement. Question

(OP)
Hi , Did the test, and have the folowing error.



ODBC Error: SQLSTATE=37000, Native Error code=0
Syntax Error: SELECT COMP_REPORT_NAME , (SELECT <<??>> COMP_KEY FROM COMPFILE WHERE COMP_KEY = '1')  FROM COMPFILE

The Query is:
SELECT COMP_REPORT_NAME , (SELECT COMP_KEY FROM COMPFILE WHERE COMP_KEY = '1')  FROM COMPFILE


Don't understand.... it should be so simple..   Please Help !


Carlos


RE: SELECT within SELECT Statement. Question

Did you try my sample?  Did it work?  If it doesn't, it might be time to upgrade to a current version of PSQL.

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

RE: SELECT within SELECT Statement. Question

(OP)
I Have done thay same thing as your example...

in the following link i send you the images of what i have.

http://incubeware.com/pervasive.html

I'm lost with this error..

Or.. if you know a different way of doing this same thing..

Thanks in advance..

Carlos

RE: SELECT within SELECT Statement. Question

I'm guessing that PSQL 2000i SP3 doesn't support that syntax.  

The only thing I can suggest would be to upgrade to PSQL V9.5 (the current version).  A trial version is available from Pervasive's web site (http://www.pervasive.com/downloads).  
You should test with the PSQL v9 before you put it into production.

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