Nested SQL with multiple table join does not work with wildcard searches
Oracle development, Wildcards, Oracle SQL, Oracle 10g, iBATIS, ORM, Object Relational Mapping
Dear sir(s):Currently I am working with iBatis with Oracle 10g as the backend. I
have
a number of jsps or Web pages which take various input parameters in the
format of
#xxxxxxxx# where xxxxxxxx represents the dataitem from the Javascript. In
my
sqlmap, there is a complex join involving nested SELECT statements. The
nesting works fine as one of them is used for grouping a result set of no
more than
500 rows at a time. This works fine.
The main problem with the sqlmap is that part of the SELECT statment
involves
a composite join between three Oracle tables/views. By using the backend
utility
TOAD, I can see all the individual columns and data, so there is no issue
with the
underlying data.
The problem I am facing is a subtle one. Throughout my
application, there
are various queries involving wildcard searches with the input parameters.
When
there is a query involving a single table, the wildcard parameter like qu*
will
return the result set with the values quit, queer, question quintessence,
etc.
When I use the wildcard with the multiple joins and there are logical
asisgnments
to the joined tbales, the query returns back an empty set. What gives ??
Someone had suggested using an Oracle bind variable instead of using
literals. How would this impact the existing syntax? A simpler version of this
code snippet involves no joins between tables and views and the wildcard feature
does work.
Here is the code snippet.
<SQLstatement>
SELECT * FROM ( SELECT /*+ FIRST_ROWS(500) */
B.*, ROWNUM RNUM
FROM(
SELECT
TC.TXN_CODE,
TC.DESCRIPTION TC_DESCR,
TC.CR_DB,
TM.TXN_TYPE_NAME TXN_TYPE,
TT.DESCRIPTION TT_DESCR,
TC.REVERSAL,
TC.SIGN,
TC.CHANNEL
FROM
LKP_TXN_CODES_VW TC,
LKP_TXN_TYPES_VW TT,
MAP_AML_TXN_CODES TM
WHERE
TC.TXN_CODE =
TM.POSTED_TXN_CODE (+)
AND TT.TXN_TYPE =
TM.TXN_TYPE_NAME
<dynamic>
<isNotNull
property="TXN_CODE">
<isNotNull
property="CONDITION_TXN_CODE_LIKE">
AND TC.TXN_CODE
LIKE #TXN_CODE#
</isNotNull>
<isNotNull
property="CONDITION_TXN_CODE_EQUAL">
AND TC.TXN_CODE
= #TXN_CODE#
</isNotNull>
</isNotNull>
<isNotNull
property="TC_DESCR">
<isNotNull
property="CONDITION_TC_DESCR_LIKE">
AND
TT.DESCRIPTION LIKE #TC_DESCR#
</isNotNull>
<isNotNull
property="CONDITION_TC_DESCR_EQUAL">
AND
TT.DESCRIPTION = #TC_DESCR#
</isNotNull>
</isNotNull>
<isNotNull property="CR_DB"
>
AND TC.CR_DB
= #CR_DB#
</isNotNull>
<isNotNull
property="TXN_TYPE">
AND TT.TXN_TYPE
= #TXN_TYPE#
</isNotNull>
</dynamic>
ORDER BY TXN_CODE
) B
WHERE ROWNUM < #ROW_TO_END# )
WHERE RNUM > #ROW_TO_START#
</SQLstatement>
I look forward to any input from the iBatis user community.
regards,
Mason Yu Jr.
