[E.Gómez]

Hello
I'm trying to create one new customized role for ticket assignement into incident/service request workflow.. The role  has a conditional behavior
- If related CI is defined on ticket creation, then role == one CI-role (support group for incients of this CI, using CI ID and role id or name).
- If no, assign to category (sd_catalog) group

I'm trying some SQLs working ok into sql client but when ported to Easyvista I got error.. I don't know if there are limitations into these SQL statements or....

Example of one SQL working at sql server but not into application (I've tryed too with case, working ok at sql client, same error at application)
---------------------------
IF (SELECT CR.GROUP_ID FROM CMDB_ASSET_GROUP_ROLE CR
    INNER JOIN SD_REQUEST TIK ON TIK.CI_ID = CR.ASSET_ID
    WHERE REQUEST_ID IN (@ID@) AND CR.GROUP_ID IS NOT NULL AND CR.ROLE_ID = 45 ) IS NULL

(SELECT CAT.GROUP_ID FROM SD_CATALOG CAT
INNER JOIN SD_REQUEST TIK ON TIK.SD_CATALOG_ID = CAT.SD_CATALOG_ID
WHERE TIK.REQUEST_ID IN (@ID@) AND CAT.GROUP_ID IS NOT NULL)

ELSE
(SELECT CR.GROUP_ID FROM CMDB_ASSET_GROUP_ROLE CR
INNER JOIN SD_REQUEST TIK ON TIK.CI_ID = CR.ASSET_ID
WHERE REQUEST_ID IN (@ID@) AND CR.GROUP_ID IS NOT NULL AND CR.ROLE_ID = 45)
----------------------
I got error (060000005 - Missing SQL property) at SMO_MSSQLException log. As I see, Easyvista is sending ok the query to database. If I copy the statement from exception log file to sql client, it works ok, I get the correct group_id

Any help?
thank you!

[ABBE Philippe]

(10-30-2019, 11:05 AM)egomez Wrote: Hello
I'm trying to create one new customized role for ticket assignement into incident/service request workflow.. The role  has a conditional behavior
- If related CI is defined on ticket creation, then role == one CI-role (support group for incients of this CI, using CI ID and role id or name).
- If no, assign to category (sd_catalog) group

I'm trying some SQLs working ok into sql client but when ported to Easyvista I got error.. I don't know if there are limitations into these SQL statements or....

Example of one SQL working at sql server but not into application (I've tryed too with case, working ok at sql client, same error at application)
---------------------------
IF (SELECT CR.GROUP_ID FROM CMDB_ASSET_GROUP_ROLE CR
    INNER JOIN SD_REQUEST TIK ON TIK.CI_ID = CR.ASSET_ID
    WHERE REQUEST_ID IN (@ID@) AND CR.GROUP_ID IS NOT NULL AND CR.ROLE_ID = 45 ) IS NULL

(SELECT CAT.GROUP_ID FROM SD_CATALOG CAT
INNER JOIN SD_REQUEST TIK ON TIK.SD_CATALOG_ID = CAT.SD_CATALOG_ID
WHERE TIK.REQUEST_ID IN (@ID@) AND CAT.GROUP_ID IS NOT NULL)

ELSE
(SELECT CR.GROUP_ID FROM CMDB_ASSET_GROUP_ROLE CR
INNER JOIN SD_REQUEST TIK ON TIK.CI_ID = CR.ASSET_ID
WHERE REQUEST_ID IN (@ID@) AND CR.GROUP_ID IS NOT NULL AND CR.ROLE_ID = 45)
----------------------
I got error (060000005 - Missing SQL property) at SMO_MSSQLException log. As I see, Easyvista is sending ok the query to database. If I copy the statement from exception log file to sql client, it works ok, I get the correct group_id

Any help?
thank you!

Hello,
Can you try this query :

SELECT COALESCE(CR.GROUP_ID,CAT.GROUP_ID) FROM SD_REQUEST TIK
LEFT OUTER JOIN CMDB_ASSET_GROUP_ROLE CR ON TIK.CI_ID = CR.ASSET_ID AND CR.ROLE_ID = 45
LEFT OUTER JOIN SD_CATALOG CAT ON TIK.SD_CATALOG_ID = CAT.SD_CATALOG_ID
WHERE REQUEST_ID IN (@ID@)

P.ABBE

[E.Gómez]

Hello
It's working ok at development platform. thanks a lot..
I was trying some similar with cases (when X is not null then X), as I knew coalesce is translated into case at sql server, but always got error with explicit case...... I don't know why...
But this statement is working ok.

thak you