| Oracle OleDb provider [message #132343] |
Fri, 12 August 2005 01:13  |
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  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
