|
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? |
|
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. |
Please explain what you mean by "not working"... examples of actual data values plus the expected results would be very helpful.
And please re-check your SQL statements. Somehow they don't appear to be the exact opposite - cf. the LIKE predicates.
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?
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?
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.
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?