Duplicate a view

[jkhoury]

Hi there,

i am wondering if it's logical to deploy a view in 2 virtual ModelMetaData. I was able to deploy it but the Materialization fails with the below exceptions.

org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate (SELECT UID FROM Sys.Tables WHERE (VDBName = VARIABLES.vdbName) AND (SchemaName IS NOT NULL) AND (Name = viewName) LIMIT 2): TEIID30345 The command of this scalar subquery returned more than one value: SELECT UID FROM Sys.Tables WHERE (VDBName = VARIABLES.vdbName) AND (SchemaName IS NOT NULL) AND (Name = viewName) LIMIT 2

The purpose is to read the view from different Materialization locations deepening on a criteria.

Regards

Message was edited by: John Khoury Wrong Expcetion

[shawkins]

It is fine to have the same view name in two different schemas.  However the sql shown above is a scalar subquery which can return at most one row.  So by effectively only specifying the name there is an exception.  When/where is this subquery running?

[jkhoury]

I am getting this error when i try to laod the view

PreparedStatement prepareStatement = teiidServer.getConnection().prepareStatement("execute SYSADMIN.loadMatView('" + viewModelName + "','" + imView.getName() + "' ) ");

  ApplicationBeanUtils.getDataBoxService().dropViewIndices(imView, webAppCache.getDomainDataDb());

  prepareStatement.executeUpdate();

[shawkins]

I don't see where that sql would be in the system procedure, what version of Teiid are you using?

[jkhoury]

8.10.0

[shawkins]

I see, yes there is bug in the procedure definition.  It has the predicate schemaName = schemaName, which the rewriter is converting to schemaName is not null.  It should be schemaName = VARIABLES.schemaName

[shawkins]

Logged as [TEIID-3468] Exception with same table name in materialization procedures - JBoss Issue Tracker which will be 8.11 Beta2.

[jkhoury]

I tried the fix and it worked..

Thanks