ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Leonardo Holanda" <...@atlantico.com.br>
Subject dynamic "prepend" problem
Date Tue, 15 Mar 2005 14:40:38 GMT
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.

 


 

 


Mime
View raw message