Here is the query I am rebuilding with an OPENQUERY to the Oracle tables it it runs 5 times as fast but I am having trouble with this one piece swithcing it to Oracle syntax. My sql syntax does not work for this. I need only the number is in bewteen the () and it then needs to be converted to numeric so that I can add to the ASGN_STR column. This column at.AUTH_DOC_POSN_TITLE has a numeric string at the end of it as such

OVERSTRENGTH SSG (1)

Maintenance SGT (5)

Cook SSG (1)

Truck DriverSGT (2)

Computer Repair SPC (8)

Heavy Maintenance SSG (0)

PBO SGT (1)

Drill SPC (4)

Here is current code I am working on but don't know how to do this in Oracle syntax.

Select* from OPENQUERY(SIDPERS, '

SELECTat.UPC as UIC,

ut.Addr_City as City,

at.Auth_Para_Dsg as PARA,

at.Auth_Line_Dsg as LINE,

at.GRADE,

substr(at.POSC, 1,4) as DMOS,

case at.AUTH_PERS_IDENT when ''E'' then ''M'' when ''W'' then ''M'' when ''O'' then ''M'' else ''I'' end GENDER,

at.AUTH_STR,

at.ASGN_STR,

at.AUTH_DOC_POSN_TITLE,

case when at.AUTH_DOC_POSN_TITLE like ''(%)'' then substr(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0') = 0 THEN 0

FromSIDPERS.PERS_UNIT_TBL ut Inner join

SIDPERS.PERS_AUTH_STR_TBL at on at.UPC = ut.UPC')