Oracle tools, such as Oracle SQL *Plus and Oracle SQL Developer, change a SELECT query into two or more queries that it sends to MCPSQL. For example, the following query entered from Oracle SQL *Plus:

SELECT * FROM customer@mylink;

results in the following queries sent to MCPSQL:

SELECT * FROM customer WHERE 0=1;
SELECT * FROM customer;

For the first query, Oracle SQL *Plus performs a SELECT statement that does not return any records and uses the call to obtain information about all of the columns. The second query returns the records.

Known Issues

Oracle query tools report type mismatch error with DATE in WHERE clause

When using ODBC to retrieve DMSII data into Oracle, you might receive the following error:

Invalid mixing of types in a comparison

This error occurs when performing a SELECT query that includes the WHERE clause with a DATE field. The following queries, when entered in Oracle SQL *Plus or SQL Developer, return the error:

SELECT * FROM order@lnk WHERE ord_date > DATE '2020-03-22';
SELECT * FROM order@lnk WHERE ord_date > TO_DATE('22-MAR-2020'); 

To resolve this issue, use the TIMESTAMP or TO_TIMESTAMP function instead. For example,

SELECT * FROM order@lnk WHERE ord_date > TIMESTAMP '2020-03-22 00:00:00';
SELECT * FROM order@lnk WHERE ord_date > TO_TIMESTAMP ('2020/03/22', 'YYYY/MM/DD'); 

Using DATE in INSERT and UPDATE queries

When using ODBC to insert or update DSMII data via Oracle, you might receive the following error:

The DATE literal string must have the form YYYY-MM-DD, and must be a valid date

This error occurs when performing an INSERT or UPDATE query that includes a DATE field. The following query, when entered in Oracle SQL *Plus or SQL Developer, returns the error:

INSERT into order@lnk (id, customer, orderdate) values (123, 'Best Corp.', DATE '2020-03-22');

There are no TIMESTAMP syntactical workarounds to avoid this issue.

SELECT DISTINCT with some WHERE conditions

A query that performs a SELECT DISTINCT might return no rows when there are rows to be returned. This issue only occurs when a WHERE condition is included with an alpha field that is matching for blank. For example,

SELECT DISTINCT id FROM address@mylink WHERE city = ‘’;

results in only the first information gathering call to ODBC:

SELECT * FROM address WHERE 0 = 1;

The query does not perform a secondary call to select the actual rows. This issue occurs only in the Oracle tools with the combination of DISTINCT and WHERE <column>=‘’. The following syntax returns the correct number of rows:

SELECT DISTINCT id FROM address@mylink WHERE city <> ‘’;
SELECT DISTINCT id FROM address@mylink WHERE city = ‘New York’; 
SELECT id FROM address@mylink WHERE city = ‘’;