Advertisement

06.17.2008 at 03:10PM PDT, ID: 23493442
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
7.8

SQL query with join between 2 tables not working

Asked by j2eemason in Oracle 10.x

Tags: , , , ,

I have a sysytem with J2EE application running with an Oracle back end. I have a query screen
which takes a transacion code #TXN_CODE#, description as #TC_DESCR# and so forth.  What
is happening is that there are 2 views and a table involved.  The outer most SELECT is to
capture a group of no more than 500 rows from a given result set.   This works fine.  The Oracle
objects and views exist and there is valid data in each one.  There is dynamic Javascript  with
&lt and &gt which box in the wildcard searches.  In this SQL statement there are 4 search
parameters.   In 2 of these sections, there is a dynamic call for the wildcard search.  So
for example the TXN_CODE field in the Web page someone enters q12*, after this SQL
staement is executed the result set should return all rows whose TXN_CODE is q123, q1234, q12345,
q123444, q1234456, q12345678 and so forth.  The CR_DB and TXN_TYPE work fine and there are no technical issues.  The problem is with the first 2 search parameters TXN_CODE and TN_DESCR
with the wild card provision.  This is a very subtle bug which I need some assitance.  Thanks !!!!Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:

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#
 
Keywords: SQL query with join between 2 tables …
Title
1 Oracle old joins (+) vs new
2 Oracle SQL joining multiple fields into o…
3 Oracle  update sql
4 Oracle join weirdness
5 J2EE
6 Oracle join works fine in TOAD, but n…
 
Loading Advertisement...
 
[+][-]06.17.2008 at 03:16PM PDT, ID: 21807948

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.17.2008 at 09:49PM PDT, ID: 21810146

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.17.2008 at 10:02PM PDT, ID: 21810186

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.18.2008 at 05:32AM PDT, ID: 21812109

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.18.2008 at 06:32AM PDT, ID: 21812643

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.18.2008 at 06:37AM PDT, ID: 21812685

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.18.2008 at 06:54AM PDT, ID: 21812869

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.18.2008 at 06:58AM PDT, ID: 21812913

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.18.2008 at 07:30AM PDT, ID: 21813272

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Oracle 10.x
Tags: Oracle, 10g, Windows XP, SQL, IE 6
Sign Up Now!
Solution Provided By: sdstuber
Participating Experts: 2
Solution Grade: A
 
 
[+][-]06.18.2008 at 09:47AM PDT, ID: 21814745

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.20.2008 at 11:19PM PDT, ID: 21836620

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
[+][-]06.22.2008 at 09:48PM PDT, ID: 21843551

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.23.2008 at 05:25AM PDT, ID: 21845398

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.25.2008 at 03:41AM PDT, ID: 21864188

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
[+][-]06.27.2008 at 05:38AM PDT, ID: 21882970

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628