Re: Sort of OT: Help with an SQL query on the AR System database...
The same field id could exist on multiple forms with different field lengths, and as Jarl said, you need to include the schema id. Curious though, do you need the subquery? How about: select C.SCHEMAID, C.NAME, A.FIELDNAME, A.FIELDID, B.MAXLENGTH from FIELD A, FIELD_CHAR B, ARSCHEMA C where (A.SCHEMAID = C.SCHEMAID) and (A.SCHEMAID = B.SCHEMAID) and (A.FIELDID = B.FIELDID) and (A.FIELDID = 100 AND A.FIELDID = 199) and (B.MAXLENGTH = 0 or B.MAXLENGTH = 1000) order by C.SCHEMAID, A.FIELDID; On Fri, May 15, 2015 at 10:13 AM, Joe D'Souza jdso...@shyle.net wrote: ** I have developed this query for listing all global fields (regular and window specific) that are either of 0 length or input length above 1000. select C.SCHEMAID, C.NAME, A.FIELDNAME, A.FIELDID, B.MAXLENGTH from FIELD A, FIELD_CHAR B, ARSCHEMA C where (A.SCHEMAID = C.SCHEMAID) and (A.SCHEMAID = B.SCHEMAID) and (A.FIELDID = B.FIELDID) and (A.FIELDID = 100 AND A.FIELDID = 199) and (A.FIELDID in (select B.FIELDID from FIELD_CHAR B where B.MAXLENGTH = 0 or B.MAXLENGTH = 1000)) order by C.SCHEMAID, A.FIELDID; Strangely in the MAXLENGTH column, I even see results that have a MAXLENGTH of 200, 255 and some other values under 1000 that are not 0.. What do you see wrong in my query? I’m hoping someone would spot what I can’t seem to on what I might have done wrong in my query. Cheers Joe _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: Sort of OT: Help with an SQL query on the AR System database...
Nevermind.. I was not thinking right.. select C.SCHEMAID, C.NAME, A.FIELDNAME, A.FIELDID, B.MAXLENGTH from FIELD A, FIELD_CHAR B, ARSCHEMA C where (A.SCHEMAID = C.SCHEMAID) and (A.SCHEMAID = B.SCHEMAID) and (A.FIELDID = B.FIELDID) and (A.FIELDID = 100 AND A.FIELDID = 199) and (B.MAXLENGTH = 0 or B.MAXLENGTH = 1000) order by C.SCHEMAID, A.FIELDID; I think this should give me the result I was after. Joe _ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Jarl Grøneng Sent: Friday, May 15, 2015 1:29 PM To: arslist@ARSLIST.ORG Subject: Re: Sort of OT: Help with an SQL query on the AR System database... ** Hi This subquery will list all the fields from all the forms You need to include the schemaid (A.FIELDID in (select B.FIELDID from FIELD_CHAR B where B.MAXLENGTH = 0 or B.MAXLENGTH = 1000)) -- J 2015-05-15 19:13 GMT+02:00 Joe D'Souza jdso...@shyle.net: ** I have developed this query for listing all global fields (regular and window specific) that are either of 0 length or input length above 1000. select C.SCHEMAID, C.NAME, A.FIELDNAME, A.FIELDID, B.MAXLENGTH from FIELD A, FIELD_CHAR B, ARSCHEMA C where (A.SCHEMAID = C.SCHEMAID) and (A.SCHEMAID = B.SCHEMAID) and (A.FIELDID = B.FIELDID) and (A.FIELDID = 100 AND A.FIELDID = 199) and (A.FIELDID in (select B.FIELDID from FIELD_CHAR B where B.MAXLENGTH = 0 or B.MAXLENGTH = 1000)) order by C.SCHEMAID, A.FIELDID; Strangely in the MAXLENGTH column, I even see results that have a MAXLENGTH of 200, 255 and some other values under 1000 that are not 0.. What do you see wrong in my query? Im hoping someone would spot what I cant seem to on what I might have done wrong in my query. Cheers Joe _ARSlist: Where the Answers Are and have been for 20 years_ _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: Sort of OT: Help with an SQL query on the AR System database...
Yes I was not thinking right. I had that subquery before I developed the whole query and left it there and then didn't think it was not required once I brought more tables into the query. I changed it exactly to what you have down there when Jarl pointed out what I was not looking at :-). Joe _ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Thad Esser Sent: Friday, May 15, 2015 1:37 PM To: arslist@ARSLIST.ORG Subject: Re: Sort of OT: Help with an SQL query on the AR System database... ** The same field id could exist on multiple forms with different field lengths, and as Jarl said, you need to include the schema id. Curious though, do you need the subquery? How about: select C.SCHEMAID, C.NAME, A.FIELDNAME, A.FIELDID, B.MAXLENGTH from FIELD A, FIELD_CHAR B, ARSCHEMA C where (A.SCHEMAID = C.SCHEMAID) and (A.SCHEMAID = B.SCHEMAID) and (A.FIELDID = B.FIELDID) and (A.FIELDID = 100 AND A.FIELDID = 199) and (B.MAXLENGTH = 0 or B.MAXLENGTH = 1000) order by C.SCHEMAID, A.FIELDID; On Fri, May 15, 2015 at 10:13 AM, Joe D'Souza jdso...@shyle.net wrote: ** I have developed this query for listing all global fields (regular and window specific) that are either of 0 length or input length above 1000. select C.SCHEMAID, C.NAME, A.FIELDNAME, A.FIELDID, B.MAXLENGTH from FIELD A, FIELD_CHAR B, ARSCHEMA C where (A.SCHEMAID = C.SCHEMAID) and (A.SCHEMAID = B.SCHEMAID) and (A.FIELDID = B.FIELDID) and (A.FIELDID = 100 AND A.FIELDID = 199) and (A.FIELDID in (select B.FIELDID from FIELD_CHAR B where B.MAXLENGTH = 0 or B.MAXLENGTH = 1000)) order by C.SCHEMAID, A.FIELDID; Strangely in the MAXLENGTH column, I even see results that have a MAXLENGTH of 200, 255 and some other values under 1000 that are not 0.. What do you see wrong in my query? I'm hoping someone would spot what I can't seem to on what I might have done wrong in my query. Cheers Joe _ARSlist: Where the Answers Are and have been for 20 years_ _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Sort of OT: Help with an SQL query on the AR System database...
I have developed this query for listing all global fields (regular and window specific) that are either of 0 length or input length above 1000. select C.SCHEMAID, C.NAME, A.FIELDNAME, A.FIELDID, B.MAXLENGTH from FIELD A, FIELD_CHAR B, ARSCHEMA C where (A.SCHEMAID = C.SCHEMAID) and (A.SCHEMAID = B.SCHEMAID) and (A.FIELDID = B.FIELDID) and (A.FIELDID = 100 AND A.FIELDID = 199) and (A.FIELDID in (select B.FIELDID from FIELD_CHAR B where B.MAXLENGTH = 0 or B.MAXLENGTH = 1000)) order by C.SCHEMAID, A.FIELDID; Strangely in the MAXLENGTH column, I even see results that have a MAXLENGTH of 200, 255 and some other values under 1000 that are not 0.. What do you see wrong in my query? I'm hoping someone would spot what I can't seem to on what I might have done wrong in my query. Cheers Joe ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: Sort of OT: Help with an SQL query on the AR System database...
Hi This subquery will list all the fields from all the forms You need to include the schemaid (A.FIELDID in (select B.FIELDID from FIELD_CHAR B where B.MAXLENGTH = 0 or B .MAXLENGTH = 1000)) -- J 2015-05-15 19:13 GMT+02:00 Joe D'Souza jdso...@shyle.net: ** I have developed this query for listing all global fields (regular and window specific) that are either of 0 length or input length above 1000. select C.SCHEMAID, C.NAME, A.FIELDNAME, A.FIELDID, B.MAXLENGTH from FIELD A, FIELD_CHAR B, ARSCHEMA C where (A.SCHEMAID = C.SCHEMAID) and (A.SCHEMAID = B.SCHEMAID) and (A.FIELDID = B.FIELDID) and (A.FIELDID = 100 AND A.FIELDID = 199) and (A.FIELDID in (select B.FIELDID from FIELD_CHAR B where B.MAXLENGTH = 0 or B.MAXLENGTH = 1000)) order by C.SCHEMAID, A.FIELDID; Strangely in the MAXLENGTH column, I even see results that have a MAXLENGTH of 200, 255 and some other values under 1000 that are not 0.. What do you see wrong in my query? I’m hoping someone would spot what I can’t seem to on what I might have done wrong in my query. Cheers Joe _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: How to find SQL query while querying a vendor form
Thanks all for your inputs. I could not find anything from the loggin option as the pluin log level were set to ALL. However I happened to look into configuration file of this custom plugin and could see the path of the log file for this plugin. I was able to see the relavant SQL queries in the plugin loggin file. Regards, Saurabh -- View this message in context: http://ars-action-request-system.1.n7.nabble.com/How-to-find-SQL-query-while-querying-a-vendor-form-tp121104p121163.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
How to find SQL query while querying a vendor form
i All, An active link is defined on a display-only form, its set field action is querying a vendor form using the EXTERNAL operator. From the extract of the below plugin log I can see its failing when tryin to query ORB:asset_display form. I am getting an error like shown below: /Database error: could not receive data from server: Error 0 while trying to execute SQL statement: SELECT CAST(element_id as varchar(15)) as PriKey, A.asset_hostname as COL0, A.asset_labelname as COL1, A.asset_operation_status as COL2, A.asset_s (ARERR 10502)/ I would like to know about the full SQL query which is queried at the external database. Can anybody point out from which log I might be able to fetch our this full query ?? Enabling SQL log is not helping me. ARDBCGetEntry – vendor CROOM.ARDBC.POSTGRE table ORB:project_display entry 512991 PLGN TID: 20 RPC ID: 102723 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:07.9915 */-GE OK PLGN TID: 20 RPC ID: 102724 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:07.9921 */+CT ARDBCCommitTransaction – vendor CROOM.ARDBC.POSTGRE PLGN TID: 20 RPC ID: 102724 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:07.9927 */-CT OK PLGN TID: 20 RPC ID: 102725 Queue: ARDBC Client-RPC: 390695 /* Wed /Mar 18 2015 07:28:08.4525 */+GLEWF ARDBCGetListEntryWithFields – vendor CROOM.ARDBC.POSTGRE table ORB:asset_display PLGN TID: 20 RPC ID: 102725 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:08.8188 */-GLEWF FAIL/ PLGN TID: 20 RPC ID: 102726 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:08.8199 */+RT ARDBCRollbackTransaction – vendor CROOM.ARDBC.POSTGRE PLGN TID: 20 RPC ID: 102726 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:08.8556 */-RT FAIL Regards, Saurabh -- View this message in context: http://ars-action-request-system.1.n7.nabble.com/How-to-find-SQL-query-while-querying-a-vendor-form-tp121104.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: How to find SQL query while querying a vendor form
Saurabh, If it was there, I would expect it to be between these two lines /* Wed /Mar 18 2015 07:28:08.4525 */+GLEWFARDBCGetListEntryWithFields – vendor CROOM.ARDBC.POSTGRE table ORB:asset_display /* Wed Mar 18 2015 07:28:08.8188 */-GLEWF FAIL/ but it's not there...you can try changing the logging level from whatever it is right now to Debug, restart the plugins, and try again to see if CROOM put debug statements into the log so you can see that sort of databut I don't know if it was done or not. On Wed, Mar 18, 2015 at 9:45 AM, MalviyaSaurabh malviya.saurab...@gmail.com wrote: i All, An active link is defined on a display-only form, its set field action is querying a vendor form using the EXTERNAL operator. From the extract of the below plugin log I can see its failing when tryin to query ORB:asset_display form. I am getting an error like shown below: /Database error: could not receive data from server: Error 0 while trying to execute SQL statement: SELECT CAST(element_id as varchar(15)) as PriKey, A.asset_hostname as COL0, A.asset_labelname as COL1, A.asset_operation_status as COL2, A.asset_s (ARERR 10502)/ I would like to know about the full SQL query which is queried at the external database. Can anybody point out from which log I might be able to fetch our this full query ?? Enabling SQL log is not helping me. ARDBCGetEntry – vendor CROOM.ARDBC.POSTGRE table ORB:project_display entry 512991 PLGN TID: 20 RPC ID: 102723 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:07.9915 */-GE OK PLGN TID: 20 RPC ID: 102724 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:07.9921 */+CT ARDBCCommitTransaction – vendor CROOM.ARDBC.POSTGRE PLGN TID: 20 RPC ID: 102724 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:07.9927 */-CT OK PLGN TID: 20 RPC ID: 102725 Queue: ARDBC Client-RPC: 390695 /* Wed /Mar 18 2015 07:28:08.4525 */+GLEWF ARDBCGetListEntryWithFields – vendor CROOM.ARDBC.POSTGRE table ORB:asset_display PLGN TID: 20 RPC ID: 102725 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:08.8188 */-GLEWF FAIL/ PLGN TID: 20 RPC ID: 102726 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:08.8199 */+RT ARDBCRollbackTransaction – vendor CROOM.ARDBC.POSTGRE PLGN TID: 20 RPC ID: 102726 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:08.8556 */-RT FAIL Regards, Saurabh -- View this message in context: http://ars-action-request-system.1.n7.nabble.com/How-to-find-SQL-query-while-querying-a-vendor-form-tp121104.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: How to find SQL query while querying a vendor form
As LJ points out, if the AR server was issuing the SQL it would be inside the API call. However, this is a vendor form backed by an ARDBC plugin, and it is this that is doing the actual data fetching. You’ll need to see what debug options are available for the plugin or, failing that, at the remote database end itself. Mark From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of LJ LongWing Sent: 18 March 2015 16:19 To: arslist@ARSLIST.ORG Subject: Re: How to find SQL query while querying a vendor form ** Saurabh, If it was there, I would expect it to be between these two lines /* Wed /Mar 18 2015 07:28:08.4525 */+GLEWFARDBCGetListEntryWithFields – vendor CROOM.ARDBC.POSTGRE table ORB:asset_display /* Wed Mar 18 2015 07:28:08.8188 */-GLEWF FAIL/ but it's not there...you can try changing the logging level from whatever it is right now to Debug, restart the plugins, and try again to see if CROOM put debug statements into the log so you can see that sort of databut I don't know if it was done or not. On Wed, Mar 18, 2015 at 9:45 AM, MalviyaSaurabh malviya.saurab...@gmail.commailto:malviya.saurab...@gmail.com wrote: i All, An active link is defined on a display-only form, its set field action is querying a vendor form using the EXTERNAL operator. From the extract of the below plugin log I can see its failing when tryin to query ORB:asset_display form. I am getting an error like shown below: /Database error: could not receive data from server: Error 0 while trying to execute SQL statement: SELECT CAST(element_id as varchar(15)) as PriKey, A.asset_hostname as COL0, A.asset_labelname as COL1, A.asset_operation_status as COL2, A.asset_s (ARERR 10502)/ I would like to know about the full SQL query which is queried at the external database. Can anybody point out from which log I might be able to fetch our this full query ?? Enabling SQL log is not helping me. ARDBCGetEntry – vendor CROOM.ARDBC.POSTGRE table ORB:project_display entry 512991 PLGN TID: 20 RPC ID: 102723 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:07.9915 */-GE OK PLGN TID: 20 RPC ID: 102724 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:07.9921 */+CT ARDBCCommitTransaction – vendor CROOM.ARDBC.POSTGRE PLGN TID: 20 RPC ID: 102724 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:07.9927 */-CT OK PLGN TID: 20 RPC ID: 102725 Queue: ARDBC Client-RPC: 390695 /* Wed /Mar 18 2015 07:28:08.4525 */+GLEWF ARDBCGetListEntryWithFields – vendor CROOM.ARDBC.POSTGRE table ORB:asset_display PLGN TID: 20 RPC ID: 102725 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:08.8188 */-GLEWF FAIL/ PLGN TID: 20 RPC ID: 102726 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:08.8199 */+RT ARDBCRollbackTransaction – vendor CROOM.ARDBC.POSTGRE PLGN TID: 20 RPC ID: 102726 Queue: ARDBC Client-RPC: 390695 /* Wed Mar 18 2015 07:28:08.8556 */-RT FAIL Regards, Saurabh -- View this message in context: http://ars-action-request-system.1.n7.nabble.com/How-to-find-SQL-query-while-querying-a-vendor-form-tp121104.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.orghttp://www.arslist.org Where the Answers Are, and have been for 20 years _ARSlist: Where the Answers Are and have been for 20 years_ BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: SQL Query
My recommendation would be to do a run process. Like everyone else is saying, the best way to run an escalation is to use a form just for escalations. One record per escalation. That way you have a small amount of records for it to walk over. Then use a filter to perform the work. When you run the escalation on a form, it reviews EVERY record in that form and if it passes the qualification fires the work for that record. Then again for each record it finds. If it finds ANY it does NOT fire the else action. If it finds NONE it will fire the ELSE action ONCE. If you run the process below, it will perform the updates to the server in a MUCH faster manner. This run process command can only be performed in a filter or an escalation. Application-Query-Delete-Entry form qualificationString Brian Goralczyk Phone 574-643-1144 Email bgoralc...@gmail.com On Thu, Aug 15, 2013 at 5:54 PM, Kathy Morris kathymorris...@aol.comwrote: ** ** ** I had to shut off the escalations because our system was hung. There were over 36,000 records. Yesterday there were over 500,000 records. I did know there was a multi thread delete. Is this in Oracle? ** ** Thanks so much. I have been struggling with this all day. ** ** *From:* Action Request System discussion list(ARSList) [mailto: arslist@ARSLIST.ORG] *On Behalf Of *Brian Goralczyk *Sent:* Thursday, August 15, 2013 5:47 PM *To:* arslist@ARSLIST.ORG *Subject:* Re: SQL Query ** ** ** Do NOT use an escalation. It will review all the rows. Unless you are running it on a different table that doesn't have a lot of rows and doing a delete from that. ** ** You want to make sure you do a multi thread delete and not a single thread delete. ** ** What DB are you using? ** ** If you are using Oracle I could give you an sample sql statement that would delete what you need fairly painlessly. ** ** Brian Goralczyk Brian Goralczyk Phone 574-643-1144 Email bgoralc...@gmail.com ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
SQL Query
Hi, I am trying to remove specific records from a table (T350). If I run a SQL query on a Remedy form that has a T-table (T350) with a B-table (B350) attachments, Am I going to be able to delete the data in both tables? My query: Select C8 from T350 Where C8 = 'Reconciliation' My concern is. how am I going to delete the records in the B-table that are associated with the records from the T table? The Column C8 does not exist in the B350 table so I cannot write. Select C8 from B350 Where C8 = 'Reconciliation' - C8 does not exist in the B table. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: SQL Query
Don't forget your H350 table too. But the C1 from T350 if I remember right is the C1 from H350 B350. Also, can't you do this with an escalation or filter? If you do this with workflow the system will get all the sub tables for you. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Kathy Morris Sent: Thursday, August 15, 2013 7:26 AM To: arslist@ARSLIST.ORG Subject: SQL Query ** Hi, I am trying to remove specific records from a table (T350). If I run a SQL query on a Remedy form that has a T-table (T350) with a B-table (B350) attachments, Am I going to be able to delete the data in both tables? My query: Select C8 from T350 Where C8 = 'Reconciliation' My concern is... how am I going to delete the records in the B-table that are associated with the records from the T table? The Column C8 does not exist in the B350 table so I cannot write... Select C8 from B350 Where C8 = 'Reconciliation' - C8 does not exist in the B table. _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: SQL Query
Kathy, Is this about the RE:Job Runs form? Use ARSystem to do the delete. It will clean up H and B tables for you. Did you create the filter to do the delete? Are you having an issue deleting the RE:Job runs records? Regards, Ryan. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Kathy Morris Sent: Thursday, August 15, 2013 10:26 AM To: arslist@ARSLIST.ORG Subject: SQL Query ** Hi, I am trying to remove specific records from a table (T350). If I run a SQL query on a Remedy form that has a T-table (T350) with a B-table (B350) attachments, Am I going to be able to delete the data in both tables? My query: Select C8 from T350 Where C8 = 'Reconciliation' My concern is... how am I going to delete the records in the B-table that are associated with the records from the T table? The Column C8 does not exist in the B350 table so I cannot write... Select C8 from B350 Where C8 = 'Reconciliation' - C8 does not exist in the B table. _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: SQL Query
Is there any reason you can't remove them using the client? You would have to do a compound query and do the B and H table(s) first before deleting from the T table i.e. Select C1 From B350 Where C1 in (Select C1 from T350 Where C8 = 'Reconciliation' ) Select ENTRYID From B350C536870923 Where ENTRYID in (Select C1 from T350 Where C8 = 'Reconciliation' ) Select ENTRYID From H350 Where ENTRYID in (Select C1 from T350 Where C8 = 'Reconciliation' ) Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Kathy Morris Sent: Thursday, August 15, 2013 9:26 AM To: arslist@ARSLIST.ORG Subject: SQL Query ** Hi, I am trying to remove specific records from a table (T350). If I run a SQL query on a Remedy form that has a T-table (T350) with a B-table (B350) attachments, Am I going to be able to delete the data in both tables? My query: Select C8 from T350 Where C8 = 'Reconciliation' My concern is... how am I going to delete the records in the B-table that are associated with the records from the T table? The Column C8 does not exist in the B350 table so I cannot write... Select C8 from B350 Where C8 = 'Reconciliation' - C8 does not exist in the B table. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: SQL Query
Kathy, Unless you need to keep the records in this form.perform a truncate on the T H and B tables If it is the RE:Job Runs form the records on only for referencenothing else.. Regards, Ryan. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Kathy Morris Sent: Thursday, August 15, 2013 10:43 AM To: arslist@ARSLIST.ORG Subject: Re: SQL Query ** ** Our performance is so poor right now that we are doing this directly thru SQL. I did not do a query on C1, I queried C8. Is there something that need to do with C1? We are trying to stabilize things because a lot of records are created in the table and it's degrading performance right now. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG]mailto:[mailto:arslist@ARSLIST.ORG] On Behalf Of Pargeter, Christie :CO IS Sent: Thursday, August 15, 2013 10:36 AM To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: Re: SQL Query ** Don't forget your H350 table too. But the C1 from T350 if I remember right is the C1 from H350 B350. Also, can't you do this with an escalation or filter? If you do this with workflow the system will get all the sub tables for you. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Kathy Morris Sent: Thursday, August 15, 2013 7:26 AM To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: SQL Query ** Hi, I am trying to remove specific records from a table (T350). If I run a SQL query on a Remedy form that has a T-table (T350) with a B-table (B350) attachments, Am I going to be able to delete the data in both tables? My query: Select C8 from T350 Where C8 = 'Reconciliation' My concern is... how am I going to delete the records in the B-table that are associated with the records from the T table? The Column C8 does not exist in the B350 table so I cannot write... Select C8 from B350 Where C8 = 'Reconciliation' - C8 does not exist in the B table. _ARSlist: Where the Answers Are and have been for 20 years_ _ARSlist: Where the Answers Are and have been for 20 years_ _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: SQL Query
Hi, We are trying to delete data from 3 forms. RE: Job Runs RE: Job Events Application Pending Our team Project Manager wants to run a history report on the data that was in Job Runs to measure how long reconciliation jobs are running (instead of adding another 10 Gig of Memory). To prepare to truncate the table, I exported the data in from RE: Job Runs directly from Oracle to .csv file, however the Run Start Time exported as 1,354,455,451 . Application Pending cannot be completely deleted. Not sure of the impact of deleting all the data in RE: Job Events.. We do need some history. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Downing, Ryan Sent: Thursday, August 15, 2013 11:03 AM To: arslist@ARSLIST.ORG Subject: Re: SQL Query ** Kathy, Unless you need to keep the records in this form...perform a truncate on the T H and B tables If it is the RE:Job Runs form the records on only for reference..nothing else.. Regards, Ryan. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Kathy Morris Sent: Thursday, August 15, 2013 10:43 AM To: arslist@ARSLIST.ORG Subject: Re: SQL Query ** ** Our performance is so poor right now that we are doing this directly thru SQL. I did not do a query on C1, I queried C8. Is there something that need to do with C1? We are trying to stabilize things because a lot of records are created in the table and it's degrading performance right now. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Pargeter, Christie :CO IS Sent: Thursday, August 15, 2013 10:36 AM To: arslist@ARSLIST.ORG Subject: Re: SQL Query ** Don't forget your H350 table too. But the C1 from T350 if I remember right is the C1 from H350 B350. Also, can't you do this with an escalation or filter? If you do this with workflow the system will get all the sub tables for you. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Kathy Morris Sent: Thursday, August 15, 2013 7:26 AM To: arslist@ARSLIST.ORG Subject: SQL Query ** Hi, I am trying to remove specific records from a table (T350). If I run a SQL query on a Remedy form that has a T-table (T350) with a B-table (B350) attachments, Am I going to be able to delete the data in both tables? My query: Select C8 from T350 Where C8 = 'Reconciliation' My concern is. how am I going to delete the records in the B-table that are associated with the records from the T table? The Column C8 does not exist in the B350 table so I cannot write. Select C8 from B350 Where C8 = 'Reconciliation' - C8 does not exist in the B table. _ARSlist: Where the Answers Are and have been for 20 years_ _ARSlist: Where the Answers Are and have been for 20 years_ _ARSlist: Where the Answers Are and have been for 20 years_ _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: SQL Query
We are not using the client because it was hanging, and I tried the escalation to delete the entries however it hung. Thanks From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Thursday, August 15, 2013 10:36 AM To: arslist@ARSLIST.ORG Subject: Re: SQL Query ** Is there any reason you can't remove them using the client? You would have to do a compound query and do the B and H table(s) first before deleting from the T table i.e. Select C1 From B350 Where C1 in (Select C1 from T350 Where C8 = 'Reconciliation' ) Select ENTRYID From B350C536870923 Where ENTRYID in (Select C1 from T350 Where C8 = 'Reconciliation' ) Select ENTRYID From H350 Where ENTRYID in (Select C1 from T350 Where C8 = 'Reconciliation' ) Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Kathy Morris Sent: Thursday, August 15, 2013 9:26 AM To: arslist@ARSLIST.ORG Subject: SQL Query ** Hi, I am trying to remove specific records from a table (T350). If I run a SQL query on a Remedy form that has a T-table (T350) with a B-table (B350) attachments, Am I going to be able to delete the data in both tables? My query: Select C8 from T350 Where C8 = 'Reconciliation' My concern is. how am I going to delete the records in the B-table that are associated with the records from the T table? The Column C8 does not exist in the B350 table so I cannot write. Select C8 from B350 Where C8 = 'Reconciliation' - C8 does not exist in the B table. _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: SQL Query
*** What is B350C536870923? When I entered this value B30+the column - the query did not work for me. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Thursday, August 15, 2013 10:36 AM To: arslist@ARSLIST.ORG Subject: Re: SQL Query ** Is there any reason you can't remove them using the client? You would have to do a compound query and do the B and H table(s) first before deleting from the T table i.e. Select C1 From B350 Where C1 in (Select C1 from T350 Where C8 = 'Reconciliation' ) Select ENTRYID From B350C536870923 Where ENTRYID in (Select C1 from T350 Where C8 = 'Reconciliation' ) Select ENTRYID From H350 Where ENTRYID in (Select C1 from T350 Where C8 = 'Reconciliation' ) Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Kathy Morris Sent: Thursday, August 15, 2013 9:26 AM To: arslist@ARSLIST.ORG Subject: SQL Query ** Hi, I am trying to remove specific records from a table (T350). If I run a SQL query on a Remedy form that has a T-table (T350) with a B-table (B350) attachments, Am I going to be able to delete the data in both tables? My query: Select C8 from T350 Where C8 = 'Reconciliation' My concern is. how am I going to delete the records in the B-table that are associated with the records from the T table? The Column C8 does not exist in the B350 table so I cannot write. Select C8 from B350 Where C8 = 'Reconciliation' - C8 does not exist in the B table. _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: SQL Query
I was just giving an example (the 536870923 was an example of a field ID) . If you look in your database you should see a B350Cx table. That holds the actual attachment. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Kathy Morris Sent: Thursday, August 15, 2013 11:24 AM To: arslist@ARSLIST.ORG Subject: Re: SQL Query ** *** What is B350C536870923? When I entered this value B30+the column - the query did not work for me. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Thursday, August 15, 2013 10:36 AM To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: Re: SQL Query ** Is there any reason you can't remove them using the client? You would have to do a compound query and do the B and H table(s) first before deleting from the T table i.e. Select C1 From B350 Where C1 in (Select C1 from T350 Where C8 = 'Reconciliation' ) Select ENTRYID From B350C536870923 Where ENTRYID in (Select C1 from T350 Where C8 = 'Reconciliation' ) Select ENTRYID From H350 Where ENTRYID in (Select C1 from T350 Where C8 = 'Reconciliation' ) Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Kathy Morris Sent: Thursday, August 15, 2013 9:26 AM To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: SQL Query ** Hi, I am trying to remove specific records from a table (T350). If I run a SQL query on a Remedy form that has a T-table (T350) with a B-table (B350) attachments, Am I going to be able to delete the data in both tables? My query: Select C8 from T350 Where C8 = 'Reconciliation' My concern is... how am I going to delete the records in the B-table that are associated with the records from the T table? The Column C8 does not exist in the B350 table so I cannot write... Select C8 from B350 Where C8 = 'Reconciliation' - C8 does not exist in the B table. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: SQL Query
Do NOT use an escalation. It will review all the rows. Unless you are running it on a different table that doesn't have a lot of rows and doing a delete from that. You want to make sure you do a multi thread delete and not a single thread delete. What DB are you using? If you are using Oracle I could give you an sample sql statement that would delete what you need fairly painlessly. Brian Goralczyk Brian Goralczyk Phone 574-643-1144 Email bgoralc...@gmail.com On Thu, Aug 15, 2013 at 11:31 AM, Kathy Morris kathymorris...@aol.comwrote: ** We are not using the client because it was hanging, and I tried the escalation to delete the entries however it hung. Thanks ** ** *From:* Action Request System discussion list(ARSList) [mailto: arslist@ARSLIST.ORG] *On Behalf Of *Grooms, Frederick W *Sent:* Thursday, August 15, 2013 10:36 AM *To:* arslist@ARSLIST.ORG *Subject:* Re: SQL Query ** ** ** Is there any reason you can’t remove them using the client? ** ** You would have to do a compound query and do the B and H table(s) first before deleting from the T table ** ** i.e. Select C1 From B350 Where C1 in (Select C1 from T350 Where C8 = ‘Reconciliation’ ) ** ** Select ENTRYID From B350C536870923 Where ENTRYID in (Select C1 from T350 Where C8 = ‘Reconciliation’ ) ** ** Select ENTRYID From H350 Where ENTRYID in (Select C1 from T350 Where C8 = ‘Reconciliation’ ) ** ** Fred ** ** ** ** ** ** *From:* Action Request System discussion list(ARSList) [ mailto:arslist@ARSLIST.ORG arslist@ARSLIST.ORG] *On Behalf Of *Kathy Morris *Sent:* Thursday, August 15, 2013 9:26 AM *To:* arslist@ARSLIST.ORG *Subject:* SQL Query ** ** ** Hi, ** ** I am trying to remove specific records from a table (T350). If I run a SQL query on a Remedy form that has a T-table (T350) with a B-table (B350) attachments, Am I going to be able to delete the data in both tables? ** ** My query: Select C8 from T350 Where C8 = ‘Reconciliation’ ** ** My concern is… how am I going to delete the records in the B-table that are associated with the records from the T table? The Column C8 does not exist in the B350 table so I cannot write… Select C8 from B350 Where C8 = ‘Reconciliation’ - C8 does not exist in the B table. ** ** ** ** ** ** _ARSlist: Where the Answers Are and have been for 20 years_ _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: SQL Query
I had to shut off the escalations because our system was hung. There were over 36,000 records. Yesterday there were over 500,000 records. I did know there was a multi thread delete. Is this in Oracle? Thanks so much. I have been struggling with this all day. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Brian Goralczyk Sent: Thursday, August 15, 2013 5:47 PM To: arslist@ARSLIST.ORG Subject: Re: SQL Query ** Do NOT use an escalation. It will review all the rows. Unless you are running it on a different table that doesn't have a lot of rows and doing a delete from that. You want to make sure you do a multi thread delete and not a single thread delete. What DB are you using? If you are using Oracle I could give you an sample sql statement that would delete what you need fairly painlessly. Brian Goralczyk Brian Goralczyk Phone 574-643-1144 Email bgoralc...@gmail.com On Thu, Aug 15, 2013 at 11:31 AM, Kathy Morris kathymorris...@aol.com wrote: ** We are not using the client because it was hanging, and I tried the escalation to delete the entries however it hung. Thanks From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Thursday, August 15, 2013 10:36 AM To: arslist@ARSLIST.ORG Subject: Re: SQL Query ** Is there any reason you can't remove them using the client? You would have to do a compound query and do the B and H table(s) first before deleting from the T table i.e. Select C1 From B350 Where C1 in (Select C1 from T350 Where C8 = 'Reconciliation' ) Select ENTRYID From B350C536870923 Where ENTRYID in (Select C1 from T350 Where C8 = 'Reconciliation' ) Select ENTRYID From H350 Where ENTRYID in (Select C1 from T350 Where C8 = 'Reconciliation' ) Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Kathy Morris Sent: Thursday, August 15, 2013 9:26 AM To: arslist@ARSLIST.ORG Subject: SQL Query ** Hi, I am trying to remove specific records from a table (T350). If I run a SQL query on a Remedy form that has a T-table (T350) with a B-table (B350) attachments, Am I going to be able to delete the data in both tables? My query: Select C8 from T350 Where C8 = 'Reconciliation' My concern is. how am I going to delete the records in the B-table that are associated with the records from the T table? The Column C8 does not exist in the B350 table so I cannot write. Select C8 from B350 Where C8 = 'Reconciliation' - C8 does not exist in the B table. _ARSlist: Where the Answers Are and have been for 20 years_ _ARSlist: Where the Answers Are and have been for 20 years_ _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: Populating Table field with sql query result
Hi Ravi; I've not had a reason to do this yet, so I can't offer specifics (happily, there's been lots of good and related information over the last couple of days--and much more earlier, I'm sure). You'll need a view or vendor form set up so that ARS can see the table from the other database--after that, you can probably run queries against the view/vendor form in a similar way. View forms generally require a link specified in ar.cfg with the connection string of the remote DB, and vendor forms require an ARDBC plug-in to use. If you can't get direct access to the remote DB, maybe you can get the other DBA to ship you the data in batches to import (eeew)? Sorry I couldn't be more help, but if you give these grand ARS listers more detail like remote DB type, the access you're likely to have to it, ARS specs...well, all that, I bet they've more clues :) ~james -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ravi Sent: Thursday, May 22, 2008 8:43 PM To: arslist@ARSLIST.ORG Subject: Re: Populating Table field with sql query result James: I have been trying to do something similar. One question. If the table on which I want to run the sql query is on a different database, what do I have to do within Remedy so it can connect to a table in that database. Thanks Ravi Russell, James C wrote: ** Hello Victor; I don't know that this is what you're looking for, but I run a series of sql queries in a single escalation to populate a 'current state' kinda form--I'm using one query per field; might not be the most effective way in the world, but it works. This has MS SQL date conversions in--oracle is different and a bit more cryptic (IMO--I've got those around somewhere if you need 'em): Here's a Set Fields example that works just fine: Read Value for Field From: SQL SQL Command: SELECT COUNT(1) FROM Your_Form_Name WHERE (Create_Date = DATEDIFF(s, '19700101', DATEADD(mm,-30,getdate( AND (rb_TicketType = 5) If No Requests Match: Set fields to NULL If Multiple Records Match: Use first matching Fields: Name: cntTiks Value: $1$ ...there's no reason in the world that you couldn't make a set fields with an sql statement like: SELECT thing1, thing2, thing3, ... ,thingN FROM Your_Form WHERE Field_name = 'char_value' OR Field_Name2 84600 (or whatever--could do joins and unions against multiple forms if necessary) ..and then the Names/values in the 'Fields' section of the set fields is like: Name: Field1 Value: $1$ Name: Field2 Value: $2$ Name: Field3 Value: $3$ : : Name: FieldN Value: $N$ When you're choosing a value for the field, the drop-down list will give you an option to choose a value x from the 'SQL Result Column,' where x is a number between 1 and N (the number of fields you're pulling in your select statement). Erm. For whatever it's worth. HTH ~james -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Victor Sent: Sunday, May 18, 2008 12:47 PM To: arslist@ARSLIST.ORG Subject: Populating Table field with sql query result Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Populating Table field with sql query result
On Sunday 18 of May 2008 22:39:03 Grooms, Frederick W wrote: Have your SQL Query populate a table in the database and have a View form pointing to that table. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Victor Sent: Sunday, May 18, 2008 12:47 PM To: arslist@ARSLIST.ORG Subject: Populating Table field with sql query result Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are Thank you Fred - but I'm afraid this approach is unacceptable. The idea is to introduce a (sort of) summary button on system class form. On clicking this button a summary page describing this particular CI together its dependencies,components and components of its dependencies (in a single Table form) will be displayed. Remedy ARS 6.3 Oracle 9i database MS IIS Windows 2000 Any suggestions please? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Populating Table field with sql query result
Victor, It is not the same as an ARS table field... but What if you write workflow that walks the data and builds a dynamic HTML page that is displayed in a View field? (Which by the way is basically what the CI viewer does, only it uses a Data Visualization Field which has a few more bells and whistles than a plain old View field(iframe).) Do you think this approach could work for the desired UI? -- Carey Matthew Black Remedy Skilled Professional (RSP) ARS = Action Request System(Remedy) Love, then teach Solution = People + Process + Tools Fast, Accurate, Cheap Pick two. On Thu, May 22, 2008 at 5:44 AM, Victor [EMAIL PROTECTED] wrote: On Sunday 18 of May 2008 22:39:03 Grooms, Frederick W wrote: Have your SQL Query populate a table in the database and have a View form pointing to that table. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Victor Sent: Sunday, May 18, 2008 12:47 PM To: arslist@ARSLIST.ORG Subject: Populating Table field with sql query result Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are Thank you Fred - but I'm afraid this approach is unacceptable. The idea is to introduce a (sort of) summary button on system class form. On clicking this button a summary page describing this particular CI together its dependencies,components and components of its dependencies (in a single Table form) will be displayed. Remedy ARS 6.3 Oracle 9i database MS IIS Windows 2000 Any suggestions please? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Populating Table field with sql query result
** Hello Victor; I don't know that this is what you're looking for, but I run a series of sql queries in a single escalation to populate a 'current state' kinda form--I'm using one query per field; might not be the most effective way in the world, but it works. This has MS SQL date conversions in--oracle is different and a bit more cryptic (IMO--I've got those around somewhere if you need 'em): Here's a Set Fields example that works just fine: Read Value for Field From: SQL SQL Command: SELECT COUNT(1) FROM Your_Form_Name WHERE (Create_Date = DATEDIFF(s, '19700101', DATEADD(mm,-30,getdate( AND (rb_TicketType = 5) If No Requests Match: Set fields to NULL If Multiple Records Match: Use first matching Fields: Name: cntTiks Value: $1$ ...there's no reason in the world that you couldn't make a set fields with an sql statement like: SELECT thing1, thing2, thing3, ... ,thingN FROM Your_Form WHERE Field_name = 'char_value' OR Field_Name2 84600 (or whatever--could do joins and unions against multiple forms if necessary) ..and then the Names/values in the 'Fields' section of the set fields is like: Name: Field1 Value: $1$ Name: Field2 Value: $2$ Name: Field3 Value: $3$ : : Name: FieldN Value: $N$ When you're choosing a value for the field, the drop-down list will give you an option to choose a value x from the 'SQL Result Column,' where x is a number between 1 and N (the number of fields you're pulling in your select statement). Erm. For whatever it's worth. HTH ~james -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Victor Sent: Sunday, May 18, 2008 12:47 PM To: arslist@ARSLIST.ORG Subject: Populating Table field with sql query result Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Populating Table field with sql query result
James: I have been trying to do something similar. One question. If the table on which I want to run the sql query is on a different database, what do I have to do within Remedy so it can connect to a table in that database. Thanks Ravi Russell, James C wrote: ** Hello Victor; I don't know that this is what you're looking for, but I run a series of sql queries in a single escalation to populate a 'current state' kinda form--I'm using one query per field; might not be the most effective way in the world, but it works. This has MS SQL date conversions in--oracle is different and a bit more cryptic (IMO--I've got those around somewhere if you need 'em): Here's a Set Fields example that works just fine: Read Value for Field From: SQL SQL Command: SELECT COUNT(1) FROM Your_Form_Name WHERE (Create_Date = DATEDIFF(s, '19700101', DATEADD(mm,-30,getdate( AND (rb_TicketType = 5) If No Requests Match: Set fields to NULL If Multiple Records Match: Use first matching Fields: Name: cntTiks Value: $1$ ...there's no reason in the world that you couldn't make a set fields with an sql statement like: SELECT thing1, thing2, thing3, ... ,thingN FROM Your_Form WHERE Field_name = 'char_value' OR Field_Name2 84600 (or whatever--could do joins and unions against multiple forms if necessary) ..and then the Names/values in the 'Fields' section of the set fields is like: Name: Field1 Value: $1$ Name: Field2 Value: $2$ Name: Field3 Value: $3$ : : Name: FieldN Value: $N$ When you're choosing a value for the field, the drop-down list will give you an option to choose a value x from the 'SQL Result Column,' where x is a number between 1 and N (the number of fields you're pulling in your select statement). Erm. For whatever it's worth. HTH ~james -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Victor Sent: Sunday, May 18, 2008 12:47 PM To: arslist@ARSLIST.ORG Subject: Populating Table field with sql query result Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Populating Table field with sql query result
*Hi Victor, *On search in doc, I found related doc for you. To pull information from external tables, you can use the Set Fields action with the Read Value for Field From set to SQL. This allows you to send an SQL |SELECT| command to the database and assign the return values to AR System fields. Observe the following general rules for using SQL commands: * You need not use every value that is returned from the SQL command, but you must use at least one. * You can use the same value in more than one field. * You can issue only one SQL command per action. You cannot enter two commands separated by a semicolon and have both commands run. To run a set of commands, create separate actions, or create a stored procedure and run that. (Stored procedures do not return values.) * Turn on AR System server SQL logging to debug the SQL syntax if it returns unexpected values or results. A good debugging strategy is to start an SQL interpreter (for example, isql for Sybase, SQL*Plus for Oracle^® , Command Center for DB2, or Query Analyzer for SQL Server) and to enter the same SQL command directly into the database to verify its validity. * Because there is no error checking on the SQL statement, run the SQL statement directly against the database (as a test) before you enter it into the SQL Command field. You can then copy and paste the tested SQL command directly into the SQL Command field. * If the SQL operation fails, an AR System error message and the underlying database error message appear * For more information about Set Fields action with SQL, see the /Workflow Objects/ guide. Hope this helps... Regards, *Sandeep Vyom Labs Pvt. Ltd. An ISO 2 certified company. Consulting | Outsourcing | Training || BMC Remedy BSM | ITIL Web : www.vyomlabs.com * Victor wrote: Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Populating Table field with sql query result
Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Populating Table field with sql query result
Have your SQL Query populate a table in the database and have a View form pointing to that table. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Victor Sent: Sunday, May 18, 2008 12:47 PM To: arslist@ARSLIST.ORG Subject: Populating Table field with sql query result Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Remedy SQL query assistance... Please Help.
We would like to do this in Access but are running into an issue with extra characters being recognized and fails to connect, currently running this in Excel. We are going to use the Closed option due to it being the last in the list. And since this is going to be a process that is automated and updating every 2 minutes we kind of did not want to enter the actual date in the code. We were hoping to use a function similar to $DATE$ to capture the current date, we currently have that function in a macro that spits the data to a csv like you mentioned. The only problem is that some of the macros will not have data in the early morning so that is why we are shooting for a SQL query. Remedy gives you an error when no data is found and since there is no way to not display errors like that it stops the rest of the Remedy macros from running behind it. I attached the macro that we are using. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Eli Schilling Sent: Friday, December 14, 2007 7:21 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy SQL query assistance... Please Help. ** Raul, are you running this query from within MS Excel? Crystal Reports? Also, If you're using an ARSystem ODBC driver the date conversion formula I gave you isn't necessary. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 12:35 PM To: arslist@ARSLIST.ORG Subject: FW: Remedy SQL query assistance... Please Help. ** From: Zaldivar, Jack (ISD, IT) Sent: Friday, December 14, 2007 3:33 PM To: Martinez, Raul (ISD, IT) Subject: RE: Remedy SQL query assistance... Please Help. SELECT HPD:HelpDesk.Assigned To Group+, HPD:HelpDesk.Assigned To Individual+, HPD:HelpDesk.Assignee Login Name, HPD:HelpDesk.Case ID+, HPD:HelpDesk.Source, HPD:HelpDesk.Status, HPD:HelpDesk.Submitted By FROM HPD:HelpDesk HPD:HelpDesk WHERE (dateadd(ss, (convert(int, Create Date))+(3600*-8), '01-01-1970 00:00:00') = '12/13/2007' AND dateadd(ss, (convert(int, Create Date))+(3600*-8), '01-01-1970 00:00:00') '12/14/2007') AND (HPD:HelpDesk.Status'Resolved' And HPD:HelpDesk.Status'Closed') ORDER BY HPD:HelpDesk.Case ID+ * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are image001.jpg AssignedToServDeskCombined.arq Description: AssignedToServDeskCombined.arq
Re: Remedy SQL query assistance... Please Help.
If you are using SQL directly to the database then you have to do all of the translations for Selection Fields and Date (Date/Time, Date, and Time) Fields yourself. Selection Fields like Status are stored in the database as integers (1st selection = 0, 2nd selection = 1, ...) Date Fields are stored in the database as integers as well. Date/Time fields are the number of seconds since January 1, 1970 Date fields are the number of days since January 1, 4713 B.C. Time fields are the number of seconds since midnight Also since you will be doing this every 2 minutes here is a performance tip. Do all of the conversions on your side of the qualifications. (Instead of doing the conversion on a field in the database to convert it to what you are looking for, convert what you are looking for into the way it is stored in the databse. If you convert the field from the database then every row in the database will have to be checked to see if it matched your qualifications.) i.e. Instead of converting the date field in the database to a real date so you can check if it is a value, convert your value into an integer as it would be stored in the database. Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Tuesday, December 18, 2007 3:21 PM To: arslist@ARSLIST.ORG Subject: Remedy SQL query assistance... Please Help. ** We would like to do this in Access but are running into an issue with extra characters being recognized and fails to connect, currently running this in Excel. We are going to use the Closed option due to it being the last in the list. And since this is going to be a process that is automated and updating every 2 minutes we kind of did not want to enter the actual date in the code. We were hoping to use a function similar to $DATE$ to capture the current date, we currently have that function in a macro that spits the data to a csv like you mentioned. The only problem is that some of the macros will not have data in the early morning so that is why we are shooting for a SQL query. Remedy gives you an error when no data is found and since there is no way to not display errors like that it stops the rest of the Remedy macros from running behind it. I attached the macro that we are using. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Eli Schilling Sent: Friday, December 14, 2007 7:21 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy SQL query assistance... Please Help. ** Raul, are you running this query from within MS Excel? Crystal Reports? Also, If you're using an ARSystem ODBC driver the date conversion formula I gave you isn't necessary. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 12:35 PM To: arslist@ARSLIST.ORG Subject: FW: Remedy SQL query assistance... Please Help. ** From: Zaldivar, Jack (ISD, IT) Sent: Friday, December 14, 2007 3:33 PM To: Martinez, Raul (ISD, IT) Subject: RE: Remedy SQL query assistance... Please Help. SELECT HPD:HelpDesk.Assigned To Group+, HPD:HelpDesk.Assigned To Individual+, HPD:HelpDesk.Assignee Login Name, HPD:HelpDesk.Case ID+, HPD:HelpDesk.Source, HPD:HelpDesk.Status, HPD:HelpDesk.Submitted By FROM HPD:HelpDesk HPD:HelpDesk WHERE (dateadd(ss, (convert(int, Create Date))+(3600*-8), '01-01-1970 00:00:00') = '12/13/2007' AND dateadd(ss, (convert(int, Create Date))+(3600*-8), '01-01-1970 00:00:00') '12/14/2007') AND (HPD:HelpDesk.Status'Resolved' And HPD:HelpDesk.Status'Closed') ORDER BY HPD:HelpDesk.Case ID+ * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are image001.jpg
Remedy SQL query assistance... Please Help.
Can anyone assist me in either in connecting an ODBC connection to Remedy through Access or with creating a SQL search query for $DATE$ function? I am needing to grab all submitted tickets for current day based on certain submitters. I already have a remedy macro created but it would be easier with a SQL query. I had to split the macro up several times due to the server being limited to only 1000 line return. * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are AssignedToServDeskCombined.arq Description: AssignedToServDeskCombined.arq
Re: Remedy SQL query assistance... Please Help.
I think he mentioned that the SERVER has been limited to 1000. On Dec 14, 2007 11:03 AM, Evans.Randy [EMAIL PROTECTED] wrote: ** Change your preferences to unlimited while you run the macro. You probably don't want to go against the database itself. Remedy has a tendency to store dates as long integers based on the number of seconds since 1/1/1970. -- *From:* Action Request System discussion list(ARSList) [mailto: [EMAIL PROTECTED] *On Behalf Of *Martinez, Raul (ISD, IT) *Sent:* Friday, December 14, 2007 9:47 AM *To:* arslist@ARSLIST.ORG *Subject:* Remedy SQL query assistance... Please Help. ** Can anyone assist me in either in connecting an ODBC connection to Remedy through Access or with creating a SQL search query for $DATE$ function? I am needing to grab all submitted tickets for current day based on certain submitters. I already have a remedy macro created but it would be easier with a SQL query. I had to split the macro up several times due to the server being limited to only 1000 line return. * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Remedy SQL query assistance... Please Help.
Change your preferences to unlimited while you run the macro. You probably don't want to go against the database itself. Remedy has a tendency to store dates as long integers based on the number of seconds since 1/1/1970. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 9:47 AM To: arslist@ARSLIST.ORG Subject: Remedy SQL query assistance... Please Help. ** Can anyone assist me in either in connecting an ODBC connection to Remedy through Access or with creating a SQL search query for $DATE$ function? I am needing to grab all submitted tickets for current day based on certain submitters. I already have a remedy macro created but it would be easier with a SQL query. I had to split the macro up several times due to the server being limited to only 1000 line return. * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Remedy SQL query assistance... Please Help.
What database are you using? On Dec 14, 2007 11:33 AM, Martinez, Raul (ISD, IT) [EMAIL PROTECTED] wrote: ** Yes there is a limitation in affect right now on the server. Here is an example of what we are doing, but of course the code is not 100% correct: said syntax error *?* SELECT HPD:HelpDesk.Assigned To Group+, HPD:HelpDesk.Assigned To Individual+, HPD:HelpDesk.Assignee Login Name, HPD:HelpDesk.Case ID+, HPD:HelpDesk.Source, HPD:HelpDesk.Status, HPD:HelpDesk.Submitted By FROM HPD:HelpDesk HPD:HelpDesk WHERE (HPD:HelpDesk.Assignee Login Name='Jack Zaldivar') AND (HPD:HelpDesk.Status'Resolved' And HPD:HelpDesk.Status'Closed') AND (HPD:HelpDesk.Create TimeSYSDATE) ORDER BY HPD:HelpDesk.Case ID+ 11:11:11 AM *?* that's just an example 11:11:23 AM *?* not what we REALLY want 11:11:30 AM *From:* Action Request System discussion list(ARSList) [mailto: [EMAIL PROTECTED] *On Behalf Of *Frank Caruso *Sent:* Friday, December 14, 2007 11:08 AM *To:* arslist@ARSLIST.ORG *Subject:* Re: Remedy SQL query assistance... Please Help. ** I think he mentioned that the SERVER has been limited to 1000. On Dec 14, 2007 11:03 AM, Evans.Randy [EMAIL PROTECTED] wrote: ** Change your preferences to unlimited while you run the macro. You probably don't want to go against the database itself. Remedy has a tendency to store dates as long integers based on the number of seconds since 1/1/1970. -- *From:* Action Request System discussion list(ARSList) [mailto: [EMAIL PROTECTED] *On Behalf Of *Martinez, Raul (ISD, IT) *Sent:* Friday, December 14, 2007 9:47 AM *To:* arslist@ARSLIST.ORG *Subject:* Remedy SQL query assistance... Please Help. ** Can anyone assist me in either in connecting an ODBC connection to Remedy through Access or with creating a SQL search query for $DATE$ function? I am needing to grab all submitted tickets for current day based on certain submitters. I already have a remedy macro created but it would be easier with a SQL query. I had to split the macro up several times due to the server being limited to only 1000 line return. * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Remedy SQL query assistance... Please Help.
Yes there is a limitation in affect right now on the server. Here is an example of what we are doing, but of course the code is not 100% correct: said syntax error ? SELECT HPD:HelpDesk.Assigned To Group+, HPD:HelpDesk.Assigned To Individual+, HPD:HelpDesk.Assignee Login Name, HPD:HelpDesk.Case ID+, HPD:HelpDesk.Source, HPD:HelpDesk.Status, HPD:HelpDesk.Submitted By FROM HPD:HelpDesk HPD:HelpDesk WHERE (HPD:HelpDesk.Assignee Login Name='Jack Zaldivar') AND (HPD:HelpDesk.Status'Resolved' And HPD:HelpDesk.Status'Closed') AND (HPD:HelpDesk.Create TimeSYSDATE) ORDER BY HPD:HelpDesk.Case ID+ 11:11:11 AM ? that's just an example 11:11:23 AM ? not what we REALLY want 11:11:30 AM From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Frank Caruso Sent: Friday, December 14, 2007 11:08 AM To: arslist@ARSLIST.ORG Subject: Re: Remedy SQL query assistance... Please Help. ** I think he mentioned that the SERVER has been limited to 1000. On Dec 14, 2007 11:03 AM, Evans.Randy [EMAIL PROTECTED] wrote: ** Change your preferences to unlimited while you run the macro. You probably don't want to go against the database itself. Remedy has a tendency to store dates as long integers based on the number of seconds since 1/1/1970. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 9:47 AM To: arslist@ARSLIST.ORG Subject: Remedy SQL query assistance... Please Help. ** Can anyone assist me in either in connecting an ODBC connection to Remedy through Access or with creating a SQL search query for $DATE$ function? I am needing to grab all submitted tickets for current day based on certain submitters. I already have a remedy macro created but it would be easier with a SQL query. I had to split the macro up several times due to the server being limited to only 1000 line return. * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Remedy SQL query assistance... Please Help.
Not sure what you mean by the Database but we are trying this search in excel via an ODBC connection. We also have tried to connect straight through Access but are unable to due to the importing of all the tables instead of allowing us to specify particular tables. If anyone knows how to do this in Access either that would be helpful. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Frank Caruso Sent: Friday, December 14, 2007 11:50 AM To: arslist@ARSLIST.ORG Subject: Re: Remedy SQL query assistance... Please Help. ** What database are you using? On Dec 14, 2007 11:33 AM, Martinez, Raul (ISD, IT) [EMAIL PROTECTED] wrote: ** Yes there is a limitation in affect right now on the server. Here is an example of what we are doing, but of course the code is not 100% correct: said syntax error ? SELECT HPD:HelpDesk.Assigned To Group+, HPD:HelpDesk.Assigned To Individual+, HPD:HelpDesk.Assignee Login Name, HPD:HelpDesk.Case ID+, HPD:HelpDesk.Source, HPD:HelpDesk.Status, HPD:HelpDesk.Submitted By FROM HPD:HelpDesk HPD:HelpDesk WHERE (HPD:HelpDesk.Assignee Login Name='Jack Zaldivar') AND (HPD:HelpDesk.Status'Resolved' And HPD:HelpDesk.Status'Closed') AND (HPD:HelpDesk.Create TimeSYSDATE) ORDER BY HPD:HelpDesk.Case ID+ 11:11:11 AM ? that's just an example 11:11:23 AM ? not what we REALLY want 11:11:30 AM From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Frank Caruso Sent: Friday, December 14, 2007 11:08 AM To: arslist@ARSLIST.ORG Subject: Re: Remedy SQL query assistance... Please Help. ** I think he mentioned that the SERVER has been limited to 1000. On Dec 14, 2007 11:03 AM, Evans.Randy [EMAIL PROTECTED] wrote: ** Change your preferences to unlimited while you run the macro. You probably don't want to go against the database itself. Remedy has a tendency to store dates as long integers based on the number of seconds since 1/1/1970. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 9:47 AM To: arslist@ARSLIST.ORG Subject: Remedy SQL query assistance... Please Help. ** Can anyone assist me in either in connecting an ODBC connection to Remedy through Access or with creating a SQL search query for $DATE$ function? I am needing to grab all submitted tickets for current day based on certain submitters. I already have a remedy macro created but it would be easier with a SQL query. I had to split the macro up several times due to the server being limited to only 1000 line return. * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Remedy SQL query assistance... Please Help.
I'm not familiar with the stock Help Desk app, but this looks odd to
me... What are the enum values for the Status, and if you are excluding
Resolved and Closed via , I just don't like that. If resolved and
closed are right next together and they have an enum value of 3 and 4,
then ('Status' 3 and 'Status' 4). If they are not next together
then you could say something like ('Status' = 0 OR 'Status' =1 OR
'Status' = 3). Break up the dates like ('Create Date' = 12/01/2007
AND 'Create Date 12/14/2007), dates are from midnight unless
specified, so be inclusive on the lower date and exclusive on the latest
date. If you are macroing it, set those puppies up as variables and
dump them as csv's then merge the spreadsheets. If you are trying to
catch over a 1000 tickets for one day then do something like: ('Create
Date' = 12/01/2007 AND 'Create Date 12/01/2007 12:00). I could
go on, but we probably need more details on this ODBC connection, is it
giving you fits outside the realm that I've addressed?
Darrell E Reading II
Contact Center Development
Wal-Mart
45739
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
##
##
##
##
##
##
##
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT)
Sent: Friday, December 14, 2007 10:34
To: arslist@ARSLIST.ORG
Subject: Re: Remedy SQL query assistance... Please Help.
**
Yes there is a limitation in affect right now on the server. Here is an
example of what we are doing, but of course the code is not 100%
correct:
said syntax error
?
SELECT HPD:HelpDesk.Assigned To Group+, HPD:HelpDesk.Assigned To
Individual+, HPD:HelpDesk.Assignee Login Name, HPD:HelpDesk.Case
ID+, HPD:HelpDesk.Source, HPD:HelpDesk.Status,
HPD:HelpDesk.Submitted By
FROM HPD:HelpDesk HPD:HelpDesk
WHERE (HPD:HelpDesk.Assignee Login Name='Jack Zaldivar') AND
(HPD:HelpDesk.Status'Resolved' And HPD:HelpDesk.Status'Closed')
AND (HPD:HelpDesk.Create TimeSYSDATE)
ORDER BY HPD:HelpDesk.Case ID+
11:11:11 AM
?
that's just an example
11:11:23 AM
?
not what we REALLY want
11:11:30 AM
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Frank Caruso
Sent: Friday, December 14, 2007 11:08 AM
To: arslist@ARSLIST.ORG
Subject: Re: Remedy SQL query assistance... Please Help.
** I think he mentioned that the SERVER has been limited to 1000.
On Dec 14, 2007 11:03 AM, Evans.Randy [EMAIL PROTECTED]
wrote:
**
Change your preferences to unlimited while you run the macro. You
probably don't want to go against the database itself. Remedy has a
tendency to store dates as long integers based on the number of seconds
since 1/1/1970.
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT)
Sent: Friday, December 14, 2007 9:47 AM
To: arslist@ARSLIST.ORG
Subject: Remedy SQL query assistance... Please Help.
**
Can anyone assist me in either in connecting an ODBC connection to
Remedy through Access or with creating a SQL search query for $DATE$
function? I am needing to grab all submitted tickets for current day
based on certain submitters. I already have a remedy macro created but
it would be easier with a SQL query. I had to split the macro up several
times due to the server being limited to only 1000 line return.
*
This communication, including attachments, is
for the exclusive use of addressee and may contain proprietary,
confidential and/or privileged information. If you are not the intended
recipient, any use, copying, disclosure, dissemination or distribution
is
strictly prohibited. If you are not the intended recipient, please
notify
the sender immediately by return e-mail, delete this communication and
destroy all copies.
*
__20060125___This posting was submitted with HTML in
it___
__20060125___This posting was submitted with HTML in
it___
__20060125___This posting was submitted with HTML in
it___
__20060125___This posting was submitted with HTML in
it___
-
**
This email and any files transmitted with it are confidential and
intended solely for the individual or entity to whom they are
addressed. If you have received this email in error destroy it
immediately.
**
Wal-Mart Confidential
Re: Remedy SQL query assistance... Please Help.
Raul, If you want to do a direct SQL query and work with date ranges you have to convert the date field in the database. To do so in MS SQL try: dateadd(ss, (convert(int, Submit_Date))+(3600*-8), '01-01-1970 00:00:00') So a simple query might look like: Select Incident_Number, Description, Priority_text = Case WHEN Priority = 3 THEN 'Low' WHEN Priority = 2 THEN 'Medium' When PRIORITY = 1 THEN 'High' WHEN Priority = 0 THEN 'Critical' END, submitter from HPD_Help_Desk Where dateadd(ss, (convert(int, Submit_Date))+(3600*-8), '01-01-1970 00:00:00') = '12/13/2007' AND dateadd(ss, (convert(int, Submit_Date))+(3600*-8), '01-01-1970 00:00:00') '12/14/2007' AND (Submitter = 'Bob.Backline' OR Submitter = 'Sally.Supporter' OR Submitter = 'Francy.Frontline') Group by Submitter Is that what you were hoping for? Cheers! Eli From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 7:47 AM To: arslist@ARSLIST.ORG Subject: Remedy SQL query assistance... Please Help. ** Can anyone assist me in either in connecting an ODBC connection to Remedy through Access or with creating a SQL search query for $DATE$ function? I am needing to grab all submitted tickets for current day based on certain submitters. I already have a remedy macro created but it would be easier with a SQL query. I had to split the macro up several times due to the server being limited to only 1000 line return. * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
FW: Remedy SQL query assistance... Please Help.
From: Zaldivar, Jack (ISD, IT) Sent: Friday, December 14, 2007 3:33 PM To: Martinez, Raul (ISD, IT) Subject: RE: Remedy SQL query assistance... Please Help. SELECT HPD:HelpDesk.Assigned To Group+, HPD:HelpDesk.Assigned To Individual+, HPD:HelpDesk.Assignee Login Name, HPD:HelpDesk.Case ID+, HPD:HelpDesk.Source, HPD:HelpDesk.Status, HPD:HelpDesk.Submitted By FROM HPD:HelpDesk HPD:HelpDesk WHERE (dateadd(ss, (convert(int, Create Date))+(3600*-8), '01-01-1970 00:00:00') = '12/13/2007' AND dateadd(ss, (convert(int, Create Date))+(3600*-8), '01-01-1970 00:00:00') '12/14/2007') AND (HPD:HelpDesk.Status'Resolved' And HPD:HelpDesk.Status'Closed') ORDER BY HPD:HelpDesk.Case ID+ From: Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 2:21 PM To: Zaldivar, Jack (ISD, IT) Subject: FW: Remedy SQL query assistance... Please Help. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Eli Schilling Sent: Friday, December 14, 2007 3:13 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy SQL query assistance... Please Help. ** Raul, If you want to do a direct SQL query and work with date ranges you have to convert the date field in the database. To do so in MS SQL try: dateadd(ss, (convert(int, Submit_Date))+(3600*-8), '01-01-1970 00:00:00') So a simple query might look like: Select Incident_Number, Description, Priority_text = Case WHEN Priority = 3 THEN 'Low' WHEN Priority = 2 THEN 'Medium' When PRIORITY = 1 THEN 'High' WHEN Priority = 0 THEN 'Critical' END, submitter from HPD_Help_Desk Where dateadd(ss, (convert(int, Submit_Date))+(3600*-8), '01-01-1970 00:00:00') = '12/13/2007' AND dateadd(ss, (convert(int, Submit_Date))+(3600*-8), '01-01-1970 00:00:00') '12/14/2007' AND (Submitter = 'Bob.Backline' OR Submitter = 'Sally.Supporter' OR Submitter = 'Francy.Frontline') Group by Submitter Is that what you were hoping for? Cheers! Eli From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 7:47 AM To: arslist@ARSLIST.ORG Subject: Remedy SQL query assistance... Please Help. ** Can anyone assist me in either in connecting an ODBC connection to Remedy through Access or with creating a SQL search query for $DATE$ function? I am needing to grab all submitted tickets for current day based on certain submitters. I already have a remedy macro created but it would be easier with a SQL query. I had to split the macro up several times due to the server being limited to only 1000 line return. * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are Outlook.jpg
Re: Remedy SQL query assistance... Please Help.
Raul, are you running this query from within MS Excel? Crystal Reports? Also, If you're using an ARSystem ODBC driver the date conversion formula I gave you isn't necessary. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 12:35 PM To: arslist@ARSLIST.ORG Subject: FW: Remedy SQL query assistance... Please Help. ** From: Zaldivar, Jack (ISD, IT) Sent: Friday, December 14, 2007 3:33 PM To: Martinez, Raul (ISD, IT) Subject: RE: Remedy SQL query assistance... Please Help. SELECT HPD:HelpDesk.Assigned To Group+, HPD:HelpDesk.Assigned To Individual+, HPD:HelpDesk.Assignee Login Name, HPD:HelpDesk.Case ID+, HPD:HelpDesk.Source, HPD:HelpDesk.Status, HPD:HelpDesk.Submitted By FROM HPD:HelpDesk HPD:HelpDesk WHERE (dateadd(ss, (convert(int, Create Date))+(3600*-8), '01-01-1970 00:00:00') = '12/13/2007' AND dateadd(ss, (convert(int, Create Date))+(3600*-8), '01-01-1970 00:00:00') '12/14/2007') AND (HPD:HelpDesk.Status'Resolved' And HPD:HelpDesk.Status'Closed') ORDER BY HPD:HelpDesk.Case ID+ From: Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 2:21 PM To: Zaldivar, Jack (ISD, IT) Subject: FW: Remedy SQL query assistance... Please Help. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Eli Schilling Sent: Friday, December 14, 2007 3:13 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy SQL query assistance... Please Help. ** Raul, If you want to do a direct SQL query and work with date ranges you have to convert the date field in the database. To do so in MS SQL try: dateadd(ss, (convert(int, Submit_Date))+(3600*-8), '01-01-1970 00:00:00') So a simple query might look like: Select Incident_Number, Description, Priority_text = Case WHEN Priority = 3 THEN 'Low' WHEN Priority = 2 THEN 'Medium' When PRIORITY = 1 THEN 'High' WHEN Priority = 0 THEN 'Critical' END, submitter from HPD_Help_Desk Where dateadd(ss, (convert(int, Submit_Date))+(3600*-8), '01-01-1970 00:00:00') = '12/13/2007' AND dateadd(ss, (convert(int, Submit_Date))+(3600*-8), '01-01-1970 00:00:00') '12/14/2007' AND (Submitter = 'Bob.Backline' OR Submitter = 'Sally.Supporter' OR Submitter = 'Francy.Frontline') Group by Submitter Is that what you were hoping for? Cheers! Eli From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 7:47 AM To: arslist@ARSLIST.ORG Subject: Remedy SQL query assistance... Please Help. ** Can anyone assist me in either in connecting an ODBC connection to Remedy through Access or with creating a SQL search query for $DATE$ function? I am needing to grab all submitted tickets for current day based on certain submitters. I already have a remedy macro created but it would be easier with a SQL query. I had to split the macro up several times due to the server being limited to only 1000 line return. * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are Outlook.jpg
Re: AR 6.3 How to Populate a Table with a SQL Query?
Just remember that you can not use the rownum to up update the remote table. -- Jarl On 9/8/07, Grooms, Frederick W [EMAIL PROTECTED] wrote: Since it is a view of an external DB do the following. Create a local view of the external DB. Inside the local view add Rownum as a column in the query from the external database (use LPAD(rownum,15,'0') to create a 15 character 0 padded columm). Have your Remedy View form use the local view as it's source and the Rownum column as the unique key. Personally I never create a Remedy view form across a dbLink. I always make a local db view. This way when I move a form from Dev to Test to Production the form does not change even if the dbLink has to change between environments. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 4:28 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Unfortunately, neither of these solutions creates a column that is a suitable key field. From the documentation: The database table must have a column (field) that enforces non-null and unique values. This column will act as the Request ID. If the administrator chooses a column that is non-unique or allows nulls, data corruption will probably occur. The Request ID field must be an integer field or a character field that is no less than 6 and no greater than 15 characters. Otherwise, the Key field list will be empty, and you will not be able to create the view form. My DBA and I are still trying to create a column that meets this requirement. -Paul -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Friday, September 07, 2007 12:14 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Add the Column ROWNUM to the view Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Nall, Roger Sent: Friday, September 07, 2007 2:11 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? If it truly a view then you should be able to add a column to the view that will act as Request ID. Make sure that you increment the column by 1 for each record. HTH, Roger A. Nall Manager, OSSNMS Remedy T-Mobile USA Desk: 813-348-2556 Cell: 973-652-6723 FAX: 813-348-2565 sf49fanv AIM IM RogerNall Yahoo IM -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 3:08 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Ah yes, view forms. (You can tell I'm new at this.) Unfortunately, while creating the view, none of my database fields qualify as an index (tripping up on the 6-15 char window). I'm trying to work around this in oracle (the table is actually a view of an external DB so I have no control of that varchar limit), any other methods of implementing are appreciated. Thanks! -Paul -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Opela, Gary L Contr OC-ALC/ITMA Sent: Friday, September 07, 2007 11:43 AM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? You cannot populate a table via direct SQL (unfortunately). You can, however, create a remedy view and point it to a database SQL view of what you need, then point the remedy table to the view you created. Now, to pass a dynamic variable in the table qualification field, check out EXTERNAL() function in the AR Admin basic book. Thanks, Gary Opela, Jr Sr. Remedy Developer Leader Communications, Inc. 405 736 3211 -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 12:16 PM To: arslist@ARSLIST.ORG Subject: AR 6.3 How to Populate a Table with a SQL Query? Hello, I have a 'Customer Data' page on my main form in which there is one read/write character field named 'Company Name+', and several read-only character fields. I have set up a menu that reads from an Oracle View SQL query our database of customers, attached it to the 'Customer Data' field. and set up a couple active links so that when a search term is entered into Customer Data, or a Customer is chosen from the menu, the read-only fields are filled in with the appropriate SQL result columns. Now, my issue is that I would also like to have a table on the page that I can populate with the customer's contact list. In order to do this, I must make another SQL query, using the 'customer_id' from the original 'company_name
AR 6.3 How to Populate a Table with a SQL Query?
Hello, I have a 'Customer Data' page on my main form in which there is one read/write character field named 'Company Name+', and several read-only character fields. I have set up a menu that reads from an Oracle View SQL query our database of customers, attached it to the 'Customer Data' field. and set up a couple active links so that when a search term is entered into Customer Data, or a Customer is chosen from the menu, the read-only fields are filled in with the appropriate SQL result columns. Now, my issue is that I would also like to have a table on the page that I can populate with the customer's contact list. In order to do this, I must make another SQL query, using the 'customer_id' from the original 'company_name' query to key which rows to pull out. Any ideas on workflow that could pass the customer_id variable to a new SQL query, and then populate a table with several rows of data? I can't seem to work out a solution using menus/forms/active links. Thank you! ___ Paul Blasquez [EMAIL PROTECTED] ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: AR 6.3 How to Populate a Table with a SQL Query?
You cannot populate a table via direct SQL (unfortunately). You can, however, create a remedy view and point it to a database SQL view of what you need, then point the remedy table to the view you created. Now, to pass a dynamic variable in the table qualification field, check out EXTERNAL() function in the AR Admin basic book. Thanks, Gary Opela, Jr Sr. Remedy Developer Leader Communications, Inc. 405 736 3211 -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 12:16 PM To: arslist@ARSLIST.ORG Subject: AR 6.3 How to Populate a Table with a SQL Query? Hello, I have a 'Customer Data' page on my main form in which there is one read/write character field named 'Company Name+', and several read-only character fields. I have set up a menu that reads from an Oracle View SQL query our database of customers, attached it to the 'Customer Data' field. and set up a couple active links so that when a search term is entered into Customer Data, or a Customer is chosen from the menu, the read-only fields are filled in with the appropriate SQL result columns. Now, my issue is that I would also like to have a table on the page that I can populate with the customer's contact list. In order to do this, I must make another SQL query, using the 'customer_id' from the original 'company_name' query to key which rows to pull out. Any ideas on workflow that could pass the customer_id variable to a new SQL query, and then populate a table with several rows of data? I can't seem to work out a solution using menus/forms/active links. Thank you! ___ Paul Blasquez [EMAIL PROTECTED] ___ 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: AR 6.3 How to Populate a Table with a SQL Query?
Ah yes, view forms. (You can tell I'm new at this.) Unfortunately, while creating the view, none of my database fields qualify as an index (tripping up on the 6-15 char window). I'm trying to work around this in oracle (the table is actually a view of an external DB so I have no control of that varchar limit), any other methods of implementing are appreciated. Thanks! -Paul -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Opela, Gary L Contr OC-ALC/ITMA Sent: Friday, September 07, 2007 11:43 AM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? You cannot populate a table via direct SQL (unfortunately). You can, however, create a remedy view and point it to a database SQL view of what you need, then point the remedy table to the view you created. Now, to pass a dynamic variable in the table qualification field, check out EXTERNAL() function in the AR Admin basic book. Thanks, Gary Opela, Jr Sr. Remedy Developer Leader Communications, Inc. 405 736 3211 -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 12:16 PM To: arslist@ARSLIST.ORG Subject: AR 6.3 How to Populate a Table with a SQL Query? Hello, I have a 'Customer Data' page on my main form in which there is one read/write character field named 'Company Name+', and several read-only character fields. I have set up a menu that reads from an Oracle View SQL query our database of customers, attached it to the 'Customer Data' field. and set up a couple active links so that when a search term is entered into Customer Data, or a Customer is chosen from the menu, the read-only fields are filled in with the appropriate SQL result columns. Now, my issue is that I would also like to have a table on the page that I can populate with the customer's contact list. In order to do this, I must make another SQL query, using the 'customer_id' from the original 'company_name' query to key which rows to pull out. Any ideas on workflow that could pass the customer_id variable to a new SQL query, and then populate a table with several rows of data? I can't seem to work out a solution using menus/forms/active links. Thank you! ___ Paul Blasquez [EMAIL PROTECTED] ___ 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: AR 6.3 How to Populate a Table with a SQL Query?
If it truly a view then you should be able to add a column to the view that will act as Request ID. Make sure that you increment the column by 1 for each record. HTH, Roger A. Nall Manager, OSSNMS Remedy T-Mobile USA Desk: 813-348-2556 Cell: 973-652-6723 FAX: 813-348-2565 sf49fanv AIM IM RogerNall Yahoo IM -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 3:08 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Ah yes, view forms. (You can tell I'm new at this.) Unfortunately, while creating the view, none of my database fields qualify as an index (tripping up on the 6-15 char window). I'm trying to work around this in oracle (the table is actually a view of an external DB so I have no control of that varchar limit), any other methods of implementing are appreciated. Thanks! -Paul -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Opela, Gary L Contr OC-ALC/ITMA Sent: Friday, September 07, 2007 11:43 AM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? You cannot populate a table via direct SQL (unfortunately). You can, however, create a remedy view and point it to a database SQL view of what you need, then point the remedy table to the view you created. Now, to pass a dynamic variable in the table qualification field, check out EXTERNAL() function in the AR Admin basic book. Thanks, Gary Opela, Jr Sr. Remedy Developer Leader Communications, Inc. 405 736 3211 -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 12:16 PM To: arslist@ARSLIST.ORG Subject: AR 6.3 How to Populate a Table with a SQL Query? Hello, I have a 'Customer Data' page on my main form in which there is one read/write character field named 'Company Name+', and several read-only character fields. I have set up a menu that reads from an Oracle View SQL query our database of customers, attached it to the 'Customer Data' field. and set up a couple active links so that when a search term is entered into Customer Data, or a Customer is chosen from the menu, the read-only fields are filled in with the appropriate SQL result columns. Now, my issue is that I would also like to have a table on the page that I can populate with the customer's contact list. In order to do this, I must make another SQL query, using the 'customer_id' from the original 'company_name' query to key which rows to pull out. Any ideas on workflow that could pass the customer_id variable to a new SQL query, and then populate a table with several rows of data? I can't seem to work out a solution using menus/forms/active links. Thank you! ___ Paul Blasquez [EMAIL PROTECTED] ___ 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 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: AR 6.3 How to Populate a Table with a SQL Query?
Add the Column ROWNUM to the view Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Nall, Roger Sent: Friday, September 07, 2007 2:11 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? If it truly a view then you should be able to add a column to the view that will act as Request ID. Make sure that you increment the column by 1 for each record. HTH, Roger A. Nall Manager, OSSNMS Remedy T-Mobile USA Desk: 813-348-2556 Cell: 973-652-6723 FAX: 813-348-2565 sf49fanv AIM IM RogerNall Yahoo IM -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 3:08 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Ah yes, view forms. (You can tell I'm new at this.) Unfortunately, while creating the view, none of my database fields qualify as an index (tripping up on the 6-15 char window). I'm trying to work around this in oracle (the table is actually a view of an external DB so I have no control of that varchar limit), any other methods of implementing are appreciated. Thanks! -Paul -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Opela, Gary L Contr OC-ALC/ITMA Sent: Friday, September 07, 2007 11:43 AM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? You cannot populate a table via direct SQL (unfortunately). You can, however, create a remedy view and point it to a database SQL view of what you need, then point the remedy table to the view you created. Now, to pass a dynamic variable in the table qualification field, check out EXTERNAL() function in the AR Admin basic book. Thanks, Gary Opela, Jr Sr. Remedy Developer Leader Communications, Inc. 405 736 3211 -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 12:16 PM To: arslist@ARSLIST.ORG Subject: AR 6.3 How to Populate a Table with a SQL Query? Hello, I have a 'Customer Data' page on my main form in which there is one read/write character field named 'Company Name+', and several read-only character fields. I have set up a menu that reads from an Oracle View SQL query our database of customers, attached it to the 'Customer Data' field. and set up a couple active links so that when a search term is entered into Customer Data, or a Customer is chosen from the menu, the read-only fields are filled in with the appropriate SQL result columns. Now, my issue is that I would also like to have a table on the page that I can populate with the customer's contact list. In order to do this, I must make another SQL query, using the 'customer_id' from the original 'company_name' query to key which rows to pull out. Any ideas on workflow that could pass the customer_id variable to a new SQL query, and then populate a table with several rows of data? I can't seem to work out a solution using menus/forms/active links. Thank you! ___ Paul Blasquez [EMAIL PROTECTED] ___ 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 ___ 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: AR 6.3 How to Populate a Table with a SQL Query?
Unfortunately, neither of these solutions creates a column that is a suitable key field. From the documentation: The database table must have a column (field) that enforces non-null and unique values. This column will act as the Request ID. If the administrator chooses a column that is non-unique or allows nulls, data corruption will probably occur. The Request ID field must be an integer field or a character field that is no less than 6 and no greater than 15 characters. Otherwise, the Key field list will be empty, and you will not be able to create the view form. My DBA and I are still trying to create a column that meets this requirement. -Paul -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Friday, September 07, 2007 12:14 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Add the Column ROWNUM to the view Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Nall, Roger Sent: Friday, September 07, 2007 2:11 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? If it truly a view then you should be able to add a column to the view that will act as Request ID. Make sure that you increment the column by 1 for each record. HTH, Roger A. Nall Manager, OSSNMS Remedy T-Mobile USA Desk: 813-348-2556 Cell: 973-652-6723 FAX: 813-348-2565 sf49fanv AIM IM RogerNall Yahoo IM -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 3:08 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Ah yes, view forms. (You can tell I'm new at this.) Unfortunately, while creating the view, none of my database fields qualify as an index (tripping up on the 6-15 char window). I'm trying to work around this in oracle (the table is actually a view of an external DB so I have no control of that varchar limit), any other methods of implementing are appreciated. Thanks! -Paul -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Opela, Gary L Contr OC-ALC/ITMA Sent: Friday, September 07, 2007 11:43 AM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? You cannot populate a table via direct SQL (unfortunately). You can, however, create a remedy view and point it to a database SQL view of what you need, then point the remedy table to the view you created. Now, to pass a dynamic variable in the table qualification field, check out EXTERNAL() function in the AR Admin basic book. Thanks, Gary Opela, Jr Sr. Remedy Developer Leader Communications, Inc. 405 736 3211 -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 12:16 PM To: arslist@ARSLIST.ORG Subject: AR 6.3 How to Populate a Table with a SQL Query? Hello, I have a 'Customer Data' page on my main form in which there is one read/write character field named 'Company Name+', and several read-only character fields. I have set up a menu that reads from an Oracle View SQL query our database of customers, attached it to the 'Customer Data' field. and set up a couple active links so that when a search term is entered into Customer Data, or a Customer is chosen from the menu, the read-only fields are filled in with the appropriate SQL result columns. Now, my issue is that I would also like to have a table on the page that I can populate with the customer's contact list. In order to do this, I must make another SQL query, using the 'customer_id' from the original 'company_name' query to key which rows to pull out. Any ideas on workflow that could pass the customer_id variable to a new SQL query, and then populate a table with several rows of data? I can't seem to work out a solution using menus/forms/active links. Thank you! ___ Paul Blasquez [EMAIL PROTECTED] ___ 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 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are ___ UNSUBSCRIBE or access ARSlist
Re: AR 6.3 How to Populate a Table with a SQL Query?
I need to clarify something, sorry: I can type in which key field to use manually, and it will accept it, and my table is able to import SOME fields from the view form, but not others. Here are the fields in my view: Name Null?Type - CUSTOMER_NAME VARCHAR2(50) CUSTOMER_NUMBER NOT NULL VARCHAR2(30) CONTACT_NAME VARCHAR2(91) EMAIL VARCHAR2(2000) STATUS VARCHAR2(1) ROLE NOT NULL VARCHAR2(80) IBX NOT NULL VARCHAR2(20) I am using CUSTOMER_NUMBER as the key field, as it is unique. From my table, I am not able to select CONTACT_NAME or EMAIL; those do not even show up in my Fields on Form list in my table property tab. The only thing those two have in common is the VARCHAR size is 80. Is there a size limit on columns a view form can provide? -Paul -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 2:28 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Unfortunately, neither of these solutions creates a column that is a suitable key field. From the documentation: The database table must have a column (field) that enforces non-null and unique values. This column will act as the Request ID. If the administrator chooses a column that is non-unique or allows nulls, data corruption will probably occur. The Request ID field must be an integer field or a character field that is no less than 6 and no greater than 15 characters. Otherwise, the Key field list will be empty, and you will not be able to create the view form. My DBA and I are still trying to create a column that meets this requirement. -Paul -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Friday, September 07, 2007 12:14 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Add the Column ROWNUM to the view Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Nall, Roger Sent: Friday, September 07, 2007 2:11 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? If it truly a view then you should be able to add a column to the view that will act as Request ID. Make sure that you increment the column by 1 for each record. HTH, Roger A. Nall Manager, OSSNMS Remedy T-Mobile USA Desk: 813-348-2556 Cell: 973-652-6723 FAX: 813-348-2565 sf49fanv AIM IM RogerNall Yahoo IM -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 3:08 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Ah yes, view forms. (You can tell I'm new at this.) Unfortunately, while creating the view, none of my database fields qualify as an index (tripping up on the 6-15 char window). I'm trying to work around this in oracle (the table is actually a view of an external DB so I have no control of that varchar limit), any other methods of implementing are appreciated. Thanks! -Paul -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Opela, Gary L Contr OC-ALC/ITMA Sent: Friday, September 07, 2007 11:43 AM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? You cannot populate a table via direct SQL (unfortunately). You can, however, create a remedy view and point it to a database SQL view of what you need, then point the remedy table to the view you created. Now, to pass a dynamic variable in the table qualification field, check out EXTERNAL() function in the AR Admin basic book. Thanks, Gary Opela, Jr Sr. Remedy Developer Leader Communications, Inc. 405 736 3211 -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 12:16 PM To: arslist@ARSLIST.ORG Subject: AR 6.3 How to Populate a Table with a SQL Query? Hello, I have a 'Customer Data' page on my main form in which there is one read/write character field named 'Company Name+', and several read-only character fields. I have set up a menu that reads from an Oracle View SQL query our database of customers, attached it to the 'Customer Data' field. and set up a couple active links so that when a search term is entered into Customer Data, or a Customer is chosen from
Re: AR 6.3 How to Populate a Table with a SQL Query?
Since it is a view of an external DB do the following. Create a local view of the external DB. Inside the local view add Rownum as a column in the query from the external database (use LPAD(rownum,15,'0') to create a 15 character 0 padded columm). Have your Remedy View form use the local view as it's source and the Rownum column as the unique key. Personally I never create a Remedy view form across a dbLink. I always make a local db view. This way when I move a form from Dev to Test to Production the form does not change even if the dbLink has to change between environments. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 4:28 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Unfortunately, neither of these solutions creates a column that is a suitable key field. From the documentation: The database table must have a column (field) that enforces non-null and unique values. This column will act as the Request ID. If the administrator chooses a column that is non-unique or allows nulls, data corruption will probably occur. The Request ID field must be an integer field or a character field that is no less than 6 and no greater than 15 characters. Otherwise, the Key field list will be empty, and you will not be able to create the view form. My DBA and I are still trying to create a column that meets this requirement. -Paul -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Friday, September 07, 2007 12:14 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Add the Column ROWNUM to the view Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Nall, Roger Sent: Friday, September 07, 2007 2:11 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? If it truly a view then you should be able to add a column to the view that will act as Request ID. Make sure that you increment the column by 1 for each record. HTH, Roger A. Nall Manager, OSSNMS Remedy T-Mobile USA Desk: 813-348-2556 Cell: 973-652-6723 FAX: 813-348-2565 sf49fanv AIM IM RogerNall Yahoo IM -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 3:08 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Ah yes, view forms. (You can tell I'm new at this.) Unfortunately, while creating the view, none of my database fields qualify as an index (tripping up on the 6-15 char window). I'm trying to work around this in oracle (the table is actually a view of an external DB so I have no control of that varchar limit), any other methods of implementing are appreciated. Thanks! -Paul -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Opela, Gary L Contr OC-ALC/ITMA Sent: Friday, September 07, 2007 11:43 AM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? You cannot populate a table via direct SQL (unfortunately). You can, however, create a remedy view and point it to a database SQL view of what you need, then point the remedy table to the view you created. Now, to pass a dynamic variable in the table qualification field, check out EXTERNAL() function in the AR Admin basic book. Thanks, Gary Opela, Jr Sr. Remedy Developer Leader Communications, Inc. 405 736 3211 -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 12:16 PM To: arslist@ARSLIST.ORG Subject: AR 6.3 How to Populate a Table with a SQL Query? Hello, I have a 'Customer Data' page on my main form in which there is one read/write character field named 'Company Name+', and several read-only character fields. I have set up a menu that reads from an Oracle View SQL query our database of customers, attached it to the 'Customer Data' field. and set up a couple active links so that when a search term is entered into Customer Data, or a Customer is chosen from the menu, the read-only fields are filled in with the appropriate SQL result columns. Now, my issue is that I would also like to have a table on the page that I can populate with the customer's contact list. In order to do this, I must make another SQL query, using the 'customer_id' from the original 'company_name' query to key which rows to pull out. Any ideas on workflow that could pass the customer_id variable to a new SQL query, and then populate a table with several rows of data? I can't seem to work out a solution using menus/forms/active links
Re: Newbie help with creating a SQL menu..Making the SQL query context sensitive
Thank you. Will try that. One more question I have is: How do I make this menu context sensitive. In other words. I have a field called Category with values Data and Security. If the value is Data, I want to run a particular SQL query (select distinct(LOCATION) from REMEDY.LOCATIONS). If the Value is Security, I want to run a different SQL query (from a different table - select distinct(HOSTNAME) from REMEDY.HOSTS). I have reviewed the example in training manual where it talks about building a context sensitive menu assuming the target data is in another form. Will I have create a form and join data from both the tables? Thanks Ray Axton wrote: If you want to access a table in the same oracle instance, but in a different schema, use this syntax for the table name: schema.tableName If you want to access data in a remote schema, (1) create a database link in Oracle, then (2), use this syntax for the table name: remoteSchema.tableName@DbLinkName Axton Grams On 7/29/07, Ray [EMAIL PROTECTED] wrote: Axton, Dave: I changed the Server name to the IP address of the remedy server instead of the database server and it works. Any idea if I can connect to a database table that is not the remedy database? If yes, how would I do that? Thanks Ray Shellman, David wrote: ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Newbie help with creating a SQL menu..Making the SQL query context sensitive
Couple of suggestions: 1. Use two Menus. One for server and for data. When user selects from category you could change the menu with an Active Link Change action; you might also have to do this on Display of each record. 2. You could have the table name and column be dynamic based on the Category field. If the user chooses Data then set a temp field to REMEDY.LOCATIONSand another temp field to LOCATION; if Server then set a temp field to REMEDY.HOSTS and another temp field to HOSTNAME. Use the temp fields in the SQL statement: select distinct($temp_column$) from $temp_table$. On 7/30/07, Ray [EMAIL PROTECTED] wrote: Thank you. Will try that. One more question I have is: How do I make this menu context sensitive. In other words. I have a field called Category with values Data and Security. If the value is Data, I want to run a particular SQL query (select distinct(LOCATION) from REMEDY.LOCATIONS). If the Value is Security, I want to run a different SQL query (from a different table - select distinct(HOSTNAME) from REMEDY.HOSTS). I have reviewed the example in training manual where it talks about building a context sensitive menu assuming the target data is in another form. Will I have create a form and join data from both the tables? Thanks Ray Axton wrote: If you want to access a table in the same oracle instance, but in a different schema, use this syntax for the table name: schema.tableName If you want to access data in a remote schema, (1) create a database link in Oracle, then (2), use this syntax for the table name: remoteSchema.tableName@DbLinkName Axton Grams On 7/29/07, Ray [EMAIL PROTECTED] wrote: Axton, Dave: I changed the Server name to the IP address of the remedy server instead of the database server and it works. Any idea if I can connect to a database table that is not the remedy database? If yes, how would I do that? Thanks Ray Shellman, David wrote: ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are -- Frank Caruso Specific Integration, Inc. Senior Remedy Engineer, ITIL Foundation Certified www.specificintegration.com 703-376-1249 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Newbie help with creating a SQL menu..Making the SQL query context sensitive
Here is one idea, you could create two menus and change them in an active link depending on your Category choice. Another idea but I am not sure it will work is this: * Create table to data such as URL's, stored procedures. SQL statements, etc. * Each record should have a different Label or ID. * Create a display only field on your form that has your Categroy field on. * You will only need one menu and multiple active links. * In your menu you reference your display only field to pull the data. HTH, Roger A. Nall Manager, OSSNMS Remedy T-Mobile USA Desk: 813-348-2556(New) Cell: 973-652-6723 FAX: 813-348-2565 sf49fanv AIM IM RogerNall Yahoo IM -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ray Sent: Monday, July 30, 2007 7:58 AM To: arslist@ARSLIST.ORG Subject: Re: Newbie help with creating a SQL menu..Making the SQL query context sensitive Thank you. Will try that. One more question I have is: How do I make this menu context sensitive. In other words. I have a field called Category with values Data and Security. If the value is Data, I want to run a particular SQL query (select distinct(LOCATION) from REMEDY.LOCATIONS). If the Value is Security, I want to run a different SQL query (from a different table - select distinct(HOSTNAME) from REMEDY.HOSTS). I have reviewed the example in training manual where it talks about building a context sensitive menu assuming the target data is in another form. Will I have create a form and join data from both the tables? Thanks Ray Axton wrote: If you want to access a table in the same oracle instance, but in a different schema, use this syntax for the table name: schema.tableName If you want to access data in a remote schema, (1) create a database link in Oracle, then (2), use this syntax for the table name: remoteSchema.tableName@DbLinkName Axton Grams On 7/29/07, Ray [EMAIL PROTECTED] wrote: Axton, Dave: I changed the Server name to the IP address of the remedy server instead of the database server and it works. Any idea if I can connect to a database table that is not the remedy database? If yes, how would I do that? Thanks Ray Shellman, David wrote: ___ 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
Dates in a SQL Query
I have a simple count SQL query that sets a field on my form: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 I would also like to query it more by adding date fields. I have a start and end date on my form, but how do I translate this into at SQL Query? I know if I simply do this: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 and EXP_DATE = '$Start Date$' and EXP_DATE $End Date$' I'm not going to get what I need. My EXP_DATE field is truly just a date field, not Date/Time We are using AR System 6.3 AR User 6.0 Oracle 9i and Sun Solaris. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Dates in a SQL Query
Roger, The 2 are not the same.. Date/Time fields are fields where the date/time is the number of seconds since 12:00:00 AM on 1st Jan 1970 and is called Epoch Time. See http://en.wikipedia.org/wiki/Unix_time for more details. Date fields on the other hand however are the number of days since the noon of 1st Jan 4713 BC and is referred to as Julian Date. See http://en.wikipedia.org/wiki/Julian_day for more details. Lisa, I'm not too certain but I think I saw references of how to use this in SQL statement to convert the integer into appropriate dates in one of the Admin guides.. Joe -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Roger Justice Sent: Monday, June 11, 2007 2:39 PM To: arslist@ARSLIST.ORG Subject: Re: Dates in a SQL Query ** All date and date/time fields are stored in seconds therefore you will have to calculate it. -Original Message- From: Kemes, Lisa To: arslist@ARSLIST.ORG Sent: Mon, 11 Jun 2007 2:35 pm Subject: Dates in a SQL Query ** I have a simple count SQL query that sets a field on my form: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 I would also like to query it more by adding date fields. I have a start and end date on my form, but how do I translate this into at SQL Query? I know if I simply do this: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 and EXP_DATE = '$Start Date$' and EXP_DATE $End Date$' I'm not going to get what I need. My EXP_DATE field is truly just a date field, not Date/Time We are using AR System 6.3 AR User 6.0 Oracle 9i and Sun Solaris. No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.13/843 - Release Date: 6/10/2007 1:39 PM ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Dates in a SQL Query
Joe, I just sent her the answer. As you noted Date/Time fields are in seconds and Date fields are in Days. Lisa only needs to add 1 like EXP_DATE ($End Date$' + 1) Dave -- [EMAIL PROTECTED] (Wireless) - Original Message - From: Action Request System discussion list(ARSList) arslist@ARSLIST.ORG To: arslist@ARSLIST.ORG arslist@ARSLIST.ORG Sent: Mon Jun 11 14:54:10 2007 Subject: Re: Dates in a SQL Query ** Roger, The 2 are not the same.. Date/Time fields are fields where the date/time is the number of seconds since 12:00:00 AM on 1st Jan 1970 and is called Epoch Time. See http://en.wikipedia.org/wiki/Unix_time for more details. Date fields on the other hand however are the number of days since the noon of 1st Jan 4713 BC and is referred to as Julian Date. See http://en.wikipedia.org/wiki/Julian_day for more details. Lisa, I'm not too certain but I think I saw references of how to use this in SQL statement to convert the integer into appropriate dates in one of the Admin guides.. Joe -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Roger Justice Sent: Monday, June 11, 2007 2:39 PM To: arslist@ARSLIST.ORG Subject: Re: Dates in a SQL Query ** All date and date/time fields are stored in seconds therefore you will have to calculate it. -Original Message- From: Kemes, Lisa To: arslist@ARSLIST.ORG Sent: Mon, 11 Jun 2007 2:35 pm Subject: Dates in a SQL Query ** I have a simple count SQL query that sets a field on my form: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 I would also like to query it more by adding date fields. I have a start and end date on my form, but how do I translate this into at SQL Query? I know if I simply do this: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 and EXP_DATE = '$Start Date$' and EXP_DATE $End Date$' I'm not going to get what I need. My EXP_DATE field is truly just a date field, not Date/Time We are using AR System 6.3 AR User 6.0 Oracle 9i and Sun Solaris. __20060125___This posting was submitted with HTML in it___
Re: Dates in a SQL Query
Create 2 Display Only Integer fields on your form (zInt1 and zInt2) Set zInt1 = $Start Date$ Set zInt2 = $End Date$ Then use the 2 Integer fields in your SQL Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kemes, Lisa Sent: Monday, June 11, 2007 1:35 PM To: arslist@ARSLIST.ORG Subject: Dates in a SQL Query ** I have a simple count SQL query that sets a field on my form: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 I would also like to query it more by adding date fields. I have a start and end date on my form, but how do I translate this into at SQL Query? I know if I simply do this: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 and EXP_DATE = '$Start Date$' and EXP_DATE $End Date$' I'm not going to get what I need. My EXP_DATE field is truly just a date field, not Date/Time We are using AR System 6.3 AR User 6.0 Oracle 9i and Sun Solaris. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Dates in a SQL Query
Thanks Fred! One more question, this is a display form, other than Lose Focus what other Execute On value can I use to show that one of the date fields have changed? Lisa From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Monday, June 11, 2007 4:01 PM To: arslist@ARSLIST.ORG Subject: Re: Dates in a SQL Query ** Create 2 Display Only Integer fields on your form (zInt1 and zInt2) Set zInt1 = $Start Date$ Set zInt2 = $End Date$ Then use the 2 Integer fields in your SQL Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kemes, Lisa Sent: Monday, June 11, 2007 1:35 PM To: arslist@ARSLIST.ORG Subject: Dates in a SQL Query ** I have a simple count SQL query that sets a field on my form: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 I would also like to query it more by adding date fields. I have a start and end date on my form, but how do I translate this into at SQL Query? I know if I simply do this: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 and EXP_DATE = '$Start Date$' and EXP_DATE $End Date$' I'm not going to get what I need. My EXP_DATE field is truly just a date field, not Date/Time We are using AR System 6.3 AR User 6.0 Oracle 9i and Sun Solaris. __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Dates in a SQL Query
Smallest interval (3 seconds)? -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Kemes, Lisa Sent: Monday, June 11, 2007 4:45 PM To: arslist@ARSLIST.ORG Subject: Re: Dates in a SQL Query ** Thanks Fred! One more question, this is a display form, other than Lose Focus what other Execute On value can I use to show that one of the date fields have changed? Lisa -- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Monday, June 11, 2007 4:01 PM To: arslist@ARSLIST.ORG Subject: Re: Dates in a SQL Query ** Create 2 Display Only Integer fields on your form (zInt1 and zInt2) Set zInt1 = $Start Date$ Set zInt2 = $End Date$ Then use the 2 Integer fields in your SQL Fred -- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kemes, Lisa Sent: Monday, June 11, 2007 1:35 PM To: arslist@ARSLIST.ORG Subject: Dates in a SQL Query ** I have a simple count SQL query that sets a field on my form: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 I would also like to query it more by adding date fields. I have a start and end date on my form, but how do I translate this into at SQL Query? I know if I simply do this: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 and EXP_DATE = '$Start Date$' and EXP_DATE $End Date$' I'm not going to get what I need. My EXP_DATE field is truly just a date field, not Date/Time We are using AR System 6.3 AR User 6.0 Oracle 9i and Sun Solaris. __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.13/843 - Release Date: 6/10/2007 1:39 PM ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Dates in a SQL Query
On DisplayOnly forms that generate reporting data I usually put a button called Generate on the form. That way I can separate the report parameters from the output. I know ... really old school ... but it works Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kemes, Lisa Sent: Monday, June 11, 2007 3:45 PM To: arslist@ARSLIST.ORG Subject: Re: Dates in a SQL Query ** Thanks Fred! One more question, this is a display form, other than Lose Focus what other Execute On value can I use to show that one of the date fields have changed? Lisa From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Monday, June 11, 2007 4:01 PM To: arslist@ARSLIST.ORG Subject: Re: Dates in a SQL Query ** Create 2 Display Only Integer fields on your form (zInt1 and zInt2) Set zInt1 = $Start Date$ Set zInt2 = $End Date$ Then use the 2 Integer fields in your SQL Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Kemes, Lisa Sent: Monday, June 11, 2007 1:35 PM To: arslist@ARSLIST.ORG Subject: Dates in a SQL Query ** I have a simple count SQL query that sets a field on my form: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 I would also like to query it more by adding date fields. I have a start and end date on my form, but how do I translate this into at SQL Query? I know if I simply do this: SELECT COUNT(SURVEY_NUMBER) from TEIS_SURVEY_CALCULATIONS Where AVGSCORE_7 0 and EXP_DATE = '$Start Date$' and EXP_DATE $End Date$' I'm not going to get what I need. My EXP_DATE field is truly just a date field, not Date/Time We are using AR System 6.3 AR User 6.0 Oracle 9i and Sun Solaris. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Converting Remedy Datetime for SQL Query
Fellow ARSList Members I need to convert the Epoch time format that Remedy stores its datetime data into a more readable format for an SQL query. According to an older KB article I have from Remedy, Remedy recommends using DATEADD(day, h.Cinsert field id-2440588, '01/01/1970') to format what they store as 1161641694 into 10/23/2006 6:14:54 PM. The article also claims I have to add h.Cinsert field id IS NOT NULL and h.Cinsert field id 2440590 to the where statement of the query. The following is the SQL query I am running based on the KB article. select h.C1 as Case ID, DATEADD(day, h.C3-2440588, '01/01/1970') as Arrival Time, from T87 h where h.C1 = 'HD367848' and h.C3 IS NOT NULL and h.C3 2440590 When I use this query, my Microsoft SQL Server 2000 returns Adding a value to a 'datetime' column caused overflow. Does anyone have the correct string to use in a SQL query to convert the returned date/time values into readable date/time format. I would appreciate any assistance you can give on this, and thank you for you time regarding this question. James Van Sickle ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Converting Remedy Datetime for SQL Query
This is what I have and I've never had a problem. dateadd(second, Create Date Field + 7200, '1 Jan 1970' Make sure that the number of seconds you add or subtract is correct according to your timezone. Regards, Basil Webster Remedy Developer Siemens Business Services (Pty) Ltd * E-Mail: [EMAIL PROTECTED] ( Tel: +27 11 652 7523 Ê Fax: +27 11 652-7501 ) Mobile: +27 82 452 9389 -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of James Van Sickle Sent: 25 October 2006 19:52 To: arslist@ARSLIST.ORG Subject: Converting Remedy Datetime for SQL Query Fellow ARSList Members I need to convert the Epoch time format that Remedy stores its datetime data into a more readable format for an SQL query. According to an older KB article I have from Remedy, Remedy recommends using DATEADD(day, h.Cinsert field id-2440588, '01/01/1970') to format what they store as 1161641694 into 10/23/2006 6:14:54 PM. The article also claims I have to add h.Cinsert field id IS NOT NULL and h.Cinsert field id 2440590 to the where statement of the query. The following is the SQL query I am running based on the KB article. select h.C1 as Case ID, DATEADD(day, h.C3-2440588, '01/01/1970') as Arrival Time, from T87 h where h.C1 = 'HD367848' and h.C3 IS NOT NULL and h.C3 2440590 When I use this query, my Microsoft SQL Server 2000 returns Adding a value to a 'datetime' column caused overflow. Does anyone have the correct string to use in a SQL query to convert the returned date/time values into readable date/time format. I would appreciate any assistance you can give on this, and thank you for you time regarding this question. James Van Sickle ___ 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: Converting Remedy Datetime for SQL Query
Hello, I don't think you can use them (Oracle 9i is required and I understand you're using MS SQL), but for the record here are a couple of functions I wrote recently to convert remedy timestamps to/from a more reasonable format. Regards, Carlos Ungil create function timestamp_to_date (remdate in number) return char is stuff char(20); begin select to_char((timestamp '1970-01-01 00:00:00 GMT'+ numtodsinterval(remdate, 'SECOND')) at time zone 'Europe/Zurich','dd/mm/ hh24:mi:ss') into stuff from dual; return(stuff); end timestamp_to_date; create function date_to_timestamp (txtdate in varchar2) return number is stuff number; begin select extract(second from (from_tz(to_timestamp(txtdate,'dd/mm/ hh24:mi:ss'), 'Europe/Zurich') - (timestamp '1970-01-01 00:00:00 GMT'))) +60*extract(minute from (from_tz(to_timestamp(txtdate,'dd/mm/ hh24:mi:ss'), 'Europe/Zurich') - (timestamp '1970-01-01 00:00:00 GMT'))) +60*60*extract(hour from (from_tz(to_timestamp(txtdate,'dd/mm/ hh24:mi:ss'), 'Europe/Zurich') - (timestamp '1970-01-01 00:00:00 GMT'))) +24*60*60*extract(day from (from_tz(to_timestamp(txtdate,'dd/mm/ hh24:mi:ss'), 'Europe/Zurich') - (timestamp '1970-01-01 00:00:00 GMT'))) into stuff from dual; return(stuff); end date_to_timestamp; On 10/25/06, James Van Sickle [EMAIL PROTECTED] wrote: Fellow ARSList Members I need to convert the Epoch time format that Remedy stores its datetime data into a more readable format for an SQL query. According to an older KB article I have from Remedy, Remedy recommends using DATEADD(day, h.Cinsert field id-2440588, '01/01/1970') to format what they store as 1161641694 into 10/23/2006 6:14:54 PM. The article also claims I have to add h.Cinsert field id IS NOT NULL and h.Cinsert field id 2440590 to the where statement of the query. The following is the SQL query I am running based on the KB article. select h.C1 as Case ID, DATEADD(day, h.C3-2440588, '01/01/1970') as Arrival Time, from T87 h where h.C1 = 'HD367848' and h.C3 IS NOT NULL and h.C3 2440590 When I use this query, my Microsoft SQL Server 2000 returns Adding a value to a 'datetime' column caused overflow. Does anyone have the correct string to use in a SQL query to convert the returned date/time values into readable date/time format. I would appreciate any assistance you can give on this, and thank you for you time regarding this question. James Van Sickle ___ 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
