?
Solved

How to use the sub string with inner join in Oracle Sql

Posted on 2014-08-14
7
Medium Priority
?
731 Views
Last Modified: 2014-08-18
Hi,

I am trying to use the sub string function with inner join in sql.  How do I implement it with inner join?

For example my queries looks something like this.

"SELECT ATT_NUM, ENT_NUM FROM A INNER JOIN U ON A.ATT_NUM = U.ATT_NUM INNER JOIN E ON E.ENT_NUM = U.ENT_NUM WHERE E.ENT_NAME = '" + _entname + "' AND U.UNIQUENAME '"+ _uniquename + "'", con
where Substring in ('E','C');

Open in new window


Thanks.
0
Comment
Question by:developer2012
7 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40262010
There seems to be something missing here: AND U.UNIQUENAME '"+ _uniquename + "'"

Like an '=', '>', etc..

What is ,con after that?

There are two WHEREs.

Short of that, there shouldn't be a problem using part of a string in a join.

We just need to know what Substring is?
0
 

Author Comment

by:developer2012
ID: 40262025
con is a connection string(I am using c#.net too).\

I need to retrieve the results where the string starts with E or C
0
 

Author Comment

by:developer2012
ID: 40262029
Extra where is a mistype.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40262039
select ... where (column_name like 'E%' or coluimn_name like 'C%') and ...

or

... and substr(column_name,1,1) in ('E','C')
0
 
LVL 32

Expert Comment

by:awking00
ID: 40263163
Can you elaborate a little bit more about what you want to accomplish? Are you looking to submit a query through an OracleConnection where the connection string begins with 'E' or 'C'? Can you post more of your C# code?
0
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 40263338
Hi!

Note that using substring on the left side of a comparison in a where clause results in a poor
performance as regular indexes can't be used. Functional indexes gives you some performance
but not as fast as regular indexes.

Regards,
     Tomas Helgi
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40263350
In this case indexes likely won't matter since the like only uses a single character before the wildcard.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question