cast() or convert() substring to number in SQL join

[ruegsegs_us_ibm_com]

I have a string col that I want to use as a key to join with an integer col in another table.  The join is actually on a substring of the string.  Can I do that in 1 query, or do I have to split it up some and “re-cast” the string?

In the sql join below, a.int_id is an integer which needs to match with the substring in b.string_id.  (Yes, that appears to be my only option for
joining!  These are 2 different systems and they are not modeled well, apparently.)

I'm trying to re-cast that sub-string into an integer and can't figure it out.  I'm connecting to an Oracle v11 database.      

create table myjoin as

select *

from schema1.table1 a

inner join schema2.table2 b on a.int_id = substr(b.string_id,5)

;

quit;

I think I want to re-cast right in the join.  But maybe not.  Maybe it's better to do some sort of nested query where I re-cast the substring in the select and then join to the integer.

[RW9 · Accepted solution]

Hi,

So you are using pass through yes?  I.e.:

proc sql;

     connect to oracle (path=..);

     create table WANT as

     select * from connection to oracle (...);

quit;

You should then be able to do that with the to_number function: Oracle/PLSQL: TO_NUMBER Function

inner join schema2.table2 b on a.int_id = to_number(substr(b.string_id,5))

Of course if it was SAS then you would do:

inner join schema2.table2 b on a.int_id = input(substr(b.string_id,5),best.)

[ruegsegs_us_ibm_com]

good question.  I was not using pass-through.  (I'm new to this setup, so I'll investigate more into that.)

schema1 and schema2 are SAS librefs which point to oracle tables. 

[DBailey]

You should review the requirements that SAS has to have to allow Oracle to do the work,if possible.  For example, if schema1 and schema2 are on the same oracle instance and are accessed with the same userid, then sas will pass the query to oracle to execute as long as there are no sas functions that it can't translate into oracle syntax.  Unfortunately, I believe that substr is one of those functions that while Oracle has an equivalent function, its use as criteria will cause SAS to bring data out of oracle prior to performing the join.

If you use the pass-through, I believe oracle has some implicit conversion rules that would allow the comparison int_id=substr(string_id,1,5).

[ruegsegs_us_ibm_com]

Thanks.  I didn't know I could put the SAS input() function in the join.  That worked perfectly.  Thanks!