How to select in select where the inner select is a substring of return result

Question

This is beset illustrate by an example that I think should work but doesn't:

select * from TABLE_A where KEY in (
select substr(DYNAMIC_KEY,3) from TABLE_B where DYNAMIC_KEY like '$$%' and RECORD_ID='104251893783388824');

Basically, the inner select statement return a set of result which had a '$$' prefix. This is use as a lookup key in TABLE_A which does not contain the '$$' prefix in the key.

If I manually run the inner statement:

select substr(DYNAMIC_KEY,3) from TABLE_B where DYNAMIC_KEY like '$$%' and RECORD_ID='104251893783388824'

Copy the first result, let say '8462928318772288542' and run the outer select statement

select * from TABLE_A where KEY = '8462928318772288542'

This works.

However, if I ran is as a select in (select ...) statement, I get no result.

Edit:

The database used here is Oracle 10g.

The DYNAMIC_KEY column in TABLE_B is a VARCHAR2

The KEY column in TABLE_A is CHAR 32

Answer 1

SELECT TABLE_A.* 
FROM TABLE_A a
INNER JOIN TABLE_B b ON a.KEY = b.sbustr(DYNAMIC_KEY,3)
WHERE b.DYNAMIC_KEY like '$$%' AND b.RECORD_ID='104251893783388824'

Answer 2

substring requires 3 elements not two. Also I suspect there is a mismatch between the value of key and the value of the substring.

Answer 3

Do you possibly have trailing blanks you need to trim out on either column? Or possibly cast them both to the same data type?

Answer 4

Your problem results from the comparison between datatypes "char(32)" in TABLE_A.KEY and "varchar2" in TABLE_B.DYNAMIC_KEY. The values in TABLE_A.KEY are blank padded by the dbms to the defined size of 32.

When you executed the subquery and copied the result into the outer query, you compared a column of type char(32) with a literal string, which the dbms treated like a char(32). Meaning, it was blank padded and thus the comparison worked.

In your original query you could change "where KEY in (" to "where trim(KEY) in (", then the query should work. The trim() function returns a varchar2 value.