query? Measures on h_itsm_requests with conditions in the related custom field tables

[Martyn Houghton]

Measures are linked to a single table rather than an entity.

Therefore we are trying to create a measure for on-hand (open requests) on h_itsm_requests where there are certain criteria such as h_custom_c in the h_itsm_incidents related table has a certain value.

How do we use a sub select in the query (where) clause of the measure to check for a static value?

Cheers

Martyn
 

[Martyn Houghton]

After much head scratching I have got it working in database direct, so need to see it is will work in a measure.

 

h_status not in ('status.closed','status.cancelled') and
h_fk_serviceid in 
(SELECT h_pk_serviceid FROM h_itsm_services where h_fk_servicecategory in (
'Service Desk - Uniform Tech')) and
(
((SELECT h_itsm_incidents.h_custom_c from h_itsm_incidents where h_pk_reference=h_itsm_incidents.h_fk_reference) is not null)
and 
((SELECT h_itsm_incidents.h_custom_d from h_itsm_incidents where h_pk_reference=h_itsm_incidents.h_fk_reference) in ('BUG','Bug'))
OR 
((SELECT h_itsm_servicerequests.h_custom_c from h_itsm_servicerequests where h_pk_reference=h_itsm_servicerequests.h_fk_reference) is not null)
and 
((SELECT h_itsm_servicerequests.h_custom_d from h_itsm_servicerequests where h_pk_reference=h_itsm_servicerequests.h_fk_reference) in ('BUG','Bug'))
OR
((SELECT h_itsm_problems.h_custom_c from h_itsm_problems where h_pk_reference=h_itsm_problems.h_fk_reference) is not null)
and 
((SELECT h_itsm_problems.h_custom_d from h_itsm_problems where h_pk_reference=h_itsm_problems.h_fk_reference) in ('BUG','Bug'))
OR
((SELECT h_itsm_changerequests.h_custom_c from h_itsm_changerequests where h_pk_reference=h_itsm_changerequests.h_fk_reference) is not null)
and 
((SELECT h_itsm_changerequests.h_custom_d from h_itsm_changerequests where h_pk_reference=h_itsm_changerequests.h_fk_reference) in ('BUG','Bug'))
)

 

[Martyn Houghton]

Doh! measure query text is limited to 1024 characters!