×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

"OR" in Where clause puzzler

"OR" in Where clause puzzler

"OR" in Where clause puzzler

(OP)
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").

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

RE: "OR" in Where clause puzzler

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%'

RE: "OR" in Where clause puzzler

(OP)
Thanks;
       I will give it a try.

Mike S

RE: "OR" in Where clause puzzler

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close