use the same settings several times

advertisements

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?


The documentation says:

?     A question mark that is not followed by a number creates a parameter with a number one greater than the largest parameter number already assigned. [...]
?NNN     A question mark followed by a number NNN holds a spot for the NNN-th parameter. [...]
:AAAA     A colon followed by an identifier name holds a spot for a named parameter with the name :AAAA. Named parameters are also numbered. [...] To avoid confusion, it is best to avoid mixing named and numbered parameters.