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.

[Vimal Priya]

Hi Brad ,

I tried the following sql queries from the link provided,

but still i am getting the same error for the below queries

gs.sql("select count(*) from incident");

gs.sql("select * from incident a, task b where a.sys_id=b.sys_id");

and other queries related to incident

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

---

SQL error: FAILED TRYING TO EXECUTE ON CONNECTION 28: select * from incident a, task b where a.sys_id=b.sys_id Syntax Error or Access Rule Violation detected by database (Table 'dev13239_1.incident' doesn't exist) : no thrown error

Please provide solution.

Thanks in advance.

Regards,

VimalPriya S

[Gurpreet07]

There is no incident table at in the database its just a kind of a view and hence gs.sql("desc incident") is throwing error but gs.sql("desc task") returns table details of the task table. You will find all the fields extended tables on the task table itself and you could try to make queries from task table itself by applying a filter in where clause like sys_class_name = 'incident' .Also assign some alias to the tables. following query is working fine for incident_state field

gs.sql("select t.incident_state from task t where t.sys_class_name='incident'");

[Vimal Priya]

Yes Gurpreet,

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

[bernyalvarado]

Hi, i believe you cannot have "count(*)" plus other fields in the select unless you're using a group by with the same fields in the select clause

The solution using GROUP BY will then be something like this:

Select count(*), caller_id.vip from task

        WHERE sys_class_name='incident' and caller_id.vip='true'

        GROUP BY caller_id.vip;

Thanks,

Berny

[bernyalvarado]

By the way, you may want to rethink have you're doing the integration between ServiceNow and eMite. If you need to involve SQL queries for it, it doesn't seem to be right. In fact, ServiceNow restricts direct interaction with its DB and that can also be accomplished through some workaround of some third party solutions out there.

I will recommend you rather get your team to use ServiceNow Table API, Web Services, etc... which should provide a better solution. If you don't have much experience in this area perhaps it will be good to get in touch with a ServiceNow architecture to guide you through the integration process.

Thanks,

Berny