Problems with complex query...

Hi,

I am new to this forum and hope there is somebody how has some time to help me here... really struggling....

this is my query:
<<

select * from privuser.actvcode
where
(actv_code_type_id = 205) and (short_name = 1)

(or (actv_code_type_id = 214) and (short_name not like 'JNP'))

(or (actv_code_type_id = 215) and (short_name not like 'SUB'))

(or (actv_code_type_id = 218) and (short_name not like '57%'))

(or (actv_code_type_id = 218) and (short_name not like '58%'))
>>>

I am trying to query the same two columns with different combination of values... hmm..

Many Thanks,
Nina

Try moving the "or"s outside the parentheses like so:

select * from privuser.actvcode
where
((actv_code_type_id = 205) and (short_name = 1))
or ((actv_code_type_id = 214) and (short_name not like 'JNP'))
or ((actv_code_type_id = 215) and (short_name not like 'SUB'))
or ((actv_code_type_id = 218) and (short_name not like '57%'))
or ((actv_code_type_id = 218) and (short_name not like '58%'))

Lucas Alexander
http://www.alexanderdevelopment.net

thank you Lucas!

Still no joy though.. The query is executing and gives me a result set, but it contains values i wish to exclude.

I want to include actv_code_type_id = 214, but not short_name like 'JNP'

any other good suggestions?

Nina

Is short_name a string or a number? You seem to have it defined both ways. Not sure if that is an issue worth checking or not.

Also, start with the simpliest of filters if complex filters are causing problems until you have an expected resulting set. Then add filters as you go along making sure your resulting set is still expected, until you have your finished filter.

I can't see anything first off except the differing variable type. Also, if you are getting more than one record that is not expected what are those records. It is easier to visualize what a computer is thinking when you see the unexpected results.

does that shortname "jnp" show up for actv_code_type_id = 214?...or is it showing up for another actv_code_type_id?

the last two conditions are problematic

or ((actv_code_type_id = 218) and (short_name not like '57%'))
or ((actv_code_type_id = 218) and (short_name not like '58%'))

all rows with actv_code_type_id = 218 will be accepted

the short name will either be not like one value or not like the other

Thank you very much for feedback!

The short_name is varchar 60, and are containing a mix of numbers and letters. Unfortunatly this are a external db and can not be changed either.

I do understand that the two queries short_name 58% nad 57% will not work. What I really want is the range from 57% to 58%, but I don't think this is not possible since it is varchar...


"jnp" is showing up where actv_code_type_id = 214

hmm...

Nina