Running Alter View SQL command giving issues
rajkota Aug 27, 2012 9:34 AMHi,
I am using Teiid 7.7 in my work. When I try to run 'alter view' SQL expression from Elcipse Data Source explorer / through a Java class against a VDB then it is not running as expected, giving the below error.
org.teiid.jdbc.TeiidSQLException: modelName."viewName" is not a valid view.
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113)
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70)
at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:636)
at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:62)
at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:547)
at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:139)
at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:543)
at org.teiid.jdbc.StatementImpl.executeQuery(StatementImpl.java:334)
Here is the Alter View SQL expression that I am trying to invoke:
alter view "modelName"."viewName" as SELECT col1, col2, col3, col4, col5 FROM (call myProceduretoReadFiles.getTextFiles(metadata.txt')) as F, TEXTTABLE(F.file COLUMNS col1 string, col2 string, col3 string, col4 string, col5 string HEADER 1) AS t"
Here is my view definition inside model file:
<models>
...
<model name="modelName">
<view name="viewName">
<columns>
<column name="col1" type="varchar"/>
<column name="col2" type="varchar"/>
<column name="col3" type="varchar"/>
<column name="col4" type="varchar"/>
<column name="col5" type="varchar"/>
</columns>
<definition>
SELECT col1, col2, col3, col4, col5 FROM (call myProceduretoReadFiles.getTextFiles('metadata.txt')) as F, TEXTTABLE(F.file COLUMNS col1 string, col2 string, col3 string, col4 string, col5 string HEADER 1) AS t
</definition>
</view>
</model>
<models>
When I run select * from "modelName"."viewName", then query is returning the proper response.
There might be some usage issue here while using 'alter view' and I could not able to get some more technical details on 'Alter View' usage with some samples over Teiid 7.7 documentation / forum. Please help me giving a way to make my alter view runnable.
Thanks,
Raj