Wow – I just found out I can use the ‘SQL’ thing in ARUtilities to actually SEE my Data!!!
I’m so excited!!! Okay I’ll try to hold myself back on the ‘!’s and smileys… The C950000019 field is a new field that I added to hold the ‘opened’ date-time for the incident. It is a ‘date/time’ type of field. I’m on SQL Server – so the ‘sp_help’ is great to know, since I’m an Oracle person that got plopped into this BMC Remedy environment. (not very long ago as you all can tell…) So here is my Update statement that I hope would work… however I am unclear how to format the Timestamp now. So would I do an Integer($TIMESTAMP$) Or I’ll look it up somewhere… On Load of the window: UPDATE HPD_Help_Desk SET UM_Opened_By = $USER$, UM_Opened_Date = $TIMESTAMP$ WHERE Incident_Number = $Incident Number$ AND Incident_Number != $NULL$ AND UM_Opened_By = $NULL$; On Close of the window: UPDATE HPD_Help_Desk SET UM_Opened_By = $NULL$, UM_Opened_Date = $NULL$ WHERE Incident_Number = $Incident Number$ AND $Incident Number$ != $NULL$ AND UM_Opened_By = $USER$; Thanks everyone!! *From:* Action Request System discussion list(ARSList) [mailto: [email protected]] *On Behalf Of *Jason Miller *Sent:* Monday, May 07, 2012 3:19 PM *To:* [email protected] *Subject:* Re: to commit or not to commit... that is the question ** This is a dev system right? It appears you are getting a handle on a few different techniques and concepts here. I bet there are probably a few of us wincing while reading this thread. When working with Direct SQL your gun is always loaded. Be careful where you point that thing and make sure you always hit your target. That said we are absolutely here to help and you have caught our interest :) One thing that stood out to me in the the UPDATE is "C950000019 = $TIMESTAMP$." I am assuming C950000019 is a Remedy Date/Time field. Setting that to $TIMESTAMP$ in Direct SQL is going to give you errors because you'll be trying to put a character string into an Integer field. If C950000019 is a Character field you are good to go (but Date/Time kind of loses it's value when stored as text). To store $TIMESTAMP$ in an Remedy Date/Time field you will need to convert it to UNIX Epoch time (integer) before the UPDATE. Jason On Mon, May 7, 2012 at 11:58 AM, Joe Martin D'Souza <[email protected]> wrote: Just to add to Jason, replace spaces & special characters with _. You should see all your columns in your DB table or view if you describe (describe or desc) it.. eg: Desc HPD_Help_Desk Desc Tnnn (replace nnn with the schemaid as appropriate) This works on Oracle.. If you are using MS SQL or Sybase, describe will not work.. On MS-SQL & sybase use: sp_help HPD_Help_Desk Joe -----Original Message----- From: Jlbess Sent: Monday, May 07, 2012 2:46 PM Newsgroups: public.remedy.arsystem.general To: [email protected] Subject: Re: to commit or not to commit... that is the question If you're going to use C id's instead of your field names, you need to replace HPD_HELP_DESK with the T table. The views like HPD_HELP_DESK don't have the C names, they have the database name you gave your field. (replace spaces with underscore) You also need to put single quotes around '$USER$' and '$Incident Number$' Jason On May 7, 2012, at 1:48 PM, smiley <[email protected]> wrote: Hi again - Here is my (hopefully) correct Direct Sql (which is an Action within my Active Link): UPDATE HPD_Help_Desk SET C950000018 = $USER$, C950000019 = $TIMESTAMP$ WHERE C1000000161 = $Incident Number$ The 'C' numbers are my Field ID's. I'm not sure if I'm supposed to indicate the form HPD:Help Desk with the view HPD_Help_Desk. A SQL using the same column names is not working at all in ARUtilities > SQL, so ... I'm not sure how to test it. Any ideas how to test this?? I'm used to Oracle dbs, not MS SQL Server. I really appreciate it!!! Thank you for your help!!! _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
