dimanche 3 avril 2016

sql: use of parameters

I have an sql-select (or insert) which uses the same two parameters several times. Is there a way to avoid using multiple the same parameter for every "?,?,?,?,..." in the list ?

cursor.execute(statement, list)

I could think of two named parameters but without the possibility of code-injection.

In the example below every left "?" resp. right "?" is the same string. I used the seven counts in one statement in order to get one result.

select count(case (aart like "1%")  and (adatum between ? and ?) when 1 then 1 else null end) as AufExt, 
       count(case (aart like "1%E") and (adatum between ? and ?) when 1 then 1 else null end) as AufExtE,
       count(case (aart like "1%K") and (adatum between ? and ?) when 1 then 1 else null end) as AufExtK,
       count(case (aart like "2S%") and (adatum between ? and ?) when 1 then 1 else null end) as AufInt,
       count(case (eart like "3%")  and (edatum between ? and ?) when 1 then 1 else null end) as EntExt, 
       count(case (eart like "3%K") and (edatum between ? and ?) when 1 then 1 else null end) as EntExtK,
       count(case (eart like "2S%") and (edatum between ? and ?) when 1 then 1 else null end) as EntInt
from tabelle

Related question to this problem: It looks as if no index is used in "case". Correct?

Aucun commentaire:

Enregistrer un commentaire