Using output of one query as table to query from in a stored procedure
Feb 14, 2014
Hi,
I have a stored procedure called SelectFromCustomer.I want to use the VoltTable , I got as output from one SQL in this procedure as the Table to query from for my next query within the procedure.Is this possible? If not is there any work around for this problem ?
Please see my code below -
public class SelectFromCustomer extends VoltProcedure {
public final SQLStmt sql = new SQLStmt(
"select msisdn,volume_downloaded from usage_info where rule_id = ? order by time desc limit ?;");
public VoltTable[] run(long ruleId,long limit,String operation) throws VoltAbortException {
voltQueueSQL(sql,ruleId,limit);
VoltTable[] tmpArray = voltExecuteSQL();
VoltTable tmptable = tmpArray[0];
SQLStmt tempStmt = new SQLStmt("select sum(volume_downloaded),msisdn from "+ tmptable +" group by msisdn");
voltQueueSQL(tempStmt);
VoltTable[] tmpArray1 = voltExecuteSQL();
return tmpArray1;
}
}
I want to use tmpTable as the table for the sqlStatement tempStmt.
Thanks in advance
Nithya
I have a stored procedure called SelectFromCustomer.I want to use the VoltTable , I got as output from one SQL in this procedure as the Table to query from for my next query within the procedure.Is this possible? If not is there any work around for this problem ?
Please see my code below -
public class SelectFromCustomer extends VoltProcedure {
public final SQLStmt sql = new SQLStmt(
"select msisdn,volume_downloaded from usage_info where rule_id = ? order by time desc limit ?;");
public VoltTable[] run(long ruleId,long limit,String operation) throws VoltAbortException {
voltQueueSQL(sql,ruleId,limit);
VoltTable[] tmpArray = voltExecuteSQL();
VoltTable tmptable = tmpArray[0];
SQLStmt tempStmt = new SQLStmt("select sum(volume_downloaded),msisdn from "+ tmptable +" group by msisdn");
voltQueueSQL(tempStmt);
VoltTable[] tmpArray1 = voltExecuteSQL();
return tmpArray1;
}
}
I want to use tmpTable as the table for the sqlStatement tempStmt.
Thanks in advance
Nithya