SELECT within SELECT Statement. Question

[incubeware]

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.

 

[mirtheil]

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

 

[incubeware]

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.

 

[mirtheil]

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

 

[incubeware]

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

 

[mirtheil]

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

 

[incubeware]

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

 

[mirtheil]

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