• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2357
  • Last Modified:

SQL SUBSTR Syntax with Creating a Join

I have an SQL requiring an inner join.  The hitch is for table A the join condition (ON clause) is part of a string.  Specifically I want to make a join looking similar to this...

"from table1  inner join  table2 on table1.ordernum = substr(table2.ordertrans,1,10)"

I can not get the syntax to work.  To explain a little further into what I was attempting to do in the example...
Within a string of text in table2 you can find table1.ordernum.  The string is the first 10 characters which will make the join, the rest of the characters after 10 are not useful in this situation.
0
Derek_Indianapolis
Asked:
Derek_Indianapolis
  • 6
  • 4
  • 2
  • +1
1 Solution
 
derekkrommCommented:
from table1  inner join  table2 on table1.ordernum = left(table2.ordertrans, 10)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the syntax looks fine, what error do you get?
what data types are involved?
what database are you working with?
0
 
Patrick MatthewsCommented:
Try:

FROM table1 INNER JOIN table2 ON table1.ordernum = LEFT(table2.ordertrans, 10)
0
Independent Software Vendors: 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!

 
derekkrommCommented:
if its a casting error, try this:

from table1  inner join  table2 on cast(table1.ordernum as varchar(10)) = left(table2.ordertrans, 10)
0
 
Derek_IndianapolisAuthor Commented:
I am working with Oracle through the Oracle program SQL Developer.  I am working with table1 being a VARCHAR2(10 BYTE) and table 2 benig a VARCHAR2(20 BYTE)

When I ran the LEFT it gave an error listed as an "Invalid Identifier"...

select b.orderkey, b.orderlinenumber, b.sku
from table1 inner join table2 on table1.orderkey = left(table2.sourcekey,1,10)
0
 
derekkrommCommented:
take out the "1,"

select b.orderkey, b.orderlinenumber, b.sku
from table1 inner join table2 on table1.orderkey = left(table2.sourcekey,10)
0
 
Derek_IndianapolisAuthor Commented:
That gives the same result as "LEFT is an invalid Identifier"
0
 
derekkrommCommented:
your substr should certainly work in oracle. what happens if you do this:

select substr(table2.sourcekey, 1, 10) from table2
0
 
Derek_IndianapolisAuthor Commented:
Derekkkromm,

That was my starting point.  It gives me exactly what I want to use as the joining ON for table 1... however, the program does not like me using SUBSTR within the ON
0
 
derekkrommCommented:
from table1, table2
where table1.ordernum = substr(table2.ordertrans,1,10)

?
0
 
Derek_IndianapolisAuthor Commented:
Thanks for sticking with me on that one Derekkromm.  I wish I had enough money or purpose to purchase TOAD.
0
 
derekkrommCommented:
no problem. not sure why oracle doesn't allow the substr in the ON. glad i could help!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>not sure why oracle doesn't allow the substr in the ON.
it does allow, but maybe not for some older version...
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now