Re^3: DBD::SQLite, how to pass array in query via placeholder?

I wonder ... wouldn't something like this perform better and be easier to tweak for SQL::Abstract?

SELECT SPLIT, syn(SPLIT),
    SUM(CASE WHEN WORKMODE = 40 THEN 1 ELSE 0 END),
    SUM(CASE WHEN WORKMODE = 30 THEN 1 ELSE 0 END),
    SUM(CASE WHEN WORKMODE = 50 THEN 1 ELSE 0 END),
    SUM(CASE WHEN WORKMODE = 220) THEN 1 ELSE 0 END,
    COUNT(*),
    SUM(CASE WHEN WORKMODE = 20 THEN 1 ELSE 0 END),
    SUM(CASE WHEN WORKMODE = 80 THEN 1 ELSE 0 END)
  FROM csplit
  JOIN cagent ON cagent.ACD = csplit.ACD AND cagent.SPLIT = csplit.SPL
+IT
 WHERE csplit.ACD = $1 and csplit.SPLIT = $2
   and cagent.LOC_ID = ANY ($3) and cagent.LOGID IS NOT NULL
 GROUP BY SPLIT
[download]

Jenda
Enoch was right!
Enjoy the last years of Rome.

Comment on Re^3: DBD::SQLite, how to pass array in query via placeholder? Download Code

Replies are listed 'Best First'.
Re^4: DBD::SQLite, how to pass array in query via placeholder? by dwalin (Monk) on Apr 11, 2011 at 19:22 UTC
Jenda, I agree that query in my example may not be the most optimal, but I believe it is to be out of scope for my question. In fact, I won't use SQL::Abstract in this case just because I don't need it. I've got a library of such queries already written and tested, and it's a waste of time to rebuild them with SQL::Abstract just for the sake of it. Besides, it doesn't solve my problem with DBD::SQLite, and with DBD::Postgres these queries work as expected without SQL::Abstract so I just don't need it. Thanks for suggestion though. Regards, Alex.	[reply]
Re^4: DBD::SQLite, how to pass array in query via placeholder? by Anonymous Monk on Jun 30, 2012 at 13:47 UTC
function showRecords2() { var firstName2 = document.getElementById('f +irstName22').value; results4.innerHTML = ''; db.transaction(function(tx4) { //tx.executeSql(selectAllStatement2, [], function(tx, result +) { //var firstName2 = document.getElementById('firstName22'); //tx.executeSql('SELECT * FROM Strokes WHERE email = ?', [ema +il], renderResults); // tx4.executeSql('SELECT * FROM Contacts WHERE firstNam +e="Joo" ', [], function(tx4, result4) { tx4.executeSql('SELECT * FROM Contacts WHERE firstName="' + +firstName2 + '" ', [], function(tx4, result4) { dataset = result4.rows; // report(result.rows[0].firstName, result.rows[0].lastName) +; for (var i = 0, item = null; i < dataset.length; i++) { item = dataset.item(i); results4.innerHTML +='<table width="320" border="7"><tr +> <td style="font-size:18px" width="90">' + item['id'] + '</td>' ++ '<td width="30" style="font-size:17px">' + item['lastName'] + '</td +>' + '<td>' + '<a href="#" onclick="loadRecord('+i+')">Edit -- </a +> ' + '</td>' + '<td>' + '<a href="#" onclick="deleteRecord('+it +em['id']+')">Delete</a></td></tr></table>'; //results.innerHTML += // '<li>' + item['lastName'] + ' , ' + item['firstNam +e'] + ', ' + item['phone'] + '----------- <a href="#" onclick="loadRe +cord('+i+')">edit</a></li>'; } }); }); [download]	[reply] [d/l]