Run SQL Server queries in scripts background

[Vimal Priya]

Hi All,

I have written some SQL queies but it is not working when i checked it with script background , I am getting some errors .

gs.sql("(select category from incident;)"):

[0:00:00.000] Script completed in scope global: script

---

Javascript compiler exception: invalid label (<refname>; line 1) in: gs.sql("(select category from incident;)"):

if i gives as gs.sql("select category from incident");

[0:00:00.002] Script completed in scope global: script

---

SQL error: FAILED TRYING TO EXECUTE ON CONNECTION 5: select category from incident Syntax Error or Access Rule Violation detected by database (Table 'dev14756_1.incident' doesn't exist) : no thrown error

gs.sql("(select count (*) ,caller_id.vip from incident where caller_id.vip="true")");

0:00:00.001] Script completed in scope global: script

---

Javascript compiler exception: missing ) after argument list (<refname>; line 1) in: gs.sql("(select count (*) ,caller_id.vip from incident where caller_id.vip="true")");

What will be the problem with this ?

Please Provide solution.

Thanks in advance.

[Brad Tilton · Accepted solution]

I think your first and third have some syntax errors, and here's the best sql reference I could find.

http://www.servicenowelite.com/blog/2014/2/1/glidesystem-sql

Why exactly are you using gs.sql() instead of a GlideRecord query, which would be the best practice?

[Vimal Priya]

Hi Brad,

I have tried by the following

Select count(*) from task where sys_class_name='incident'and state='closed';

Select count(*) from task where sys_class_name='incident'and state='closed'or state='resolved';

Select avg(time_worked) from task where sys_class_name='incident'

getting successfull response .

But My requirement is to collect data for only VIP incident .Some of my requirements are

              Total volume of VIP Tickets opened

Top ten most common Ticket types for VIP Tickets

VIP Tickets Closed

VIP Tickets created in a previous period that have not been Resolved

Average Resolution time for VIP Incidents

Resolution classifications

In that case how do i want to write the query?

When I tried writing as

Select count (*) ,caller_id.vip from task where sys_class_name='incident' and caller_id.vip='true';

it throws same error

0:00:00.003] Script completed in scope global: script

---

SQL error: FAILED TRYING TO EXECUTE ON CONNECTION 18: Select count (*) ,caller_id.vip from task where sys_class_name='incident' and caller_id.vip='true' Syntax Error or Access Rule Violation detected by database (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) ,caller_id.vip from task where sys_class_name='incident' and caller_id.vip='t' at line 1) : no thrown error

And my query over here is,

Why I am unable write query directly for incident/problem ?

Please help me.

Thanks in advance.

Regards,

VimalPriya S

[Vimal Priya]

Hi Brad,

As per my requirement I have to give SQL queries to my another team for integrating with eMite.

So I have written some set of queries and I am validating the same before handover to them .

I will check with the referenced link and get you back for the same.

Thanks in advance.

Regards,

VimalPriya S

[tony_barratt]

Hi VimalPriya,

You mention that you you have a requirement to give SQL queries to your team for integrating with eMite - however   gs.sql queries will not help with your proposed Integration.

Actually, in my opinion, gs.sql queries will not help with any integration.

My suggestion would be, you should close off this thread, marking question as answered and also marking one or more replies as helpful.

Having done that, you could get advice on how an integration with eMite could be carried out, by creating an new Community thread. There are a quite a few options that could be appropriate including REST and SOAP.

Best Regards

Tony