"OR" in Where clause puzzler

[Grelber]

I am working with Seagate Info v7.5 developing a Query to extract records having the word "VERBAL" in either of two fields. (Database is not relational and am using vendor defined tables that are linked to the database via MSQL "mappings&quot.

I get the expected number of records with queries that look for VERBAL in only one field and not the other or that have VERBAL in both fields but when I try to use "OR" to get records with the word in either field I get over 22,000 records, most of which do not have the word VERBAL in either field.

Here is the SQL

This query yields the 6 expected records with VERBAL in the "priority Code" field

SELECT
LTN5_GL_View.OrderName,
LTN5_GL_View.AN,
LTN5_GL_View.CollectionDateODBC,
LTN5_GL_View.OrderCode,
LTN5_GL_View.PriorityCodes,
LTN5_GL_View.PtName,
LTN5_GL_View.OrderPhys,
LTN5_GL_View.SpecimenOrderDateODBC,
LTN5_GL_View.OrderPhysName
FROM
SYSTEM.LTN5_GL_View LTN5_GL_View
WHERE
LTN5_GL_View.PriorityCodes LIKE '%VERBAL%'
ORDER BY
LTN5_GL_View.LTN5_GL_View.OrderPhys ASC

And here's the one that gives me the 15 records with "VERBAL" in the CommentText:
SELECT
LTN5_GL_View.OrderName,
LTN5_GL_View.AN,
LTN5_GL_View.CollectionDateODBC,
LTN5_GL_View.OrderCode,
LTN5_GL_View.CommentText,
LTN5_GL_View.PtName,
LTN5_GL_View.OrderPhys,
LTN5_GL_View.SpecimenOrderDateODBC,
LTN5_GL_View.OrderPhysName
FROM
SYSTEM.LTN5_GL_View LTN5_GL_View
WHERE
LTN5_GL_View.CommentText LIKE '%VERBAL%'
ORDER BY
LTN5_GL_View.LTN5_GL_View.OrderPhys ASC

Then there's the one that gives me 22000+ records:
SELECT
LTN5_GL_View.OrderName,
LTN5_GL_View.AN,
LTN5_GL_View.CollectionDateODBC,
LTN5_GL_View.OrderCode,
LTN5_GL_View.CommentText,
LTN5_GL_View.PriorityCodes,
LTN5_GL_View.PtName,
LTN5_GL_View.OrderPhys,
LTN5_GL_View.SpecimenOrderDateODBC,
LTN5_GL_View.OrderPhysName
FROM
SYSTEM.LTN5_GL_View LTN5_GL_View
WHERE
LTN5_GL_View.CommentText LIKE '%VERBAL%' or
LTN5_GL_View.PriorityCodes LIKE '%VERBAL%'
ORDER BY
LTN5_GL_View.LTN5_GL_View.OrderPhys ASC

I should only be getting 21 records with this last query what am I doing wrong?

Thanks
Mike S

 

[rhinok]

Since you're using a View, I'd combine the two separate datasets with a Union Clause in the View itself.

SELECT
...
FROM
...
WHERE
table.CommentText LIKE '%VERBAL%'
UNION
SELECT
...
FROM
...
WHERE
table.PriorityCodes LIKE '%VERBAL%'

 

[Grelber]

Thanks;
I will give it a try.

Mike S

 

[synapsevampire]

Yuo don't need to use the View, you can use the Database-Show SQL Query to manually create a View.

There is a difference in your SQL though, so make sure you allow for the LTN5_GL_View.PriorityCodes field.

-k