I've been trying to put together sample data on this problem since Friday, but I'm having issues. I'm thinking the problem is something specific to the old database I'm trying to work in:
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
PL/SQL Release 8.1.7.2.0 - Production
CORE 8.1.7.0.0 Production
TNS for HPUX: Version 8.1.7.2.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
I tried copying over all the data into my test database:
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
And, I can't duplicate the problem there. In fact, I ran my original query in my test database, using the db llink, the query ran just fine.
But, I tried to create some sample data anyway to try to get some help here, so, I created 3 tables in my test database as SELECT from the old database (via a db link).
One table was really the results of a query (with many sub-queries) that was working just fine in the old database. It was the 2nd highest level query that wasn't giving me any problems. (Below, in my query, subquery 'a' runs just fine in the test database also; it's only the top level query that gives me an error.)
The other two tables I created were just simple sub-sets of the original tables in the old database.
I ran my query in my test database against those tables, and again, it ran just fine. No character set mismatch error.
So, based on the tables from the old database, I created the following test tables:
TEST TABLE DEFINITIONS
------------------------------------
CREATE TABLE test_inv_1
( cust_id CHAR(10)
, item_id VARCHAR2(32) -- becomes 96 if I create as select
, match_item_id CHAR(10)
, docdate DATE
, tot_amt NUMBER(5)
);
INSERT INTO test_inv_1
VALUES('C1000','I0001','I0001',TO_DATE('01/01/2011','mm/dd/yyyy'),1000);
INSERT INTO test_inv_1
VALUES('C1000','I0002','I0002',TO_DATE('01/05/2011','mm/dd/yyyy'),1050);
INSERT INTO test_inv_1
VALUES('C1000','MANUAL ADJUSTMENT','MANUAL ADJ',TO_DATE('01/01/2011','mm/dd/yyyy'),1000);
INSERT INTO test_inv_1
VALUES('C1001','I0003','I0003',TO_DATE('01/01/2011','mm/dd/yyyy'),500);
INSERT INTO test_inv_1
VALUES('C1001','OTHER ADJUSTMENT','OTHER ADJU',TO_DATE('01/01/2011','mm/dd/yyyy'),50);
INSERT INTO test_inv_1
VALUES('C1001','M123','M123',TO_DATE('01/10/2011','mm/dd/yyyy'),150);
CREATE TABLE test_btab_1
( billing_id CHAR(10) NOT NULL -- becomes 30
, payment_terms_cd CHAR(3) -- becomes 9
);
INSERT INTO test_btab_1
VALUES ('I0001', '01');
INSERT INTO test_btab_1
VALUES ('I0002', '01');
INSERT INTO test_btab_1
VALUES ('I0003', '02');
INSERT INTO test_btab_1
VALUES ('M123', 'COD');
INSERT INTO test_btab_1
VALUES ('I0004', '01');
CREATE TABLE test_tabm_1
( record_type CHAR(4) NOT NULL -- becomes 12
, record_key CHAR(10) NOT NULL -- becomes 30
, numeric_1 NUMBER(15,5)
, numeric_5 NUMBER(7)
, numeric_6 NUMBER(7)
);
INSERT INTO test_tabm_1
VALUES ('LOCA','A1234',NULL,NULL,NULL);
INSERT INTO test_tabm_1
VALUES ('TERM','01',2,10,30);
INSERT INTO test_tabm_1
VALUES ('TERM','02',0,0,45);
INSERT INTO test_tabm_1
VALUES ('TERM','COD',0,0,30);
Again, the first table, test_inv_1, is an example of the results of a query that was working just fine in the old database. (There are comments in the table definitions above--I'll explain those in a bit.)
Here is the query I ran against this data:
QUERY
---------
SELECT a.*
, 1 - (t.numeric_1/100) AS amt_pst_disc
, a.docdate + t.numeric_5 - 1 AS disc_end_date
, a.docdate + t.numeric_6 - 1 AS duedate
FROM (
SELECT ic.*
, CASE
WHEN b.payment_terms_cd IS NULL
THEN CAST('03' AS CHAR(10))
ELSE CAST(b.payment_terms_cd AS CHAR(10))
END AS pay_term
, CASE
WHEN b.payment_terms_cd IS NULL
THEN 'Default Payment Terms'
END AS pt_flag
FROM test_inv_1 ic
, test_btab_1 b
WHERE ic.match_item_id = b.billing_id (+)
) a
, test_tabm_1 t
WHERE a.pay_term = t.record_key
AND t.record_type = 'TERM'
;
In the old database, I get the error:
WHERE a.pay_term = t.record_key
*
ERROR at line 107: --this is actually line 22 in the above query, since test_inv_1 represents the results of a sub-query
ORA-12704: character set mismatch
In my test database, in every form, including with the sample data above, I don't have any problems.
I unfortunately don't have a v8 test environment, and I suspect there aren't many people who actually do, anymore, so using my v10 test database is the best I can do to come up with test data...
The other odd thing I noticed is that when I DESC the tables I created in my test database via SELECT statements, and compare them with the tables I created from CREATE statements (based on the table descriptions I got from the old database), some of the data lengths don't match up. That's what's in the comments in my table definitions. I noted where the tables I created via SELECT statements had different data lengths. I'm not sure if it's important or not, but in case it is, I thought I'd include it.
Also, I'm creating this query to make a report for someone else, and the reporting system only has access to the v8 database, not my test database. So, I'm stuck making the query run against it.
Any help on this would be greatly appreciated!