Thanks Jason!!!
I got some other examples from another programmer who has a lot of
experience in SQL Server:
I’ll have to try some of these out. I’m not sure what he means by ‘losing
the leading zero’…
I used getdate() as my date in the examples.
GETDATE() IS LIKE ORACLES SYSDATE
-----
--ONE WAY (REMOVE THE COMMENTS FROM THE CAST PART AND IT WILL BE INTEGER
BUT YOU WILL LOSE THE LEADING ZERO
SELECT
-- CAST(
RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(HOUR, GETDATE() )), 2) +
RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MINUTE, GETDATE() )), 2) +
RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(SECOND, GETDATE() )), 2) +
RIGHT('0' + CONVERT(VARCHAR(3), DATEPART(MILLISECOND, GETDATE() )), 3)
-- AS INT)
FROM bbdw.DIM_CURRENCY
--ANOTHER WAY BUT STILL NO LEADING ZERO
SELECT cast(
replace(
CONVERT(VARCHAR(20), GETDATE(),114
),':','')
as int)
from bbdw.DIM_CURRENCY
--ANOTHER WAY BUT STILL NO LEADING ZERO
SELECT
CAST(
REPLACE (
str(
DATEPART(hour, getdate())
,2)
+
str(
DATEPART(minute, getdate())
,2)
+
str(
DATEPART(second, getdate())
,2)
+
str(
DATEPART(millisecond, getdate())
,3)
,' ',0)
AS INT)
from bbdw.DIM_CURRENCY
*From:* Action Request System discussion list(ARSList) [mailto:
[email protected]] *On Behalf Of *Jason Miller
*Sent:* Monday, May 07, 2012 6:32 PM
*To:* [email protected]
*Subject:* Re: to commit or not to commit... that is the question
** Here is an example I used to update record 1 in a form called
"+TemplateFrom" updating a new field called "Date/Time Field" using the
Remedy generated SQL View via a Filter. I was not able to get $TIMESTAMP$
to resolve using ARUtilities so I had to build it using Remedy workflow.
UPDATE A_TemplateForm
SET Date_Time_Field = DATEDIFF(second, '1970-01-01 00:00:00',
'$TIMESTAMP$')+25200
WHERE Request_ID = '000000000000001'
Adding 25200 is to account for my GMT offset. There are more elegant ways
to account for your offset. I just kept it simple for this proof of
concept.
Jason
On Mon, May 7, 2012 at 12:29 PM, Nancy Tietz <[email protected]> wrote:
**
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"_
_attend WWRUG12 www.wwrug.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"
