Dynamic SQL Generation

[Anonymous]

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

[Anonymous]

Hi Luke
Create job as the following images.
Code in tJava

String tforeach_3 = "";
String temp =((String)globalMap.get("tForeach_1_CURRENT_VALUE"));
if
(!temp.equals("40")){
 tforeach_3 =(Integer.valueOf(temp)-40)+"";
}
if(!temp.equals(context.temp))
context.result += "  ,raw_data_long.Question_No" + temp
+ ", raw_data_long.CT_Ans_" + (String)globalMap.get("tForeach_2_CURRENT_VALUE") + tforeach_3;
else
context.result += " ,raw_data_long.CT_Ans_" + (String)globalMap.get("tForeach_2_CURRENT_VALUE") + tforeach_3 ;
context.temp = temp;
System.out.println(context.result);
System.out.println("");

Regards,
Pedro

[Anonymous]

Perfect! Thanks for this! A lot of good tricks and techniques in this answer along with the solution to my issue. I love this tool 🙂

[Anonymous]

One last question on this. Is there a way to run this job to completion and then use the SQL that is built by it? Basically I want to use this SQL in a mySQL input component. Would this be a good place for a joblet?

[Anonymous]

Hi
Yes. If you want to use tMysqlInput, type context.result in the query of it.
Regards,
Pedro

[Anonymous]

Hello. So I have set up all my logic to generate a single SQL query and then run it through some components to execute it, transform the data and then update the DB. The issue I have having is my dynamic SQL look like this:
select raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Reg__Date ,raw_data_long.Question_No40 as Question_No, raw_data_long.CT_Ans_1 as CT_Ans_1 ,raw_data_long.CT_Ans_2 as CT_Ans_2 ,raw_data_long.CT_Ans_3a as CT_Ans_3a ,raw_data_long.CT_Ans_3b as CT_Ans_3b ,raw_data_long.CT_Ans_3c as CT_Ans_3c ,raw_data_long.CT_Ans_4 as CT_Ans_4 ,raw_data_long.CT_Ans_5 as CT_Ans_5 ,raw_data_long.CT_Ans_6 as CT_Ans_6 ,raw_data_long.CT_Ans_7 as CT_Ans_7 ,raw_data_long.CT_Ans_8 as CT_Ans_8 ,raw_data_long.CT_Ans_9 as CT_Ans_9 FROM raw_data_long
When I run the job I get this error:
Exception in component tMysqlInput_1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'raw_data_long' in field list
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
But if I don't use the dynamic SQL (context.result) and instead paste the SQL that context.result contains and run it, everything works fine. What is the difference between running the Input Component with contex.result as the query, or by putting what context.result contains?
Thanks,
Luke

[Anonymous]

I tried removing the table name and I get the following value in context.result before I run it in the Input:
select _Board_Name, School_Name, Reg__Date , Question_No40 as Question_No, CT_Ans_1 as CT_Ans_1 ,CT_Ans_2 as CT_Ans_2 ,CT_Ans_3a as CT_Ans_3a ,CT_Ans_3b as CT_Ans_3b ,CT_Ans_3c as CT_Ans_3c ,CT_Ans_4 as CT_Ans_4 ,CT_Ans_5 as CT_Ans_5 ,CT_Ans_6 as CT_Ans_6 ,CT_Ans_7 as CT_Ans_7 ,CT_Ans_8 as CT_Ans_8 ,CT_Ans_9 as CT_Ans_9 FROM raw_data_long
But the result is:
Exception in component tMysqlInput_1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '_Board_Name' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
If I run the query in the DB manually, it works fine. Not sure whats doing on here.
Luke

[Anonymous]

OK, I fixed it. Not sure why, but if I appended the table name in the component, it works fine.
context.result + " from raw_data_long"

[Anonymous]

Actually I am still getting this issue.
context.result contains the following before the mySQL input that uses it:
select _Board_Name, School_Name, Reg__Date , Question_No40 as Question_No, CT_Ans_1 as CT_Ans_1 ,CT_Ans_2 as CT_Ans_2 ,CT_Ans_3a as CT_Ans_3a ,CT_Ans_3b as CT_Ans_3b ,CT_Ans_3c as CT_Ans_3c ,CT_Ans_4 as CT_Ans_4 ,CT_Ans_5 as CT_Ans_5 ,CT_Ans_6 as CT_Ans_6 ,CT_Ans_7 as CT_Ans_7 ,CT_Ans_8 as CT_Ans_8 ,CT_Ans_9 as CT_Ans_9 FROM raw_data_long
When I run this report against the DB manually I get results back.
But when the component runs it in the job I get the following error:
select _Board_Name, School_Name, Reg__Date , Question_No40 as Question_No, CT_Ans_1 as CT_Ans_1 ,CT_Ans_2 as CT_Ans_2 ,CT_Ans_3a as CT_Ans_3a ,CT_Ans_3b as CT_Ans_3b ,CT_Ans_3c as CT_Ans_3c ,CT_Ans_4 as CT_Ans_4 ,CT_Ans_5 as CT_Ans_5 ,CT_Ans_6 as CT_Ans_6 ,CT_Ans_7 as CT_Ans_7 ,CT_Ans_8 as CT_Ans_8 ,CT_Ans_9 as CT_Ans_9 FROM raw_data_long
Any ideas as to why this would happen?
Luke

[Anonymous]

When I add the table name my context.variable has the following SQL in it:
SELECT raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Reg__Date,raw_data_long.Question_No40 as Question_No, CT_Ans_1 as CT_Ans_1 ,raw_data_long.CT_Ans_2 as CT_Ans_2 ,raw_data_long.CT_Ans_3a as CT_Ans_3a ,raw_data_long.CT_Ans_3b as CT_Ans_3b ,raw_data_long.CT_Ans_3c as CT_Ans_3c ,raw_data_long.CT_Ans_4 as CT_Ans_4 ,raw_data_long.CT_Ans_5 as CT_Ans_5 ,raw_data_long.CT_Ans_6 as CT_Ans_6 ,raw_data_long.CT_Ans_7 as CT_Ans_7 ,raw_data_long.CT_Ans_8 as CT_Ans_8 ,raw_data_long.CT_Ans_9 as CT_Ans_9 FROM raw_data_long
When the job runs I get:
Exception in component tMysqlInput_1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'raw_data_long' in field list
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
Not only does this SQL work in the DB directly, but if I put this into the mySQLInput query it works. However when I put the context variable in the query box, I get error. I can't see what the difference is,