Query Results

paulman · Post by **paulman** » Fri May 10, 2002 1:50 pm

I'm having a problem getting records back from the following query

select winid from ccontacts where responsibility_code like 38 and revenue in (12,14,17,19)

This does not return and any records or even an error message. I have an index on both 'responsibility_code' and 'revenue' fields. About 50% of the data has a revenue code of '12'. I would imagine that this would make the index unusable but I would have thought it would still do a table scan. It doesn't seem to be a doing a table scan because it does not return a timeout message. If I change the query to ( Just remove the '12' code ):

select winid from ccontacts where responsibility_code like 38 and revenue in (14,17,19)

I get a resultset back. Do you know why this is happening?

Post by **mark** » Fri May 10, 2002 2:46 pm

What are the data types for responsibility_code and revenue? You can't use like on nontext fields. It should be "like '38'".

paulman · Post by **paulman** » Fri May 10, 2002 3:00 pm

The responsibility_code field is a varchar containing multiple values. For example '11 38 42'

The revenue field is a double containing one numeric value.

I changed the query to use '38' and I get the same results. The first query I show returns nothing and the second works fine.

paulman · Post by **paulman** » Mon May 13, 2002 2:28 pm

Do you have any ideas why it's not bringing back any results?

Post by **John** » Mon May 13, 2002 3:52 pm

We are not able to reproduce the behavior here. Can you open a ticket on the Tech Support page, and include the Texis version you have, as well as a short script that reproduces the problem if possible.