⬅ Forum: Building VoltDB Applications

Post: Using output of one query as table to query from in a stored procedure

Using output of one query as table to query from in a stored procedure

Nithya.narasimhan@hp.com

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

xin

Feb 14, 2014

Hi,

Currently, we do not support subquery but we are working on that now.

For your this problem, you can use two queries like:
Q1: "select time from usage_info where rule_id = ? order by time desc limit 1 offset ?"
Q2: select sum(volume_downloaded),msisdn from usage_info where rule_id = ? and time >= ? group by msisdn"

The problem for this workaround method is there will be more rows got aggregated if the table usage_info has duplicates for the value "time" that returned from the first query.

Please let us know if this solves your problem or not.
--
Xin