Insert,Delete and Update wont support in SQL-Transform in BODS4.1

[vijy_mukunthan]

Dear Experts

In the SQL-Transform when i use select statement with multiple nested conditions it works fine. But when i use delete statement its gives syntax error. Does it mean that SQL-Transform wont support any Insert, Delete or Update Statement? How to achieve these statements in SQL-Transform then?.

  When i use the same delete statement in oracle it works fine and gives correct result.

Regards

Vijay Mukunthan

[former_member187605 · Accepted solution]

The SQL transform can only be used as a source in a dataflow. So by definition, only select operations are possible.

Insert, delete and update operations are applied to targets only.

[vijy_mukunthan · Accepted solution]

Hi Dirk

  Thanks for your reply. I am testing some scenarios. For that scenario the easiest way to achieve the result is delete or update certain data in oracle database. But unfortunately delete or update gives syntax error. same syntax works perfect in oracle SQL developer. So i doubted that SQL-Transform supports Insert, Update or Delete Statement or not.

Regards

Vijay Mukunthan

[former_member187605 · Accepted solution]

Note that the Data Integrator component of DS is not a SQL-tool but an ETL-tool, slightky different way of thinking required . We achieve your results with a dataflow as:

Specify a where-clause in the Query transform to filter out the records you want to delete. Then change  the row type from Normal to Delete in the Map Operation transform.

[vijy_mukunthan · Accepted solution]

Hi Dirk

  Thanks for your reply. From your reply i came to know Update or delete or insert wont work in SQL-Transformation. So deleting or inserting or updating i should Map_CDC operation rather than SQL queries.

Thanks

Vijay Mukunthan