Home » Infrastructure » Windows » Oracle OleDb provider
Oracle OleDb provider [message #132343] Fri, 12 August 2005 01:13 Go to next message
harryafriend
Messages: 1
Registered: August 2005
Junior Member
Hi All,
I am running Oracle9i Enterprise Edition Release 9.2.0.1.0 on windows 2000 professional.

I have a COM component developed in VB where I am using ADO to connect to oracle. My connectionstring is as below:

"Provider=OraOLEDB.Oracle.1;Data Source=123;User Id=a;Password=a;"

Everything work fine, except for this query:
SELECT A.QB_QUEST_CODE, A.QB_QUEST_DESCRIPTION QUESTION, DECODE(A.QB_QUEST_ACTIVE,1,'ACTIVE','INACTIVE') STATUS,
(SELECT COUNT(*) FROM QB_ANS WHERE QB_ANS_QUEST_CODE = A.QB_QUEST_CODE AND QB_ANS_ACTIVE=1) AS "ANSWERS (NOs)"
FROM QB_QUEST A
WHERE A.QB_QUEST_SEC_CODE = 100003
AND A.QB_QUEST_CMPX_CODE = 100001
AND A.QB_QUEST_OBJECT_CODE = 100001

Running this query directly on the oracle prompt gives the result fine, but through the provider, it gives and error :

ORA-00936: missing expression

The Problem I think is with the inline query (see count(*) in the query), but I am not sure why this is not working. Any help in this regard will be appreciated as I am in urgent need for a solution.

Regards,
Harry.
Re: Oracle OleDb provider [message #132641 is a reply to message #132343] Mon, 15 August 2005 07:59 Go to previous message
alland
Messages: 3
Registered: August 2005
Junior Member
Harry:

This is what I would do:

1) Take out the inline query and verify that it works without the inline query.

I suspect that you may not be able to use DECODE which is an Oracle function within ADO, which is Microsoft based. If this is where the problem is, try using a case statement.

2) If decode does work within ADO, then simply break up the select statements and join the results. I know this is less elegant than using the inline query but at least it gets the job done.


Previous Topic: OLE DB Drivers redistribution
Next Topic: Windows update seems to wreak havoc on Oracle Database
Goto Forum:
  


Current Time: Thu Mar 28 09:07:43 CDT 2024