AND OR

technopak

Hi,

I'm curious about the evaluation of expressions containing AND and OR.

For example, if I want write a query to find all staff who work at Science and drink Coffee or work at Science and drink Tea, how should my query go ?

I've tried things like

FIND Staff WHERE (Staff.Work='Science' AND Staff.Drink='Coffee' OR Staff.Work='Science' AND Staff.Drink='Tea')

and

FIND Staff WHERE ((Staff.Work='Science' AND Staff.Drink='Coffee') OR (Staff.Work='Science' AND Staff.Drink='Tea'))

but my query doesn't bring up all the Staff members which it should.

The query builder doesn't groan at my expression, so I'm sure it's a semantics problem - I don't understand it 🙂

However, I can't find any info anywhere in the guides about this type of expression.

Can anyone help ?

Peter

tford

Using Derby DB, I did a quick test bsv & this worked for me:

FIND Staff WHERE ((Staff.Work2='Science' AND Staff.Drink='Coffee') OR (Staff.Work2='Science' AND Staff.Drink='Tea'))

Note that I had to change the attibute "Work" to "Work2" because the config tool said "Work" was reserved for system use.

technopak

Thanks Tom,

That's interesting.

My query wasn't actually to Find Staff who like tea or coffee, but the principle is the same.

My query was trying to get over the problem of Aware's week commencing on Sunday, and trying to produce a report based on a Monday to Sunday week rather than a Sunday to Saturday week.

That query constructed the same as the staff example above was -

FIND Appointment WHERE (WEEK(DATE_PART(Appointment.StartTime))=WEEK(DATE_PART(CURRENT_TIMESTAMP)) AND DAY_NUMBER_IN_WEEK(DATE_PART(Appointment.StartTime))>1)
OR (WEEK(DATE_PART(Appointment.StartTime))=WEEK(DATE_PART(CURRENT_TIMESTAMP))+1 AND DAY_NUMBER_IN_WEEK(DATE_PART(Appointment.StartTime))<2 ) ORDER BY Appointment.StartTime

Sorry for going about my question in a roundabout way, but I had to eliminate the construction first - and I think you have done that.

Now I'm really baffled 🙂

Peter

tford

I wonder if this helps:

tford

I also wonder if it would be simpler to have a few more attributegs in your Appointment BO --

like: Appointment.StartTimeWeek

then have a rule which calulates the new attribute.

The new attribute(s) would then be used in the query & make it much less complicated.

technopak

I still don't know why my query won't work but have done something similar to what you are suggesting - I've created RealWeekStart and a RealWeekEnd Date attributes in SystemSettings which auto-updates from the day of week number when any user logs in to my app.

At least now I can produce reports in the week format which my users know and love 🙂

Where in the world uses Sunday as the first day of the week ?

Still very curious about my query !!

Thanks for the posts Tom,

Peter

tford

Hi Peter,

I think your SystemSettings solution dealt with: =WEEK(DATE_PART(CURRENT_TIMESTAMP))

My suggestin is to deal with the other part of the FIND also (see bold below):

b=WEEK(DATE_PART(CURRENT_TIMESTAMP))

I vaguely remember a post that indicated that a function like this would not work within a query -- can't find it though.

bwienhoven

He TFord,

interesting indeed.
have you tried to put the following query to work.
FIND Staff WHERE (Staff.Work='Science' AND Staff.Drink='Coffee' OR Staff.Drink='Tea')

I believe (speaking from my own experience) that aware handles the part before the AND operator separately from the part that comes after the AND operator. BUT it handles the OR operator at once. so it says exactly what you do when you read it out load.
'find staff that work at sience and drink coffee or tea'

I'm gonna keep track of this post.
nice one 😃

greetings

technopak

Hi,

I have two rules which run when a user logs in as follows -

IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=1 Then
SystemSettings.RealWeekStart=CURRENT_DATE+1
ELSE IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=2 Then
SystemSettings.RealWeekStart=CURRENT_DATE
ELSE IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=3 Then
SystemSettings.RealWeekStart=CURRENT_DATE-1
ELSE IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=4 Then
SystemSettings.RealWeekStart=CURRENT_DATE-2
ELSE IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=5 Then
SystemSettings.RealWeekStart=CURRENT_DATE-3
ELSE IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=6 Then
SystemSettings.RealWeekStart=CURRENT_DATE-4
ELSE IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=7 Then
SystemSettings.RealWeekStart=CURRENT_DATE-5

IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=1 Then
SystemSettings.RealWeekEnd=CURRENT_DATE+7
ELSE IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=2 Then
SystemSettings.RealWeekEnd=CURRENT_DATE+6
ELSE IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=3 Then
SystemSettings.RealWeekEnd=CURRENT_DATE+5
ELSE IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=4 Then
SystemSettings.RealWeekEnd=CURRENT_DATE+4
ELSE IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=5 Then
SystemSettings.RealWeekEnd=CURRENT_DATE+3
ELSE IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=6 Then
SystemSettings.RealWeekEnd=CURRENT_DATE+2
ELSE IF DAY_NUMBER_IN_WEEK(CURRENT_DATE)=7 Then
SystemSettings.RealWeekEnd=CURRENT_DATE+1

These rules set the RealWeekStart and RealWeekEnd dates to the Monday and the following Sunday of the week of the login date.

Only a workaround I know, but it works. When I get time, I'm gonna figure out how to write a custom function to generate my week start and week end dates 🙂

Meantime maybe Support can give us the official line on using AND and OR in complex expressions. What is the order of evaluation ?

Peter

technopak

Hi,

Any comment on this one - support ?

Peter

aware_support

We have noticed some problems with the execution of the ELSE statement. We'll be looking at them shortly.

rocketman

RE - your problem of your users starting the week a day later than everyone else (just kidding) If you're using mysql, have you tried

mysql> set GLOBAL default_week_format=1; from the MySql command line
This should make the standard WEEK function work without additional code.

Sorry - can't help on the AND OR ELSE thing

technopak

Thanks Mo,

That's a super tip.

Maybe it should be included in the Tips and Tricks section.

Thanks from all the 'Week starts on Monday' brigade !!

Peter