> wildcard parameter like qu* Should not it be Like qu% ? Turn on logging for connection and iBatis and check what SQL is actually executed and whether it returns anything when executed from TOAD.
On Wed, Jun 18, 2008 at 3:13 AM, <[EMAIL PROTECTED]> wrote: > > 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 ?? > > 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 &lt; #ROW_TO_END# ) > WHERE RNUM &gt; #ROW_TO_START# > > </SQLstatement> > > I look forward to any input from the iBatis user community. > > regards, > > Mason Yu Jr. > > > > ******************************************************************* > > This email and any files transmitted with it are confidential > and intended solely for the use of the individual or entity > to whom they are addressed. If you have received this > email in error please notify the sender by replying to this > email and then delete it from your system. > > No reliance may be placed upon this email without written > confirmation of its contents and any liability arising from > such reliance without written confirmation is hereby > excluded. > > JRI America > > ******************************************************************* > >
