Looking for help with whereclause constraint

[Legacy User]

Hello community!

I'm trying to build query over BREL, i'm using custom tables, but for test purposes Requests table could be used.

For example: I want to show requests with activity log status change and description text equal "test".

 

Query will look:

alg.type = 'RE' AND alg.description like 'test'

everything works fine, now I want to restrict showing Requests with the same parametrs,

 

alg.type = 'RE' AND alg.description not like 'test'

works fine if there is Resolving activity exists but with another comment,

 

not(alg.type = 'RE' AND alg.description like 'test')

also working in vary situations.

 

My goal and question, is it possible to build query that will show: Requests with specified activity log and specified comment or Requests without that activity at all, in a single query?

Regards,

Timur

[Jon_Israel]

Hi Timur,  Unfortunately for data partition constraints this wont work because you cannot have a constraint with two objects.  In this case you are looking to put a constraint on the alg object, which is separate from the cr (request) object.  A constraint can only be used against one controlled table or object such as alg OR cr, but not both at the same time.  Something like this would require custom code to do, which is outside the scope of support.  Other folks on the communities here may have accomplished this and may be willing to share their information with you.

Thanks,

Jon I.

[JEROME MAYER]

Hi Cdtj,

I'm not sure I follow your requirements and Jon comments here

Are you looking to build a data partition constrain with that query or to create a BREL in your schema or something else?

Can you write here a pseudo code of the statement that you would like to accomplish?

 

/J

[Legacy User]

ok Jerome, here is full of sadness story

Our customer wants to implement fully custom controllable SLA scheme, firstly it was based on requestor organization, ticket impact and creation time (night tickets have slighty longer SLA).

Operating this 3 constraints we have build pretty fine and easy to administrate schema, but day by day requirements was increased up to differ SLA on moon phase, CEO happines level and distance in meters between customer and assignee.

 

So currently I'm planning schema with possibility to define SLA based on any attribute-value and it looks like:

Category have SREL attribute to SLA_Pack,

SLA_Pack have amout of SLA rules, SLA rule's structure:

• sequence (int)
• service type (SREL to sdsc)
• workshift (SREL to workshifts)
• constaraints (BREL to custom constraints)

constraints table:

• param (string)
• value (string)
• parent (SREL to SLA Rule)

 

I think this is looks hard to understand but it real environemnt it looks really easy.

I can use regular Site-Defined condition instead of custom build Constraints, but building them takes a lot of time, also I have implemented Quick Build string, which is converting string like "attr=val, attr=val, attr=val" into constraints.

 

Algorithm looks like:

- checking category for SLA pack;

- fetching all SLA rules on their sequence;

- checking one by one until all of constraints will return true;

 

Going back to topic question, I'm afraid that fetching a lot of data in our prod env can cause performance lack, so I want to slighty decrease it by predifining matching data via searching over BREL.

Search query should return all SLA rules that matching ticket data or haven't defined param at all.

 

Example:We have 3 rules in 1 pack: [impact=2, org=A], [impact=3, org=A], [org=A].

Ticket have next params [impact=3, org=A].

 

Rule 2 match ticket but rule 3 also match it (without impact param).

Building correct query that perform seach with results described before is my goal.

 

Sorry if my English is unreadable

Best regards,

cdtj

[JEROME MAYER]

Hi cdtj,

 

I think I better understand now. Smart idea but I may not have good news.

 

You mostly try to build a BREL against your constraints table.

This type of scenario is possible with TSQL where clause using sub select statement but in your case we are limited and you can only construct simple where clause against the object layer.

 

I'm afraid the only way to be more efficient in resource consumption will be to have a new column for each parameters (column impact, column org, etc...) but I suppose this is what you would like to avoid as each time they come with a new smart parameter idea you will have to modify your schema. Maybe you can put all you already have now + some extra column already for custom (column extpar1, column extpar2, etc...)

 

Another solution will be to do the reverse meaning using only one column to store a map of the parameter and the value like impact:3,org:A and store unique combination. That will unlimit the numbers of parameters and be the more efficient for performance but a nightmare to maintain all those combinations over the time. Maybe small adaptation of your quick builder can eventually help there.

 

I suppose this rules will be evaluated only at time you want to attach SLA so and to conclude if performance at this time is only your criteria, I will more concentrate of adding this table to SDM cache in nx.env and maintain a good indexing of this table in SQL.SDM is pretty good on this side and your logic (js and other ) in forms will mostly have more impact on end user response time experience that your SQL query in term of perf.

 

Hope this help anyway

/J

 

P.S.: Don't care too much for your English, I'm worse than you but we leave in a big world where tolerance must be the key for success

[Legacy User]

Thank you for the reply!

I totally forgot about NX.ENV cache settings, hope playing with them could give some performance advantages.

 

>> I suppose this rules will be evaluated only at time you want to attach SLA so and to conclude if performance at this time is only your criteria.

Actually no, because we have few events that triggers SLA recalculation like impact changing, also status changes should pause and continue attached SLAs, but this is another story

 

>> Another solution will be to do the reverse meaning using only one column to store a map of the parameter

Sounds good! I'll keep this for Plan B, feels like this can be implemented using only substr and RegEx.

Also I can use current interface to maintane logic and automatically build query string (like it happens on OOTB Sitde-Defined conditions).

 

Regards,

cdtj

[J W]

I don't have anything helpful for a solution at this time but I wanted to say that your explanation of:

 

....but day by day requirements was increased up to differ SLA on moon phase, CEO happines level and distance in meters between customer and assignee.

made me laugh because I understand that all too well!  Tomorrow it will include the cost of sushi in Japan!

 

Wishing you good luck and I see some helpful ideas here already.

 

J.W.