What's wrong with this SQL statement?

[Pirre001]

I use Oracle 10g with Client9

In SQL Navigator this works:

SELECT (SELECT Count(*) from tbllev) totalrows, salesorder, customer, idt FROM tblLev

But when I use exactly same SQL from VB it doesen't work?

SQL = "SELECT (SELECT Count(*) from tblLev) totalrows, salesorder, customer, idt FROM tblLev"

I get error "ORA-00936: missing expression"

If I remove this from the SQL string: (SELECT Count(*) from tblLev) totalrows, it works from VB?

[Al42]

I'm not sure, but aren't you missing a comma?

"SELECT (SELECT Count(*) from tblLev), totalrows, salesorder, customer, idt FROM tblLev"

[techgnome]

Nooooo, he's not missing a coma.... that's the field alias for the SELECT....

Squeeze an "AS" between the ")" and "totalrows" ....

Double check your connection string and make sure you are using an Oracle provider and not a generic ODBC provider.

-tg

[Pirre001]

I've checked again and found that the oracle provider I use "Provider=OraOLEDB.Oracle" is not working with this kind of query.
If I change provider to Microsofts "Provider=msdaora" it works?

Can some one tell me why it is so?

[techgnome]

It's something that was implemented in one provider that wasn't done in the other. For anything more complete than that, you'd have to talk to those who wrote the providers.

-tg