ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From liny <innocentl...@gmail.com>
Subject Oracle10g date or timestamp question
Date Thu, 16 Oct 2008 07:50:56 GMT

Hi,

I have a table which has a receive_dttm column in DATE type.
When using Oracle 8i, iBatis can get SQL result.
But after upgrading to Oracle10g, the same SQL command can't get result and
no exception thrown.
Below is my SqlMap.xml segment:

  <select id="getInvoiceWaferData" parameterClass="invoiceWaferQuery"
resultMap="invoiceWaferMap">
    select INVOICE_DATE, EXT_DELIVERY, MATERIAL, BATCH, DELIVERY_QTY,
BATCH_NO, 
           sum(QTY) qty, DIE_NAME, 
           sum(BOXES) boxes
    from
    (
      select * from acm_wafer_invoice sap,acm_wafer_data info
      where sap.batch = info.batch_no(+)
        and sap.material = info.die_name(+)
        <isParameterPresent>
          <isNotNull prepend="and" property="batchNo">
            info.batch_no like '%' || #batchNo# || '%'
          </isNotNull>
          <isNotNull prepend="and" property="dieName">
            info.die_name like '%' || #dieName# || '%'
          </isNotNull>
          <isNotNull prepend="and" property="fromDate">
            info.receive_dttm <![CDATA[ >= ]]> #fromDate#
          </isNotNull>
          <isNotNull prepend="and" property="toDate">
            info.receive_dttm <![CDATA[ <= ]]> #toDate#
          </isNotNull>
        </isParameterPresent>
      union
      select * from acm_wafer_invoice sap,acm_wafer_data info
      where sap.batch(+) = info.batch_no
        and sap.material(+) = info.die_name
        <isParameterPresent>
          <isNotNull prepend="and" property="batchNo">
            info.batch_no like '%' || #batchNo# || '%'
          </isNotNull>
          <isNotNull prepend="and" property="dieName">
            info.die_name like '%' || #dieName# || '%'
          </isNotNull>
          <isNotNull prepend="and" property="fromDate">
            info.receive_dttm <![CDATA[ >= ]]> #fromDate#
          </isNotNull>
          <isNotNull prepend="and" property="toDate">
            info.receive_dttm <![CDATA[ <= ]]> #toDate#
          </isNotNull>
        </isParameterPresent>
    )
    group by INVOICE_DATE, EXT_DELIVERY, MATERIAL, BATCH, DELIVERY_QTY,
BATCH_NO, 
             DIE_NAME
  </select>

Parameter class, invoiceWaferQuery, has getFromDate() and getToDate() that
return java.util.Date.
I don't change the table schema.
Could you help me how to solve it and why it can't get result after
upgrading to Oracle10g.
-- 
View this message in context: http://www.nabble.com/Oracle10g-date-or-timestamp-question-tp20008834p20008834.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


Mime
View raw message