Re: can you mix a substr function with a where exists?

Why dont u use this query

select * from t2
where t2.i_acct_nbr in (select distinct substr(t1.i_acct_nbr, 1, 7)

                        from t1)

This would pretty much match the records u r looking for.

Cheers

Atrangi

> I have a table T1 that has a column with an account number
concatenated with
> other information this account number ranges from 5-10 digits.
> 45676541234 = acct: 4567654 plus 1234
>
> I have another table T2 that has the account numbers by then selves
with other
> columns. acct 4567654
>
> i want find all the accounts in T2 that exist in T1 and bring out
information
> the follwoing sql will do
>
> select I_ACCT_NBR, C_ACCT_NBR, BILLING_SYSTEM, WHERE_BILLING from
> T2
> where exists (select studyid from T1 where T1.studyid
> = C_acct_nbr)
>
> however, it will not work because all the accounts in T1 have that
extra
> amount of info in them
>
> I thought to do the following
>
> select I_ACCT_NBR, C_ACCT_NBR, BILLING_SYSTEM, WHERE_BILLING from
> T2
> where exists (select studyid from T1 where substr(T1.studyid, 1, 5)
> = substr(T2.C_ACCT_NBR, 1,5))
>
> but i get no rows returned
>
> any help
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 17 1999 - 18:53:47 CDT