Nested SQL with multiple table join does not work with wildcard searches

5 pts.
Tags:
iBATIS
Object Relational Mapping
Oracle 10g
Oracle development
Oracle SQL
ORM
Wildcards
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 &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.
ASKED: June 18, 2008  12:46 PM
UPDATED: June 18, 2008  1:02 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following