ADO, Oracle, and IS NULL

From: Paul <pferlatte_at_pattieng.com>
Date: 3 May 2003 11:27:21 -0700
Message-ID: <e52d4f6c.0305031027.53791951@posting.google.com>

Has anyone else had an issue trying to query an Oracle database with
"IS NULL" in the WHERE clause.

Here is my query

SELECT COUNT(*)
FROM ALLOCATION_DATA
WHERE TRANSACTION_TS IS NULL
AND UNIT_PICK_SYSTEM_CODE = 'GOH' When I run this query using ADO's "Set RS = Conn.Execute" method, it returns a 0 in the first record (RS(0).Value).

When I run this query in SQL+Worksheet, I get a value of 21,000 (which is how many records there are).

To make sure I wasn't loosing my mind, I tried the same thing with the following query.

SELECT COUNT(*)
FROM ALLOCATION_DATA
WHERE UNIT_PICK_SYSTEM_CODE = 'GOH' Both ADO and SQL+Worksheet returned 21,000.

So I guessed that the problem was with the "IS NULL" so i tried using the NVL function.

SELECT COUNT(*) FROM ALLOCATION_DATA
WHERE NVL(TRANSACTION_TS, TO_DATE('1/1/1900', 'MM/DD/YYYY'))

• TO_DATE('1/1/1900', 'MM/DD/YYYY') AND UNIT_PICK_SYSTEM_CODE = 'GOH'

ADO returned 0 and SQL+Worksheet returned 21,000.

If it helps any, here is the connection string I am using:

"Provider=OraOLEDB.Oracle;Password=xxxxx;User ID=xx;Data
Source=XXX_XXXXXXX"

(I x'ed out the pwd, uname, and server name)

Is there some parameter I have to set for OraOLEDB to make "IS NULL" work properly? Or is it a problem with ADO?

Also note that I am not in control of the structure of the database that I am querying nor am I in control of the fact that NULLs are used as an indicator. So modifying the database structure or the data itself is not an option. Received on Sat May 03 2003 - 13:27:21 CDT