Why doesn't this SQL work?

I've got a program that is designed to support either MS SQL Server or Oracle. We're developing on Oracle Workgroup Server 7.3.2.2.1. The user is allowed to select certain search criteria, and the software "matches" against the tables for those criteria.

In this case, the user is asking to match where a varchar field's first 5 bytes equal any other item's same 5 bytes in the table. The sql my little program is generating is below. I swear this worked a week ago, but...maybe not! It certainly doesn't work now!

SELECT L.TRANID, R.TRANID FROM TRANSACTIONS L, TRANSACTIONS R WHERE SUBSTR(L.USERREF1,1,5) = SUBSTR(R.USERREF1,1,5); In this sql, userref1 is defined in oracle as varchar2(16).

This will yield an ORA-00604 (recursive sql 1) and a ORA-00904 (invalid column name) if executed. The column userref1 certainly does exist. In fact a snippet like: SUBSTR(L.USERREF1,1,5) = '12345' works without error...

This same sequence works fine on the other sql servers we have in house, like MS NT SQL, SQL Anywhere.

Anyone have a hint or workaround for a novice Oracle DB Programmer?

bwebb_at_lightind.com Received on Mon Jun 30 1997 - 00:00:00 CDT