IN clause with subquery retunrs 0 results 1

[Sandman83]

Hi All,

I am running a query with a subuery in an IN clause and am getting back zero results. The query is;

SELECT COUNT(*), dial_digit
FROM calldetail
WHERE dial_digit IN (SELECT TO_CHAR (spd_num) FROM speed)
GROUP BY dial_digit
ORDER BY dial_digit

If I run just the subquery I get the expected results (155 rows to be exact) of numbers returned as strings. If I change the query to;

SELECT COUNT(*), dial_digit
FROM calldetail
WHERE dial_digit IN (TO_CHAR (149))
GROUP BY dial_digit
ORDER BY dial_digit

I also get no results, but changing the "TO_CHAR (149)" to " '149' " I do get results.

The dial_digit field is a string and the spd_num field is a number.

Any help would be appreciated.

Thx, Tim

 

[cmmrfrds]

If the string is 3 digits try putting a mask on to_char.

(TO_CHAR (149,'999'))

 

[Sandman83]

Thx for the reply cmmrfrds.

The field can be two or three digits. I tried

(TO_CHAR (spd_num, '99')

to see if that would return results for at least the two digit numbers, but still got zero results.

The fact that (TO_CHAR (149) does return any records while just '149' does makes me wonder if TO_CHAR will work for this at all, or if I need to go another direction.

 

[Turkbear]

Hi,
Maybe try the other conversion:

SELECT COUNT(*), dial_digit
FROM calldetail
WHERE TO_NUMBER(dial_digit)  IN (SELECT spd_num FROM speed)
GROUP BY dial_digit
ORDER BY dial_digit

You may have to change the Group By and Order By clauses as well.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Sandman83]

Hi Turkbear

Unfortunately since the dial_digit field has entries with '#' and/or '*' I get an invalid number error when trying to convert dial_digit to a number.

 

[cmmrfrds]

What is the data type of dial_digit? Is it a varchar field of varying length or will it always be from 2-3 digits of alpha numeric content? Also, is it indexed and do you want to avoid bypassing the index or doesn't it matter. Does dial_digits have leading or trailing spaces?

 

[carp]

What kind of "string" is dial_digit? If it is CHAR, then you will have blanks appended to achieve the correct length.
Try

SELECT COUNT(*), dial_digit
FROM calldetail
WHERE TRIM(dial_digit) IN (SELECT TO_CHAR (spd_num) FROM speed)
GROUP BY dial_digit
ORDER BY dial_digit

to see if this helps.

 

[Sandman83]

Thx Carp,

TRIM worked.