Teiid Designer: How I can execute the select statement with 'WHERE CLAUSE' of user command

[meltedmetal]

Dear all,

I want get the all records that will be updated in the update transformation procedure of view model.

If the user command is

UPDATE viewModel.schemaName.table1 SET column1 = 'xxx' WHERE column2 > 100;

In the view model update transformation procedure, I want to execute below SQL:

SELECT * FROM viewModel.schemaName.table1 WHERE CRITERIA;

But, the procedure validation result is :

The query is not valid.
Error:  Invalid source table - book.
Reason: Target cannot be a source to itself.

And "WHERE CRITERIA" without "TRANSLATE" is not allowed.

Will the above query statement be allowed?

OR, Other way to implement same function?

Thank you!

Best Regards,

S.Q.

[rareddy]

S.Q,

I do not understand the usefulness for the above query. Why would want to do a "select" when user issues a "update"? There are four different transformations you can supply on a view table.

1) select 2) insert 3) update 4) delete

so that you can in turn support CRUD operations. "TRANSLATE CRITERIA" is single command, that converts the user supplied criteria into transformation specific criteria, where you can specify on which command you want to apply this on. Please note that "TRANSLATE CRITERIA" only applicable for 3 & 4.  In the case of (1) Teiid query planner will automatically inline the criteria with the provided "select" transformation.

Hope this helps.

Ramesh..

[blafond]

See: http://docs.jboss.org/teiid/designer/6.0/user-guide/editing-model-objects-chapter.html#transformation-editor-section

The "Supports Updates" check-box will enable you to enter Update/Insert/Delete SQL in the transformation editor.  If you have a simple Select SQL statement, Designer will create default SQL for the update queries as a starting point. If your Select SQL is not simple, then you'll have to enter your update SQL from scratch.

Barry

[meltedmetal]

Hi, Ramesh

Thanks for your replay.

I'm sorry for missing of description of background.

The select transformation of view model table is a union query from different  two tables that has same columns.

I execute the above select query to justify which table the record is from?

The whole background is same with the Solution 2 in the my last discussion http://community.jboss.org/message/566899#566899.

And I have read your reply to it. You said that the solution 2 is not fit for my requirement.

By the way, I thought my last discussion is some complicated and long. So I posted this discussion. Sorry for the confusion.

Best Regards,

S.Q.

[meltedmetal]

Hi, Barry

Thanks for your help.

Because the select transformation of view model table is a union query from different  two tables that has same columns, I need to write my update SQL from scratch.

After just skimming it, I found it is useful for writing transformation, however maybe it could not help me on my problem of changing the default criteria mapping.

I will read the document you mentiond carefully.

Best Regards,

S.Q.

[rareddy]

S.Q,

It is good that you are trying multiple things, it is a very valid approach. But in this case you would be doing lot more work about "source_name" stuff and updating based on it, essentially trying replicate what multi-source already provides you.

To clarify, you can do a "select" in the update transformation to fetch a cursor or single value, however you can only use the "TRANSLATE CRITERIA" on the "update/delete" statements.

Ramesh..

[meltedmetal]

Hi, Remesh

Thanks for your suggestion.

In fact, this solution was my initial solution. Now I have stopped working on this.

Best Regards,

S.Q.