When NOT is necessary...

[Former Member]

It appears that iAnywhere 9.0.2 has some issues using NOT.

Here is the statement that’s working for me now:

SELECT DISTINCT contact.contact_id FROM contact WHERE ( (' ' + categories + ' ') LIKE '% 250% ') ORDER BY contact.contact_id; sQuery: SELECT DISTINCT contact.contact_id FROM contact WHERE ( (' ' + categories + ' ') LIKE '% 250% ') ORDER BY contact.contact_id; It returns 25 records

Here is the reverse of that one that I am trying to execute which finds almost all the records in the database but not the total amount -25 that are set to 250:

SELECT DISTINCT contact.contact_id FROM contact WHERE ( (categories NOT LIKE '% 250') AND (categories NOT LIKE ' %250% ') ) ORDER BY contact.contact_id;

I’ve found in SQL Interactive, even with very simple statements l can’t get the NOT to work no matter how I arrange the syntax.

I went to a SQL expert and he couldn’t figure it out and thought it was very strange.

I talked to one of the Sybase guys and he though it should work.

That's when he suggested this forum.

Anyone with any ideas?

[Breck_Carter]

Please explain what you mean by "not working"... examples of actual data values plus the expected results would be very helpful.

[VolkerBarth]

And please re-check your SQL statements. Somehow they don't appear to be the exact opposite - cf. the LIKE predicates.

[Former Member]

Say I have a database with a hundred records, if the number I am looking for in the categories column is entered 25 times, the first SQL statement finds those 25 records, the second NOT statement does not find 75 but some other amount that seems random.

I've tried an even simpler version of NOT statements in SQL Interactive and it just doesn't give me the set of records I'm looking for without the hard coded parameter I'm trying to avoid.

Make sense?

[Former Member]

Okay, this might be a better way of asking, what would be the syntax if I wanted to find all the records for the column categories that were not equal to 250, and 250 might have a return character in front or behind it?

[VolkerBarth]

Without more details on your tables, it's hard to tell. Nulls may be a problem: Generally the result of "SELECT ... WHERE x = 100 UNION SELECT ... WHERE x <> 100" will not necessary return ALL results because there may be rows where x IS NULL, and such rows won't be neither be part of the first result nor of the second. That's just three-valued logic.

[Breck_Carter]

What do you mean "hard coded parameter I'm trying to avoid"? Do you want to replace the '250' by a variable reference? A host variable? What development environment are you using? VB?

[MCMartin]

Maybe it's you distinct which misleads you, e.g if you have data like this:

contact id, categories
1,250
1,30
2,250

The first statement (like 250) should result in 1 and 2 but your NOT statement will nevertheless return 1, because contact id 1 is in the set of category 250 but also in other sets, so also not in category 250... Dependent on the distinct the like and the not like result sets will not be just the opposite of each other.

[Breck_Carter]

Now I really want to know what the schema and data looks like... it never occurred to me that contact_id might not be unique 🙂

[Breck_Carter]

Like Volker Barth said in a comment, "without more details" it's hard to tell... but, let's carry on anyway...

CREATE TABLE contact (
   contact_id    INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   categories    VARCHAR ( 100 ) NOT NULL );

INSERT contact ( categories ) VALUES ( ' 250' );
INSERT contact ( categories ) VALUES ( '       250      ' );
INSERT contact ( categories ) VALUES ( 'xxxxxx 250xxxxxx' );

INSERT contact ( categories ) VALUES ( ' 999' );
INSERT contact ( categories ) VALUES ( '       999      ' );
INSERT contact ( categories ) VALUES ( 'xxxxxx 999xxxxxx' );
COMMIT;

BEGIN
SELECT DISTINCT contact.contact_id 
  FROM contact 
 WHERE ( (' ' + categories + ' ') LIKE '% 250% ') 
 ORDER BY contact.contact_id;

SELECT DISTINCT contact.contact_id 
  FROM contact 
 WHERE ( (' ' + categories + ' ') NOT LIKE '% 250% ') 
 ORDER BY contact.contact_id; 
END;

Those two queries give these results...

contact_id
1
2
3

contact_id
4
5
6

You haven't showed us WHAT YOUR DATA ACTUALLY LOOKS LIKE, so we can't offer any opinions on whether the predicate ( (' ' + categories + ' ') NOT LIKE '% 250% ' is correct or not.

HOWEVER, the spaces look funky... the ' 's in (' ' + categories + ' ') might be redundant. JUST GUESSING... because I have NO IDEA WHAT YOUR DATA ACTUALLY LOOKS LIKE.

Hint... show us what your data actually looks like <g>... we really want to help.

Really.