In a data driven approach, I have a need to recreate a view as a initial step and then have that view as a source to the rest of the flow. I have thoroughly searched the previous posts on this community, but i didn't get clue on this need, so reposting it. Can you please throw some ideas here.
Question :2
For the same, my query text for creating the view would be auto generated and would look something like
create view v_dyn as
select c1 as col1, c2 as col2,c3 as col3 from Tbl1 union all --> line 1
select c1 as col1, c2 as col2,c3 as col3 from Tbl2 union all --> line 2
xxxxxxx
xxxxxxx
select c1 as col1, c2 as col2,c3 as col3 from Tbln union all --> line n
I need to trim union all from line n just to respect the DB syntax, in oracle world I can do it with a simple rtrim select rtrim('query txt','union all) i don't find a easy way of doing it in Talend...i dont want to end up doing a substr & index because it might quickly turn out to be a big overhead on the process...any thoughts on this
Question :3
For reading the query text, i used inputfiledelimited (after triming the union all manually) and it creates a view but even after explicitly mentioning create or replace view the second time when i run the view is not getting replaced and the initial version of the view remains, any idea on this (pls see attachment : view creation)
Question :4
Instead of using inputfiledelimited tried using Fileinputraw because i know that it is just 1 whole line, but the problem is fileinputraw reads the source as an object and i am getting error saying can convert object to string, so i am not sure where and how to convert this object to string.. any suggestion in this (pls see attachment : using fileinputraw )
Please see the attachment end-to-end for an over all view of the request
Since of the Talend community users comment in the community during their break, my tip will be always to modularize your query. I can understand your point that you want to give an end to end picture of the problem. But you can build it during later conversations if needed.
Coming to the queries,
Q1:- You can very well create a query as initial step. The best approach will be to create the query during Prejob section and store the view name to a context variable. You can replace the table name with context variable, wherever you want.
Q2:- Substring is the most easy option. You will have to trim the last few characters since you know the length of string to be trimmed before hand. Another method is to store Select statements without UNION ALL. Determine the total number of statements. Use a sequence to count till n-1 and add this sequence within an if clause saying if value of sequence is less than total statement count, add UNION ALL string else add empty string. Like I said, it need more effort compared to first method.
Q3:- Could you please print the output and see whether the view is correct? Please also select Die on error checkbox in tDBrow. It will be ideal to verify with the help of DBA whether the command is triggered to DB or not, during that point of time. Most probably it could be DDL statement error.
Q4:- The best component to convert the datatypes is tConvertType. Please add the component to the flow if you are planning to use tFileRaw.
Warm Regards, Nikhil Thampi
Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved
