I wrote this dynamic select bellow
select
*
from ECC_CHAMADO
<dynamic prepend="WHERE">
<isNotNull prepend="OR" property="numero">
CHAMADO_ID = #numero#
</isNotNull>
<isNotNull prepend="OR" property="numeroSerie">
(NUMERO_SERIE = #numeroSerie#
<isNotNull prepend="AND" property="numero">
(select count(*) from ECC_CHAMADO where CHAMADO_ID = #numero#) = 0
</isNotNull>
)
</isNotNull>
<isNotNull prepend="OR" property="clienteId">
((
<isNotNull prepend="OR" property="classificacaoChamadoId">
(ENTI_CD = #clienteId# AND CLASSIFICACAO_CHAMADO_ID = #classificacaoChamadoId#)
</isNotNull>
<isNotNull prepend="OR" property="classificacacoProblemaId">
(ENTI_CD = #clienteId# AND CLASSIFICACAO_PROBLEMA_ID = #classificacacoProblemaId#)
</isNotNull>
<isNotNull prepend="OR" property="statusId">
(ENTI_CD = #clienteId# AND STATUS_ID = #statusId#)
</isNotNull>
<isNotNull prepend="OR" property="canalId">
(ENTI_CD = #clienteId# AND CANAL_ID = #canalId#)
</isNotNull>
)
<isNotNull prepend="AND" property="numero">
(select count(*) from ECC_CHAMADO where CHAMADO_ID = #numero#) = 0
</isNotNull>
<isNotNull prepend="AND" property="numeroSerie">
(select count(*) from ECC_CHAMADO where NUMERO_SERIE = #numeroSerie#) = 0
</isNotNull>
)
</isNotNull>
</dynamic>
If no parameters are null I was expecting this prepared statement to be produced:
select * from ECC_CHAMADO WHERE
CHAMADO_ID = ?
OR
(NUMERO_SERIE = ? AND (select count(*) from ECC_CHAMADO where CHAMADO_ID = ?) = 0)
OR
(
(
(ENTI_CD = ? AND CLASSIFICACAO_CHAMADO_ID = ?)
OR
(ENTI_CD = ? AND CLASSIFICACAO_PROBLEMA_ID = ?)
OR
(ENTI_CD = ? AND STATUS_ID = ?)
)
AND
(select count(*) from ECC_CHAMADO where CHAMADO_ID = ?) = 0
AND
(select count(*) from ECC_CHAMADO where NUMERO_SERIE = ?) = 0
)
But the produced prepared statement has a syntax SQL error with na extra "OR" as follows:
select * from ECC_CHAMADO WHERE
CHAMADO_ID = ?
OR
(NUMERO_SERIE = ? AND (select count(*) from ECC_CHAMADO where CHAMADO_ID = ?) = 0)
OR
(
(OR
(ENTI_CD = ? AND CLASSIFICACAO_CHAMADO_ID = ?)
OR
(ENTI_CD = ? AND CLASSIFICACAO_PROBLEMA_ID = ?)
OR
(ENTI_CD = ? AND STATUS_ID = ?)
)
AND
(select count(*) from ECC_CHAMADO where CHAMADO_ID = ?) = 0
AND
(select count(*) from ECC_CHAMADO where NUMERO_SERIE = ?) = 0
)
How can I correct this? For now, i'm writing a constant _expression_ resulting 'false' after the opening parenthesis, but it's not very elegant way... Ibatis wasn't supposed to detect that this OR is unnecessary?
Thank you.
