2

I am trying to search for a client using name and date of birth. My query is included below.

Select * 
From Client
Inner Join Address
On Client.num = Address.cl_num
Where 
   Client.fname LIKE ‘%john%’ and Client.sname LIKE ‘%doe%’
Or
    Client.fname LIKE ‘%doe%’ and Client.sname LIKE ‘%john%’
And Client.year = 1973
And Client.month = 06
And Client.day = 05

The results however show clients who weren’t born on that day. Is there a problem with my statement?

The query is run on an iSeries server.

7

The OR clause is short circuiting your selection. OR binds lower than AND. Use parenthesis to group that criteria as follows:

Select * 
From Client
Inner Join Address
On Client.num = Address.cl_num
Where 
(
Client.fname LIKE ‘%john%’ and Client.sname LIKE ‘%doe%’
Or
Client.fname LIKE ‘%doe%’ and Client.sname LIKE ‘%john%’
)
And Client.year = 1973
And Client.month = 06
And Client.day = 05

Precedence rules for operators exist in all languages. For example, 3 + 5 * 2 will give you 13 and not 16 in most programming languages. * has higher precedence that +, the same way AND has higher precedence than OR. If you want the addition, 3+5, to happen before the multiplication, you'll use parentheses: (3 + 5) * 2

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.