Re: Fwd: Question: SQL
You could use 1 temp field with 32 filters/active links (16 to set the
temp field and 16 to check the temp field right after).
Another option is to create SQL function in your database and pass in
all the filenames (separate by a semi-colon or similar character) along
with the text to search. The function would check all filenames and
return a list of matching filenames to ARS. The next filter or active
link would then have a Run If to see if the temp field (containing the
list) was empty or not. If not empty, then send an email.
Stephen
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Tyrone Dee
Sent: Thursday, December 07, 2006 9:29 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
That is a great idea, but I have 16 attachments in my attachment pool
and I really don't want to create 16 temporary fields because users can
and do add more then one attachment at a time. The user wants me to
check for key letters in a file name and send him an email only when new
files or changed files get uploaded.
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen
Sent: Thursday, December 07, 2006 9:22 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
Have you tried:
Action 1 (if run from a filter): Set Fields zTmpField1 = UPPER($File1$)
Action 1 (if run from an active link): Set Fields zTmpField1 =
UPPER($File1$) WHERE RequestID = $RequestID$
Action 2: Set Fields SQL SELECT CHARINDEX('SOW', $zTmpField1$) and
store to zTmpInt1
After it runs the zTmpInt1 field will contain the position within the
filename if 'SOW' is found. If not found, then zTmpInt1 will be 0.
Stephen
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Tyrone Dee
Sent: Thursday, December 07, 2006 9:11 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
Hey Jim - thanks for your help - much appreciated !
I have an attachment pool - with 10 attachments and right now I am only
trying it on one of the attachments to see if my SQL statement is
correct.
I only have on filter that fires on modify - executing at 500.
Set Fields - Read Value from SQL and I set the SQL Result Column 1 to a
integer field.
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 07, 2006 9:07 AM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
What event is the filter attached to? What happens if you add a 2nd
attachment to a particular request? Does it return 1 if you do?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 3:32 PM
To: Ashton, Jim (JUS); arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Filter is firing at 500 - should I change it to 999 ?
Will that make a difference?
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 3:27 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Sounds like your filter is running before your attachment is saved.
Does it return 1 if you add a second attachment (which would actually be
recognition of the existence of the first attachment)?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:29 PM
To: Ashton, Jim (JUS); arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Yes
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:20 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Does the statement work properly if you run it with a known sample value
for $Request ID$ in Query Analyzer?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 1:37 PM
To: arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Jim - thanks for your email.
I decided to go back a bit and try just looking for SOW in the filename
- here is the SQL Statement:
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE
(Request_ID = '$Request ID$') AND
((PATINDEX('%SOW%',UPPER(Scope_of_Work)) > 0) )
It is returning 0 when I add the attachment and 1 when I delete the
attachment.
Suggestions?
Ty
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Jim Ashton
Sent: Wednesday, December 06, 2006 10:02 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
I think you may need one more set of brackets. It appears that the query
always returns 1 because Request_ID = '$Request ID$' is always true and
at least one of the OR conditions is true somewhere in the
IPVPN_IPACT_Overview table.
Put another set of brackets around everything following the AND (as
shown
below) which will resu
Re: Fwd: Question: SQL
That is a great idea, but I have 16 attachments in my attachment pool and I
really don't want to create 16 temporary fields because users can and do add
more then one attachment at a time. The user wants me to check for key
letters in a file name and send him an email only when new files or changed
files get uploaded.
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen
Sent: Thursday, December 07, 2006 9:22 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
Have you tried:
Action 1 (if run from a filter): Set Fields zTmpField1 = UPPER($File1$)
Action 1 (if run from an active link): Set Fields zTmpField1 =
UPPER($File1$) WHERE RequestID = $RequestID$
Action 2: Set Fields SQL SELECT CHARINDEX('SOW', $zTmpField1$) and
store to zTmpInt1
After it runs the zTmpInt1 field will contain the position within the
filename if 'SOW' is found. If not found, then zTmpInt1 will be 0.
Stephen
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Tyrone Dee
Sent: Thursday, December 07, 2006 9:11 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
Hey Jim - thanks for your help - much appreciated !
I have an attachment pool - with 10 attachments and right now I am only
trying it on one of the attachments to see if my SQL statement is
correct.
I only have on filter that fires on modify - executing at 500.
Set Fields - Read Value from SQL and I set the SQL Result Column 1 to a
integer field.
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 07, 2006 9:07 AM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
What event is the filter attached to? What happens if you add a 2nd
attachment to a particular request? Does it return 1 if you do?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 3:32 PM
To: Ashton, Jim (JUS); arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Filter is firing at 500 - should I change it to 999 ?
Will that make a difference?
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 3:27 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Sounds like your filter is running before your attachment is saved.
Does it return 1 if you add a second attachment (which would actually be
recognition of the existence of the first attachment)?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:29 PM
To: Ashton, Jim (JUS); arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Yes
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:20 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Does the statement work properly if you run it with a known sample value
for $Request ID$ in Query Analyzer?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 1:37 PM
To: arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Jim - thanks for your email.
I decided to go back a bit and try just looking for SOW in the filename
- here is the SQL Statement:
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE
(Request_ID = '$Request ID$') AND
((PATINDEX('%SOW%',UPPER(Scope_of_Work)) > 0) )
It is returning 0 when I add the attachment and 1 when I delete the
attachment.
Suggestions?
Ty
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Jim Ashton
Sent: Wednesday, December 06, 2006 10:02 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
I think you may need one more set of brackets. It appears that the query
always returns 1 because Request_ID = '$Request ID$' is always true and
at least one of the OR conditions is true somewhere in the
IPVPN_IPACT_Overview table.
Put another set of brackets around everything following the AND (as
shown
below) which will result in both that particular request and at least
one of the OR conditions needing to be true to return a value.
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE
(Request_ID = '$Request ID$') AND ( ((PATINDEX('%SOW%',
UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%IPDP%', UPPER(Scope_of_Work))
> 0) OR (PATINDEX('%MACD%',
UPPER(Scope_of_Work)) > 0)) )
Cheers,
Jim.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are
Re: Fwd: Question: SQL
Have you tried:
Action 1 (if run from a filter): Set Fields zTmpField1 = UPPER($File1$)
Action 1 (if run from an active link): Set Fields zTmpField1 =
UPPER($File1$) WHERE RequestID = $RequestID$
Action 2: Set Fields SQL SELECT CHARINDEX('SOW', $zTmpField1$) and
store to zTmpInt1
After it runs the zTmpInt1 field will contain the position within the
filename if 'SOW' is found. If not found, then zTmpInt1 will be 0.
Stephen
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Tyrone Dee
Sent: Thursday, December 07, 2006 9:11 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
Hey Jim - thanks for your help - much appreciated !
I have an attachment pool - with 10 attachments and right now I am only
trying it on one of the attachments to see if my SQL statement is
correct.
I only have on filter that fires on modify - executing at 500.
Set Fields - Read Value from SQL and I set the SQL Result Column 1 to a
integer field.
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 07, 2006 9:07 AM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
What event is the filter attached to? What happens if you add a 2nd
attachment to a particular request? Does it return 1 if you do?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 3:32 PM
To: Ashton, Jim (JUS); arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Filter is firing at 500 - should I change it to 999 ?
Will that make a difference?
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 3:27 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Sounds like your filter is running before your attachment is saved.
Does it return 1 if you add a second attachment (which would actually be
recognition of the existence of the first attachment)?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:29 PM
To: Ashton, Jim (JUS); arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Yes
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:20 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Does the statement work properly if you run it with a known sample value
for $Request ID$ in Query Analyzer?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 1:37 PM
To: arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Jim - thanks for your email.
I decided to go back a bit and try just looking for SOW in the filename
- here is the SQL Statement:
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE
(Request_ID = '$Request ID$') AND
((PATINDEX('%SOW%',UPPER(Scope_of_Work)) > 0) )
It is returning 0 when I add the attachment and 1 when I delete the
attachment.
Suggestions?
Ty
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Jim Ashton
Sent: Wednesday, December 06, 2006 10:02 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
I think you may need one more set of brackets. It appears that the query
always returns 1 because Request_ID = '$Request ID$' is always true and
at least one of the OR conditions is true somewhere in the
IPVPN_IPACT_Overview table.
Put another set of brackets around everything following the AND (as
shown
below) which will result in both that particular request and at least
one of the OR conditions needing to be true to return a value.
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE
(Request_ID = '$Request ID$') AND ( ((PATINDEX('%SOW%',
UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%IPDP%', UPPER(Scope_of_Work))
> 0) OR (PATINDEX('%MACD%',
UPPER(Scope_of_Work)) > 0)) )
Cheers,
Jim.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: Fwd: Question: SQL
What if you try an active link that executes on After Modify instead? ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
Re: Fwd: Question: SQL
Hey Jim - thanks for your help - much appreciated !
I have an attachment pool - with 10 attachments and right now I am only
trying it on one of the attachments to see if my SQL statement is correct.
I only have on filter that fires on modify - executing at 500.
Set Fields - Read Value from SQL and I set the SQL Result Column 1 to a
integer field.
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 07, 2006 9:07 AM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
What event is the filter attached to? What happens if you add a 2nd
attachment to a particular request? Does it return 1 if you do?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 3:32 PM
To: Ashton, Jim (JUS); arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Filter is firing at 500 - should I change it to 999 ?
Will that make a difference?
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 3:27 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Sounds like your filter is running before your attachment is saved. Does it
return 1 if you add a second attachment (which would actually be recognition
of the existence of the first attachment)?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:29 PM
To: Ashton, Jim (JUS); arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Yes
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:20 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Does the statement work properly if you run it with a known sample value for
$Request ID$ in Query Analyzer?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 1:37 PM
To: arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Jim - thanks for your email.
I decided to go back a bit and try just looking for SOW in the filename -
here is the SQL Statement:
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND
((PATINDEX('%SOW%',UPPER(Scope_of_Work)) > 0) )
It is returning 0 when I add the attachment and 1 when I delete the
attachment.
Suggestions?
Ty
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Jim Ashton
Sent: Wednesday, December 06, 2006 10:02 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
I think you may need one more set of brackets. It appears that the query
always returns 1 because Request_ID = '$Request ID$' is always true and at
least one of the OR conditions is true somewhere in the
IPVPN_IPACT_Overview table.
Put another set of brackets around everything following the AND (as shown
below) which will result in both that particular request and at least one
of the OR conditions needing to be true to return a value.
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND ( ((PATINDEX('%SOW%', UPPER(Scope_of_Work)) > 0) OR
(PATINDEX('%IPDP%', UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%MACD%',
UPPER(Scope_of_Work)) > 0)) )
Cheers,
Jim.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: Fwd: Question: SQL
What event is the filter attached to? What happens if you add a 2nd attachment
to a particular request? Does it return 1 if you do?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 3:32 PM
To: Ashton, Jim (JUS); arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Filter is firing at 500 - should I change it to 999 ?
Will that make a difference?
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 3:27 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Sounds like your filter is running before your attachment is saved. Does it
return 1 if you add a second attachment (which would actually be recognition
of the existence of the first attachment)?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:29 PM
To: Ashton, Jim (JUS); arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Yes
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:20 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Does the statement work properly if you run it with a known sample value for
$Request ID$ in Query Analyzer?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 1:37 PM
To: arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Jim - thanks for your email.
I decided to go back a bit and try just looking for SOW in the filename -
here is the SQL Statement:
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND
((PATINDEX('%SOW%',UPPER(Scope_of_Work)) > 0) )
It is returning 0 when I add the attachment and 1 when I delete the
attachment.
Suggestions?
Ty
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Jim Ashton
Sent: Wednesday, December 06, 2006 10:02 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
I think you may need one more set of brackets. It appears that the query
always returns 1 because Request_ID = '$Request ID$' is always true and at
least one of the OR conditions is true somewhere in the
IPVPN_IPACT_Overview table.
Put another set of brackets around everything following the AND (as shown
below) which will result in both that particular request and at least one
of the OR conditions needing to be true to return a value.
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND ( ((PATINDEX('%SOW%', UPPER(Scope_of_Work)) > 0) OR
(PATINDEX('%IPDP%', UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%MACD%',
UPPER(Scope_of_Work)) > 0)) )
Cheers,
Jim.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: Fwd: Question: SQL
Does the statement work properly if you run it with a known sample value for
$Request ID$ in Query Analyzer?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 1:37 PM
To: arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Jim - thanks for your email.
I decided to go back a bit and try just looking for SOW in the filename -
here is the SQL Statement:
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND
((PATINDEX('%SOW%',UPPER(Scope_of_Work)) > 0) )
It is returning 0 when I add the attachment and 1 when I delete the
attachment.
Suggestions?
Ty
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Jim Ashton
Sent: Wednesday, December 06, 2006 10:02 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
I think you may need one more set of brackets. It appears that the query
always returns 1 because Request_ID = '$Request ID$' is always true and at
least one of the OR conditions is true somewhere in the
IPVPN_IPACT_Overview table.
Put another set of brackets around everything following the AND (as shown
below) which will result in both that particular request and at least one
of the OR conditions needing to be true to return a value.
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND ( ((PATINDEX('%SOW%', UPPER(Scope_of_Work)) > 0) OR
(PATINDEX('%IPDP%', UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%MACD%',
UPPER(Scope_of_Work)) > 0)) )
Cheers,
Jim.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: Fwd: Question: SQL
Sounds like your filter is running before your attachment is saved. Does it
return 1 if you add a second attachment (which would actually be recognition of
the existence of the first attachment)?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:29 PM
To: Ashton, Jim (JUS); arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Yes
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:20 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Does the statement work properly if you run it with a known sample value for
$Request ID$ in Query Analyzer?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 1:37 PM
To: arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Jim - thanks for your email.
I decided to go back a bit and try just looking for SOW in the filename -
here is the SQL Statement:
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND
((PATINDEX('%SOW%',UPPER(Scope_of_Work)) > 0) )
It is returning 0 when I add the attachment and 1 when I delete the
attachment.
Suggestions?
Ty
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Jim Ashton
Sent: Wednesday, December 06, 2006 10:02 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
I think you may need one more set of brackets. It appears that the query
always returns 1 because Request_ID = '$Request ID$' is always true and at
least one of the OR conditions is true somewhere in the
IPVPN_IPACT_Overview table.
Put another set of brackets around everything following the AND (as shown
below) which will result in both that particular request and at least one
of the OR conditions needing to be true to return a value.
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND ( ((PATINDEX('%SOW%', UPPER(Scope_of_Work)) > 0) OR
(PATINDEX('%IPDP%', UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%MACD%',
UPPER(Scope_of_Work)) > 0)) )
Cheers,
Jim.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: Fwd: Question: SQL
Filter is firing at 500 - should I change it to 999 ?
Will that make a difference?
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 3:27 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Sounds like your filter is running before your attachment is saved. Does it
return 1 if you add a second attachment (which would actually be recognition
of the existence of the first attachment)?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:29 PM
To: Ashton, Jim (JUS); arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Yes
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:20 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Does the statement work properly if you run it with a known sample value for
$Request ID$ in Query Analyzer?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 1:37 PM
To: arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Jim - thanks for your email.
I decided to go back a bit and try just looking for SOW in the filename -
here is the SQL Statement:
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND
((PATINDEX('%SOW%',UPPER(Scope_of_Work)) > 0) )
It is returning 0 when I add the attachment and 1 when I delete the
attachment.
Suggestions?
Ty
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Jim Ashton
Sent: Wednesday, December 06, 2006 10:02 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
I think you may need one more set of brackets. It appears that the query
always returns 1 because Request_ID = '$Request ID$' is always true and at
least one of the OR conditions is true somewhere in the
IPVPN_IPACT_Overview table.
Put another set of brackets around everything following the AND (as shown
below) which will result in both that particular request and at least one
of the OR conditions needing to be true to return a value.
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND ( ((PATINDEX('%SOW%', UPPER(Scope_of_Work)) > 0) OR
(PATINDEX('%IPDP%', UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%MACD%',
UPPER(Scope_of_Work)) > 0)) )
Cheers,
Jim.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: Fwd: Question: SQL
Yes
-Original Message-
From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 2:20 PM
To: Tyrone Dee; arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Does the statement work properly if you run it with a known sample value for
$Request ID$ in Query Analyzer?
-Original Message-
From: Tyrone Dee [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 06, 2006 1:37 PM
To: arslist@ARSLIST.ORG
Subject: RE: Fwd: Question: SQL
Jim - thanks for your email.
I decided to go back a bit and try just looking for SOW in the filename -
here is the SQL Statement:
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND
((PATINDEX('%SOW%',UPPER(Scope_of_Work)) > 0) )
It is returning 0 when I add the attachment and 1 when I delete the
attachment.
Suggestions?
Ty
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Jim Ashton
Sent: Wednesday, December 06, 2006 10:02 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
I think you may need one more set of brackets. It appears that the query
always returns 1 because Request_ID = '$Request ID$' is always true and at
least one of the OR conditions is true somewhere in the
IPVPN_IPACT_Overview table.
Put another set of brackets around everything following the AND (as shown
below) which will result in both that particular request and at least one
of the OR conditions needing to be true to return a value.
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND ( ((PATINDEX('%SOW%', UPPER(Scope_of_Work)) > 0) OR
(PATINDEX('%IPDP%', UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%MACD%',
UPPER(Scope_of_Work)) > 0)) )
Cheers,
Jim.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: Fwd: Question: SQL
Jim - thanks for your email.
I decided to go back a bit and try just looking for SOW in the filename -
here is the SQL Statement:
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND
((PATINDEX('%SOW%',UPPER(Scope_of_Work)) > 0) )
It is returning 0 when I add the attachment and 1 when I delete the
attachment.
Suggestions?
Ty
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Jim Ashton
Sent: Wednesday, December 06, 2006 10:02 AM
To: arslist@ARSLIST.ORG
Subject: Re: Fwd: Question: SQL
I think you may need one more set of brackets. It appears that the query
always returns 1 because Request_ID = '$Request ID$' is always true and at
least one of the OR conditions is true somewhere in the
IPVPN_IPACT_Overview table.
Put another set of brackets around everything following the AND (as shown
below) which will result in both that particular request and at least one
of the OR conditions needing to be true to return a value.
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND ( ((PATINDEX('%SOW%', UPPER(Scope_of_Work)) > 0) OR
(PATINDEX('%IPDP%', UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%MACD%',
UPPER(Scope_of_Work)) > 0)) )
Cheers,
Jim.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Re: Fwd: Question: SQL
I think you may need one more set of brackets. It appears that the query
always returns 1 because Request_ID = '$Request ID$' is always true and at
least one of the OR conditions is true somewhere in the
IPVPN_IPACT_Overview table.
Put another set of brackets around everything following the AND (as shown
below) which will result in both that particular request and at least one
of the OR conditions needing to be true to return a value.
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND ( ((PATINDEX('%SOW%', UPPER(Scope_of_Work)) > 0) OR
(PATINDEX('%IPDP%', UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%MACD%',
UPPER(Scope_of_Work)) > 0)) )
Cheers,
Jim.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
Fwd: Question: SQL
I have created a filter to check when a user adds an attachment to look for
certain characters in the filename.
My set fields is reading from SQL as follows:
SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND ((PATINDEX('%SOW%', UPPER(Scope_of_Work)) > 0) OR
(PATINDEX('%IPDP%', UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%MACD%',
UPPER(Scope_of_Work)) > 0))
This works fine, however, the problem is even attachments I had without those
certain characters still returns a value of 1.
Can someone tell me is there something wrong with my SQL statement?
Any feedback / help would be greatly appreciated!
Thanks.
- End forwarded message -
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
