sql query Custom list table limit in Widgets / SQL problems?

[Gareth Watkins]

Hi all

 

I'm trying to create a list type widget in Service Manager and wondered if there was any limitation on querying more than 1 table using the custom SQL option. I cant seem to get any results when using even a basic query over 2 tables using a JOIN. 
 

I'd hoped that, as there is a single table query option, the custom would allow queries or multiple tables if written with the correct join statements. Can anyone help?

A sample of my SQL is below. Not a die-hard SQL user but this query successfully returns results via database direct so I would expect it to do so in the custom widget SQL window. What I get instead is a generic error advising to check the log for details. I've scoured the logs but cant locate the error / information to try and rectify the problem. 

SELECT h_itsm_requests.h_pk_reference, h_sys_tasks.h_task_id
FROM h_itsm_requests
JOIN h_sys_tasks ON h_itsm_requests.h_social_object_ref = h_sys_tasks.h_obj_ref_urn

 

Any ideas?

[Steve Giller]

We need to start with what you're trying to achieve here, as off the top of my head that would simply return every request and every Task attached to them. There doesn't appear to be any filtering which means it's very likely that the query would simply time out trying to retrieve a list of every Request along with every Task you've ever raised.

What are you trying to measure here, and what would you expect the widget to display?

[Victor]

1 hour ago, Gareth Watkins said:

this query successfully returns results

We also need to understand what "successful" means in DB and SQL context. I mean it might be that it did return results but if in doing so it consumed every bit of CPU/RAM/HDD that it could consume, then yes, results, but at what cost... pyrrhic (not always victory) some would say...

As @Steve Giller said, what is the widget for?

The query is valid, and works on my instance in a widget. Is your widget configured with the exact same query? (again, as Steve, said, I hope this is just a test, not the best one I would say... it does not return all requests, but it will return all tasks that ever existed which are associated with a request... timeout is very possible)

 

[Gareth Watkins]

HI both

I'll try and run it again using a where statement with some parameters to limit the potential number of results and rule out a timeout. I'll come back if I still get errors.