Error ORA-01722: Invalid number when creating materialized view (Oracle SDE problem?)

Question

I wanted to create a materialized view out of a query involving a spatial table (SDE based) and oracle non-spatial tables. Executing the query only, works, but as soon as I want to create a materialized view (or a table), error "ORA-01722: Invalid number" occurs.

I've tried different casting methods like to_number(), cast(x as numeric(x,x)), cast(as number(x,x)), cast(x as int), on both sides of the >= and also only on the left side. The reason is that the left side (TEST_IND_TABLE_BASIS.IND_VALUE) of the >= is of type varchar2. This is given by the data model.

The simplified example below works. My assumption is that SDE causes the troubles? Can you spot another possible problem in my code?

Here the simplified (working) version of my problem. In the original code, table TEST_IND_TABLE_BASIS is a spatial (SDE) table.

CREATE TABLE
    TEST_IND_THRSHLD
    (
        CD VARCHAR(64) NOT NULL,
        THRSHLD INTEGER
        --CONSTRAINT IND_THRSHLD_PK PRIMARY KEY (INDICATOR)
    );

INSERT INTO TEST_IND_THRSHLD (CD, THRSHLD) VALUES ('INDICATOR_1', 2);
INSERT INTO TEST_IND_THRSHLD (CD, THRSHLD) VALUES ('INDICATOR_2', 50)

CREATE TABLE
    TEST_IND_TABLE_BASIS
    (
        ID INTEGER NOT NULL,
        IND_VALUE VARCHAR(32)--,
        --CONSTRAINT IND_THRSHLD_PK PRIMARY KEY (INDICATOR)
    );    
INSERT INTO TEST_IND_TABLE_BASIS (ID, IND_VALUE) VALUES (1,'1.362');    
INSERT INTO TEST_IND_TABLE_BASIS (ID, IND_VALUE) VALUES (2,'2.362');                                                                         


CREATE MATERIALIZED VIEW MV_TEST AS
SELECT   TEST_IND_TABLE_BASIS.ID
        ,CASE WHEN
             cast(TEST_IND_TABLE_BASIS.IND_VALUE as numeric) >= (SELECT THRSHLD FROM TEST_IND_THRSHLD WHERE CD = 'INDICATOR_1')
             THEN 0
             ELSE 1
         END AS FLAG
FROM TEST_IND_TABLE_BASIS   

Answer 1

Your error with numeric separator Try this:

CREATE MATERIALIZED VIEW MV_TEST AS
SELECT   TEST_IND_TABLE_BASIS.ID
        ,CASE WHEN
             to_number(replace(TEST_IND_TABLE_BASIS.IND_VALUE, '.',',')) >= (SELECT THRSHLD FROM TEST_IND_THRSHLD WHERE CD = 'INDICATOR_1')
             THEN 0
             ELSE 1
         END AS FLAG
FROM TEST_IND_TABLE_BASIS

And more rigth that:

SELECT   TEST_IND_TABLE_BASIS.ID
        ,CASE WHEN
             to_number(TEST_IND_TABLE_BASIS.IND_VALUE, '9999999999D9999', 'NLS_NUMERIC_CHARACTERS='',.''') >= (SELECT THRSHLD FROM TEST_IND_THRSHLD WHERE CD = 'INDICATOR_1')
             THEN 0
             ELSE 1
         END AS FLAG
FROM TEST_IND_TABLE_BASIS

Output:

1   0
2   0