Oracle connection error

[Eric]

Hi

I have not been able to find a solution for this

I have a field retrieving data from an Oracle DB that works fine

{ DATABASE \c "DSN=***;UID=**;PWD=*" \s "SELECT tpklapmdta FROM
pthdba.lapmdta" }

When I attempt to retreve a count it returns a connection error

{ DATABASE \c "DSN=***;UID=**;PWD=*" \s "SELECT COUNT(tpklapmdta) FROM
pthdba.lapmdta" }

Using Oracle 8 server, Oracle 9 client, Word 2003 sp2, Windows 5.1 sp2

Anyone know what is going on or have any suggestions

Eric

 

[Peter Jamieson]

Anyone know what is going on

Not really - the syntax looks fine, but...

or have any suggestions

a. are you using Oracle's own ODBC driver or Microsoft's (the one that's
only compatible with Oracle 6 but may well work in simple cases)?
b. have you tried using the OLE DB provider?
c. do any variations such as SELECT COUNT(*) FROM... work?
d. I would also try a few other syntax options such as

SELECT COUNT(tpklapmdta) FROM "pthdba"."lapmdta"

(You may have to double up the double quotes, but you may also find that the
DATABASE field does not allow them syntactically)

SELECT COUNT(l.tpklapmdta) FROM pthdba.lapmdta l

e. purely as an experiment, are you in a position to create a view in your
Oracle database that does the SELECT count(tpklapmdta) ?

As far as (d) is concerned, there's no obvious reason why you should need
such a syntax variation, particularly when the raw SELECT tpklapmdta
statement works OK, but it may be that the ODBC driver is not simply passing
the SQL directly to Oracle but parsing it first and getting it wrong.

Don't currently have an instance of Oracle here so can't test - sorry!

Peter Jamieson