I have some SQL that looks like this:
SELECT
raw_data_long._Board_Name,
raw_data_long.School_Name,
raw_data_long.Question_No40,
raw_data_long.CT_Ans_1,
raw_data_long.CT_Ans_2,
raw_data_long.CT_Ans_3a,
raw_data_long.CT_Ans_3b,
raw_data_long.CT_Ans_3c,
raw_data_long.CT_Ans_4,
raw_data_long.CT_Ans_5,
raw_data_long.CT_Ans_6,
raw_data_long.CT_Ans_7,
raw_data_long.CT_Ans_8,
raw_data_long.CT_Ans_9,
raw_data_long.Question_No41,
raw_data_long.CT_Ans_11,
raw_data_long.CT_Ans_21,
raw_data_long.CT_Ans_3a1,
raw_data_long.CT_Ans_3b1,
raw_data_long.CT_Ans_3c1,
raw_data_long.CT_Ans_41,
raw_data_long.CT_Ans_51,
raw_data_long.CT_Ans_61,
raw_data_long.CT_Ans_71,
raw_data_long.CT_Ans_81,
raw_data_long.CT_Ans_91,
... It goes up to Question 50 following this pattern.
I would like to figure out a way to dynamically generate this in a DB Input Component. I have been playing with nesting ForEach components, but am not really getting the result I want (currently playing around with something like this):
String foo = "select raw_data_long._Board_Name, " +
" raw_data_long.School_Name, "
+ " raw_data_long.Question_No" + (String)globalMap.get("tForeach_1_CURRENT_VALUE")
+ ", raw_data_long.CT_Ans_" + (String)globalMap.get("tForeach_2_CURRENT_VALUE") + (String)globalMap.get("tForeach_3_CURRENT_VALUE")
+ ", ";
My current result looks like:
select raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Question_No40, raw_data_long.CT_Ans_1,
select raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Question_No40, raw_data_long.CT_Ans_11,
select raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Question_No40, raw_data_long.CT_Ans_12,
select raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Question_No40, raw_data_long.CT_Ans_2,
select raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Question_No40, raw_data_long.CT_Ans_21,
....
Ideally I would like the first foreach to run, then iterate through all the 2nd and 3rd foreach values rather than execute once for each loop. Can loops be configured in this way?
Is there a better idea on how to do this?
Thanks,
Luke
That didn't help.
What did help was making the query look like this:
"select " + context.result + " from raw_data_long"
I don't get errors any more, but the columns are returning null.
Email sent. I might have fixed it by doing all the SQL manipulation in the tJava components. But let me know what you think, I will post all the results here for anyone else with similar issues. Thanks! Luke
Hello all, I have a t_MSSQLInput_5 Name: JournalNew Component that contains a query, this query shall be replaced by a dynamic query at job execution. I generate a SQL Statement in a t_Java component. (see code below) The statement gets stored in a Global Variable tMSSqlInput_5_QUERY. To control it, the variable tMSSqlInput_5_QUERY is used as output in a tMsgBox. EVERYTHING OK, the variable tMSSqlInput_5_QUERY contains the new dynamic query But tMSSqlInput_5 executes the original (old) query instead the new dynamic one Now my problem: I do not manage to feed my t_MSSQLInput_5 Name: JournalNew Component with this new generated query. Please explain. How to replace an existing old query with the dynamic generated query. I would like to do that in the tJava component.here the existing code String buff = ((String)globalMap.get("Journal_MaxID")); String buff2 = "SELECT dbo.journal.TabIndex, dbo.journal.ConnectionID FROM dbo.journal WHERE dbo.journal.TabIndex > "; String buff3 = buff2 + buff; globalMap.put("tMSSqlInput_5_QUERY",(String)buff3);
