[flexcoders] Using TOTAL and AS in the SQL of a Flex app

[bredwards358]

I really don't know where to post this, as it involves both SQLite
and Flex 3, but, I'll try here first. Here's what I have in
FlexBuilder:



sqlText = "SELECT Up18RODetail.ProductID, TOTAL(Up18RODetail.Qty) AS
:qtySum " +

                                     "FROM Up18RODetail LEFT JOIN
Up18ROHeader ON Up18RODetail.ROID = Up18ROHeader.ROID " +

                                     "WHERE Up18ROHeader.DateUploaded
Between :startDate AND :endDate " +

                                     "GROUP BY Up18RODetail.ProductID " +

                                     "HAVING Up18RODetail.ProductID =
:prodProdID";



                         transactionStatement = new SQLStatement();

                         conn = new SQLConnection();

                         transactionStatement.sqlConnection = conn;

                         conn.open(dbFile);

                        
transactionStatement.addEventListener(SQLEvent.RESULT,
calcUsePercentResult);

                        
transactionStatement.addEventListener(SQLErrorEvent.ERROR,
errorHandler);

                         transactionStatement.text = sqlText;

                         transactionStatement.parameters[":startDate"] =
startDate;

                         transactionStatement.parameters[":endDate"] =
endDate;

                         transactionStatement.parameters[":qtySum"] =
qtySum; //String variable

                         transactionStatement.parameters[":prodProdID"] =
prodDataArray[i].ProductID

                         transactionStatement.execute();

Now, the SQL actually works in the SQLite database browser, however it
throws a SQL syntax error when I try to run it in my flex app and
I'm not sure why. The error reads SQLError: 'Error #3115: SQL
Error.', details:'near ":qtySum": syntax error', operation:'execute'. I
could sure use a hand here, thanks in advance…

Brian Ross Edwards

Tech-Connect LLC