Error fetching materialized view on SQL Server

[mtawk]

We have a view materialized in SQL Server and reading from Oracle.

In Oracle we have a column of type Number(38,0) which is considered a BigInteger in Teiid. So we created it of type BigInt in the materialized table structure in SQL Server.

The view is populated successfully with data, but when fetching the view we are getting the error: "The definition for BusinessModel.myview has the wrong type for column 1.  Expected biginteger, but was long."

Note that useIntegralType is set to true in OracleMetadataProcessor.

We tried to set useIntegralType to true for SQL Server, it solved the above problem but it has caused issues in other views on SQL Server (Especially the views that contain Case When columns)

[shawkins]

A Teiid BigInteger matches the Java type of the same name - an arbitrary length integral value.  In databases BigInt typically means a 64 bit integer, or Java/Teiid long.  You'll need to use a numeric(38,0) on SQL Server.

[mtawk]

We have already tried numeric(38,0), and we got the error: Expected biginteger, but was bigdecimal

[shawkins]

> We have already tried numeric(38,0), and we got the error: Expected biginteger, but was bigdecimal

By default numeric(38,0) will be mapped to bigdecimal.  With useIntegralType = true on the sql server import, then it will be mapped to biginteger.

> We tried to set useIntegralType to true for SQL Server, it solved the above problem but it has caused issues in other views on SQL Server (Especially the views that contain Case When columns)

There are a couple of thoughts here:

You could change your view definitions to omit types when possible, so that the type can be inferred from the sql - use create view V as ... or create view v (col1, col2 ...) as ... rather than create view v (col1 type, col2 type, ...) as ...

You can redefine your views to match the type expectations. 

You can use additional DDL to alter the column post import to whatever teiid type you need. 

You can log an enhancement request to allow view resolution to be more flexible with regards to type matching - generally we are trying to avoid inserting casts in the definition, but it's certainly possible to do that when the conversions are implicit.

[mtawk]

>You could change your view definitions to omit types when possible, so that the type can be inferred from the sql - use create view V as ... or create view v (col1, col2 ...) as ... rather than create view v (col1 type, col2 type, ...) as ...

That is what we are actually doing, we are not defining the datatype into the view DDL

>You can use additional DDL to alter the column post import to whatever teiid type you need.

What do you mean exactly? How can we alter a view column datatype in Teiid?

[shawkins]

> What do you mean exactly? How can we alter a view column datatype in Teiid?

I'm not referring to the view column, I'm referring to the source table column.

You may need to clarify what exactly you are saying is not working.  My understanding looks like:

create view v as select number_col, ... from oracle_table options (materialized true ...);

Such that you are using the native importer for oracle with use integral types  = true, such that number_col with native type Number(38,0) is imported as type Teiid BigInteger.  Thus the view has an expected column type of BigInteger.

You have a materialization target table in SQL Server that you need to match the schema for v.  To be wide enough the target column needs to be numeric(38,0).  If you use integral types for the SQL Server import, presumably the types will then match up - the view and the materialization target table will both be BigInteger.  You indicate that you see some other issues, so one option is to work through those.

Another option is not use integral types for the SQL Server import, but to the use targeted alters in the VDB:

ALTER FOREIGN TABLE sqlserver_table ALTER COLUMN number_col TYPE biginteger;

[mtawk]

We worked around the issue by adding cast as decimal in the view select SQL. To avoid getting the error.

>You can log an enhancement request to allow view resolution to be more flexible with regards to type matching - generally we are trying to avoid inserting casts in the definition, but it's certainly possible to do that when the conversions are implicit.

Do you want us to log an enhancement?

[shawkins]

> Do you want us to log an enhancement?

If that is something that you are interested in, yes.