Fellow Remedy Developers....

Well.. I just did it again...
Enabled an escalation which fired off hundreds of FALSE warning emails to
staff members..

I just sent my appology email.. and disabled the escalation....

HELP...

the escalation in question is supposed to send off an email message
indicating a request is past it's due date.. when the status of the
request is not yet completed... and a previous warning message was not yet
sent.

The qualification for this escalation follows:

( 'Due Date' < $DATE$) AND ( 'Status' < "Completed") AND ( 'Escalation
Email' = $NULL$ )

The Due Date fields is datatype Date..

For some reason, entries with Due Date values in the future are passing
the qualification.. and messages are flying..

ANY IDEAS... isn't the $DATE$ reference in a qualification of an
escalation correct? or should I be using the $\DATE$ keyword?

Eggman..... Phil Mougis

BAD QUAL -- BAD -- Evil !!!

( 'Due Date' <= $DATE$) AND ( 'Status' <= "ONE BEFORE Completed") AND (
'Escalation Email' = $NULL$ )
Note:: < If AT ALL POSSIBLE:: use an = with EVERY > or < otherwise you
will NEVER use an index.
Speed is a Killer of the Common Server ! Or Lack thereof !

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as
a helpful Hint !
If this were an Real Emergency your server would not be running, but
Begging for relief.

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Phil Mougis
Sent: Thursday, March 18, 2004 10:25 AM
To: ARSLIST@ARSLIST.ORG
Subject: EGG on my FACE


Fellow Remedy Developers....

Well.. I just did it again...
Enabled an escalation which fired off hundreds of FALSE warning emails to
staff members..

I just sent my appology email.. and disabled the escalation....

HELP...

the escalation in question is supposed to send off an email message
indicating a request is past it's due date.. when the status of the request
is not yet completed... and a previous warning message was not yet sent.

The qualification for this escalation follows:

( 'Due Date' < $DATE$) AND ( 'Status' < "Completed") AND ( 'Escalation
Email' = $NULL$ )

The Due Date fields is datatype Date..

For some reason, entries with Due Date values in the future are passing the
qualification.. and messages are flying..

ANY IDEAS... isn't the $DATE$ reference in a qualification of an
escalation correct? or should I be using the $\DATE$ keyword?

Eggman..... Phil Mougis



UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com

Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the
performance... not the result set?

Is there a BUG in Remedy at play here?

Now.. if I use your suggestion... I should probably use a representation for
Yesterday.
$DATE$ - 86400...

( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND (
'Escalation Email' = $NULL$ )

There is a known bug with $DATE$ and Date fields in qualifications. As a
work around you must add a Date/Time field which is set to the value of 'Due
Date' on Submit and Modify, and use that in your qualification instead.

At least you can take solace from the thought it wasn't your silly mistake
but Remedy's.

David Adams
Corporate Information Services
Information Systems Services
University of Southampton

----- Original Message -----
From: "Phil Mougis"
To:
Sent: Thursday, March 18, 2004 3:24 PM
Subject: EGG on my FACE


> Fellow Remedy Developers....
>
> Well.. I just did it again...
> Enabled an escalation which fired off hundreds of FALSE warning emails to
> staff members..
>
> I just sent my appology email.. and disabled the escalation....
>
> HELP...
>
> the escalation in question is supposed to send off an email message
> indicating a request is past it's due date.. when the status of the
> request is not yet completed... and a previous warning message was not yet
> sent.
>
> The qualification for this escalation follows:
>
> ( 'Due Date' < $DATE$) AND ( 'Status' < "Completed") AND ( 'Escalation
> Email' = $NULL$ )
>
> The Due Date fields is datatype Date..
>
> For some reason, entries with Due Date values in the future are passing
> the qualification.. and messages are flying..
>
> ANY IDEAS... isn't the $DATE$ reference in a qualification of an
> escalation correct? or should I be using the $\DATE$ keyword?
>
> Eggman..... Phil Mougis
>
>


> UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com
>

Actually, I just had that problem on something I am designing from scratch,
so it isn't as bad when I spammed myself. What I found is for a "DATE"
field as opposed to a date/time field, you can't use the keyword $DATE$.
$DATE$ returns the number of second from Jan 1, 1970 to the start of the
current day. Where as a "DATE" field uses the number of days from some date
of which I am not sure. So you almost have to do a $DATE$/86400 into an
integer to drop the remainder. What I found to be easier, although not
necessarily so in an escalation, was to put $DATE$ into a "DATE" field and
then compare the two fields.

I hope this gives you some direction.

Brian Goralczyk
Senior Remedy Developer
Verizon Wireless
5165 Emerald Parkway
Dublin, OH 43017

Phone : 614.560.8867
Mobile : 614.203.8866


-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: Thursday, March 18, 2004 10:50 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: [ARSLIST] EGG on my FACE


BAD QUAL -- BAD -- Evil !!!

( 'Due Date' <= $DATE$) AND ( 'Status' <= "ONE BEFORE Completed") AND (
'Escalation Email' = $NULL$ )
Note:: < If AT ALL POSSIBLE:: use an = with EVERY > or < otherwise you
will NEVER use an index.
Speed is a Killer of the Common Server ! Or Lack thereof !

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as
a helpful Hint !
If this were an Real Emergency your server would not be running, but
Begging for relief.

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Phil Mougis
Sent: Thursday, March 18, 2004 10:25 AM
To: ARSLIST@ARSLIST.ORG
Subject: EGG on my FACE


Fellow Remedy Developers....

Well.. I just did it again...
Enabled an escalation which fired off hundreds of FALSE warning emails to
staff members..

I just sent my appology email.. and disabled the escalation....

HELP...

the escalation in question is supposed to send off an email message
indicating a request is past it's due date.. when the status of the request
is not yet completed... and a previous warning message was not yet sent.

The qualification for this escalation follows:

( 'Due Date' < $DATE$) AND ( 'Status' < "Completed") AND ( 'Escalation
Email' = $NULL$ )

The Due Date fields is datatype Date..

For some reason, entries with Due Date values in the future are passing the
qualification.. and messages are flying..

ANY IDEAS... isn't the $DATE$ reference in a qualification of an
escalation correct? or should I be using the $\DATE$ keyword?

Eggman..... Phil Mougis



UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com



UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com

Phil,
If the notification should only be sent when a request is past its due date,
you should logically use 'Due Date' >= $DATE$ (instead of 'Due Date' <
$DATE$).

However, since you said the 'Due Date' field is a Date field (not a
Date/Time field), this still won't work due to an ARS bug that involves the
use of the $DATE$ keyword in workflow qualifications against a Date field.
Essentially, the $DATE$ keyword incorrectly returns a Date/Time value,
regardless of whether it is being evaluated against a Date field or a
Date/Time field.

One way to resolve this would be to replace your Date field with a Date/Time
field. You can set the display properties of the Date/Time field to display
as "Date Only" if desired.

Another method would be to add another Date field to your form (e.g.,
'tmpCurrentDate'). This would need to be a permanent field (not
display-only). Then run a daily escalation that fires before your
notification escalation for all records where ('Status' < "Completed").
This escalation would need a single set fields action to set the
'tmpCurrentDate' field to $DATE$ (the $DATE$ keyword does return correctly
when used as a set fields value for a Date field, it just doesn't work
consistently in qualifications). In your notification escalation, you could
then replace the $DATE$ keyword with the 'tmpCurrentDate' field. For
example:

( 'Due Date' >= 'tmpCurrentDate') AND ( 'Status' < "Completed") AND (
'EscalationEmail' = $NULL$ )

Hope this helps.

Regards,

Thomas

----- Original Message -----
From: "Phil Mougis"
Newsgroups: cogniza.remedy.arsystem.general
To:
Sent: Thursday, March 18, 2004 9:24 AM
Subject: EGG on my FACE


> Fellow Remedy Developers....
>
> Well.. I just did it again...
> Enabled an escalation which fired off hundreds of FALSE warning emails to
> staff members..
>
> I just sent my appology email.. and disabled the escalation....
>
> HELP...
>
> the escalation in question is supposed to send off an email message
> indicating a request is past it's due date.. when the status of the
> request is not yet completed... and a previous warning message was not yet
> sent.
>
> The qualification for this escalation follows:
>
> ( 'Due Date' < $DATE$) AND ( 'Status' < "Completed") AND ( 'Escalation
> Email' = $NULL$ )
>
> The Due Date fields is datatype Date..
>
> For some reason, entries with Due Date values in the future are passing
> the qualification.. and messages are flying..
>
> ANY IDEAS... isn't the $DATE$ reference in a qualification of an
> escalation correct? or should I be using the $\DATE$ keyword?
>
> Eggman..... Phil Mougis
>
>


> UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com

**

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.

I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...

-----Original Message-----
From: Action Request System discussion list(ARSList) [ mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?

Is there a BUG in Remedy at play here?

Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...

( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )




This posting was submitted via the Web interface

**
That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?

Gareth

-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.

I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...

-----Original Message-----
From: Action Request System discussion list(ARSList) [ mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?

Is there a BUG in Remedy at play here?

Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...

( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )




This posting was submitted via the Web interface


This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.

This posting was submitted via the Web interface

This is not always true, it all depend on the optimizer and the query alanyzer, and how they decide to do the query:

USES INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'

Regards,
Jarl


-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG]On Behalf Of Thomas Bean
Sent: 18. mars 2004 20:06
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
Pat,
LIKE statements should still use indexes as long as you don't use wildcards at the beginning of the comparison string. For example (assume 'mycolumn' is indexed):
USES INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'
DOES NOT USE INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE '%some text%'
--Thomas
----- Original Message -----
From: Zandi Patrick S TSgt AFRL/IFOSS
Newsgroups: cogniza.remedy.arsystem.general
To: ARSLIST@ARSLIST.ORG
Sent: Thursday, March 18, 2004 12:21 PM
Subject: Re: EGG on my FACE


**
ALL --- I have been to 2 (yes this is true) Remedy Performance Classes.. and this is a Fundamental Teaching.

This is also and has also be taught at all 5 years of (yes 5) Remedy User Group Conferences in both Tutorials, and Sessions.

This is also True when looking at some books out there... I have..
Oracle Performance books, ARS Guides on Query's, I guess I will stop there for now.

"=" equals Index if Available.
Like equals Table Scan forever.
> equals Table scan of all rows

Test it yourself.. ensure the table is not in memory first, now with some DB's you can do that.. but a large server will not.. let say 30 million rows...
you can cache some but not all...
Take the 30K row and do a query on a field let say a Char field, and put the query in as a >
Do the same thing with a >=
I have tested and tested this.. with Oracle and mssql and it is consistent...
30 seconds for first one
1 second for the second.
NOTE:: If there is an index for that field. If no index -- it just doesn't matter.. it is still a Table Scan...
OR a LIKE statement will Kill you too...

http://www.atg.com/repositories/ContentCatalogRepositoryen/manuals/ATG6.2.0/installdas/installdas2207.html

http://www.teradataforum.com/teradata/20020610191632.htm

http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf/@GenericBookTextView/2137;pt=2087

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des051.htm

Those Above Might help you out a little...

First Ensure your Queries are properly formatted,
Then if you still have issues, look to make the table into RAM for Performance enhancements.

Hope that helps some...

Don't get me wrong.. Sometime.. Not often.. you cannot do a >= .... So don't panic too much...
Just a little Performance Reminder from your Performance Conscious Fellow ARSlist'er....








-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thackeray, Gareth
Sent: Thursday, March 18, 2004 12:09 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?

Gareth
-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.
I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...
-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?
Is there a BUG in Remedy at play here?
Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...
( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )


This posting was submitted via the Web interface

This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
This posting was submitted via the Web interface
This posting was submitted via the Web interface
This posting was submitted via the Web interface

Jarl,

How could a search where the ONLY criteria was a LIKE substring search NOT do a table scan?

Rick

-----Original Message-----
From: Action Request System discussion list(ARSList) on behalf of Jarl Groneng
Sent: Thu 3/18/2004 11:46 AM
To: ARSLIST@ARSLIST.ORG
Cc:
Subject: Re: EGG on my FACE [bcc][faked-from]



This is not always true, it all depend on the optimizer and the query alanyzer, and how they decide to do the query:

USES INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'

Regards,
Jarl


-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG]On Behalf Of Thomas Bean
Sent: 18. mars 2004 20:06
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
Pat,
LIKE statements should still use indexes as long as you don't use wildcards at the beginning of the comparison string. For example (assume 'mycolumn' is indexed):
USES INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'
DOES NOT USE INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE '%some text%'
--Thomas
----- Original Message -----
From: Zandi Patrick S TSgt AFRL/IFOSS
Newsgroups: cogniza.remedy.arsystem.general
To: ARSLIST@ARSLIST.ORG
Sent: Thursday, March 18, 2004 12:21 PM
Subject: Re: EGG on my FACE


**
ALL --- I have been to 2 (yes this is true) Remedy Performance Classes.. and this is a Fundamental Teaching.

This is also and has also be taught at all 5 years of (yes 5) Remedy User Group Conferences in both Tutorials, and Sessions.

This is also True when looking at some books out there... I have..
Oracle Performance books, ARS Guides on Query's, I guess I will stop there for now.

"=" equals Index if Available.
Like equals Table Scan forever.
> equals Table scan of all rows

Test it yourself.. ensure the table is not in memory first, now with some DB's you can do that.. but a large server will not.. let say 30 million rows...
you can cache some but not all...
Take the 30K row and do a query on a field let say a Char field, and put the query in as a >
Do the same thing with a >=
I have tested and tested this.. with Oracle and mssql and it is consistent...
30 seconds for first one
1 second for the second.
NOTE:: If there is an index for that field. If no index -- it just doesn't matter.. it is still a Table Scan...
OR a LIKE statement will Kill you too...

http://www.atg.com/repositories/ContentCatalogRepositoryen/manuals/ATG6.2.0/installdas/installdas2207.html

http://www.teradataforum.com/teradata/20020610191632.htm

http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf/@GenericBookTextView/2137;pt=2087

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des051.htm

Those Above Might help you out a little...

First Ensure your Queries are properly formatted,
Then if you still have issues, look to make the table into RAM for Performance enhancements.

Hope that helps some...

Don't get me wrong.. Sometime.. Not often.. you cannot do a >= .... So don't panic too much...
Just a little Performance Reminder from your Performance Conscious Fellow ARSlist'er....








-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thackeray, Gareth
Sent: Thursday, March 18, 2004 12:09 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?

Gareth
-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.
I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...
-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?
Is there a BUG in Remedy at play here?
Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...
( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )


This posting was submitted via the Web interface

This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
This posting was submitted via the Web interface
This posting was submitted via the Web interface
This posting was submitted via the Web interface