Re: Substitute for SUBSTR

Buck wrote:
> I am working on a system (SAP) which uses Oracle, but its language
> disallows the use of any Oracle functions, we can just use generic
> SQL.
>
> I need to do the following SUBSTR in SQL, without using SQL, comparing
> '010' against '010001'. Is there a way to do this in generic SQL?
>
> create table t_1 (
> pos_nr varchar2(3)
> );
>
> create table t_2 (
> ref_posid varchar2(10)
> );
>
> insert into t_1 (pos_nr) values ('010');
> insert into t_2 (ref_posid) values ('010001');
>
> commit;
>
> -- this is what I need to do, but substr not allowed.
> select * from t_1
> where exists (select 'match'
> from t_2
> where t_1.pos_nr = substr(ref_posid,1,3));
>
>
> -- so, how can we use "LIKE" or "BETWEEN"?
> select * from t_1
> where exists (select 'match'
> from t_2
> where t_1.pos_nr like ref_posid);
>

Why not create a view in Oracle that does use SUBSTR or anything else...then have SAP just query the view with a simple "SELECT * FROM view_name"?

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com