All,
Thanks for your suggestions. I looked into the logs and played with the SQL
some more and got resolution. First thing I noticed once it started working
is that when you trigger a filter on save and/or modify of a field and
reference that field from the database, you get the value BEFORE the update
and not after. So, I re-worked it and used the power of SQL against the
field record and not the database entry and it worked. Below is the query I
used in case any are interested.
update HPD_Help_Desk set SLM_EMAIL_TO = (rtrim(ltrim(substring('$Assigned
Group$', Charindex('-', '$Assigned Group$') + 1, Len('$Assigned Group$'))))
+ 't...@client.com'), SLM_EMAIL_CC = (rtrim(ltrim(substring('$Assigned
Group$', Charindex('-', '$Assigned Group$') + 1, Len('$Assigned Group$'))))
+ 't...@client.com') + '; ' + (rtrim(ltrim(substring('$Assigned Group$',
Charindex('-', '$Assigned Group$') + 1, Len('$Assigned Group$')))) +
't...@client.com'), SLM_EMAIL_BCC = 'serviced...@gssamerica.com' where
(Incident_Number = '$Incident Number$' and (upper('$Assigned Group$') like
upper('%Tier 4%')))
Thanks,
Scott
Scott Fluegge
CIO & Sr. VP of Operations
GSS America Infotech Ltd., A SEI-CMMI Level 3 certified company
Third Floor, Block B,
Cyber Gateway, HITEC City, Madhapur,
Hyderabad - 500 081. AP. India
Phone : 91 40 44556600 ext: 312
Mobile: 91 9701222218
VoIP: (847) 307 7606 ext: 250
Email: scott.flue...@gssamerica.com
Visit: http://www.gssamerica.com
-----Original Message-----
From: Peter Romain [mailto:p.romain.arsl...@parsolutions.co.uk]
Sent: Wednesday, June 03, 2009 3:15 PM
Subject: Re: AW: Remedy 7.0 issue with Direct SQL in a filter
Scott,
You may find extra quotes are being inserted into the SQL.
Try building the complete SQL statement in a Remedy temp field and using
just this field in the direct sql action (Direct SQL -> $temp field$).
Cheers
Peter
> Scott,
>
> turn on SQL-Logging and you'll see the statement wich the arserver is
> issueing against the database. I think this should point you in the
> right direction.
>
> HTH
>
> Kind Regards Conny
>
> ________________________________
>
> Von: Action Request System discussion list(ARSList)
> [mailto:arsl...@arslist.org] Im Auftrag von Scott Fluegge
> Gesendet: Mittwoch, 3. Juni 2009 09:53
> An: arslist@ARSLIST.ORG
> Betreff: Remedy 7.0 issue with Direct SQL in a filter
> Wichtigkeit: Hoch
>
>
> **
>
> Anyone able to help me on a potentially simple Remedy question that has
> my team stumped?
>
>
>
> This is in regards to a Remedy customization we need to do in our
> environment. It is with respect to custom filters invoking Direct SQL
> (MS SQL 2005 database). The SQL works PERFECT from the enterprise
> manager but doesn't execute the complex query in Remedy. I did 4 direct
> SQL statements. 3 are complex and don't do anything. The simple 4th
> test (all are table updates) does work. I can't for the life of me
> figure out why the others fail. I am pasting the SQL here in case you
> can look into it... I am desperate!!! We are writing to hidden objects
> within the incident record which SLM will use for notification.
>
>
>
> Here is a sample that works:
>
> update HPD_Help_Desk set SLP_EMAIL_TO = 'email_address_here' where
> Incident_Number = '$Incident_Number$' and Assigned_Group like '%TIER 2%'
>
>
>
> And this one does nothing... (No errors unless I invalidate the SQL so
> seems a matching issue):
>
> update HPD_Help_Desk set SLM_EMAIL_TO =
> (rtrim(ltrim(substring(Assigned_Group, Charindex('-', Assigned_Group) +
> 1, Len(Assigned_Group)))) + 't...@client.com'), SLM_EMAIL_BCC =
> 'serviced...@gssamerica.com' where Incident_Number = '$Incident_Number$'
> and Assigned_Group like '%TIER 2%'
>
>
>
> Can you help me??? Just to re-iterate, the SQL works perfect from SQL
> Enterprise Manager (just changing the $Incident_Number$ to a real
> number) so I know it is not a simple SQL error...
>
> Thanks,
>
>
>
> Scott
>
> _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers
> Are"_
>
>
____________________________________________________________________________
___
> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
> Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
>
____________________________________________________________________________
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
