IN clause applied to QREL

[Giedrius Bekintis]

Hello,

According to CA documentation (Scoreboard Queries - CA Service Management - 14.1 - CA Technologies Documentation) "CA SDM supports the IN clause applied to QREL or BREL lists".

But if you try to use it you will get an error. For example if you will search for particular incident child:

children IN ( 'cr:400080' )

system will give an error (Attr not found or not atomic)

You my also search for related CMDB configuration item

bm_parent_hier.parent IN ( U'1D5A46C05D491345B482810AC7C04E7E' )

system will give an error (Bad where clause: invalid constraint. Unable to resolve bm_parent_hier.parent)

If you will do this on BREL list

parent_hier.parent IN ( U'1D5A46C05D491345B482810AC7C04E7E' )

Search will work as expected.

 

Is it some kind of BUG, or once again documentation regarding scoreboards are wrong? Maybe IN statement on QRELs should be used somehow differently?

[Legacy User]

hi,

as I found QREL could be used in SPEL only, to count amount of entries.

example: printf(<QREL_ATTR>.length);

 

Never seen other type of usage.

[Giedrius Bekintis]

Me to. But since support likes to point to documentation and make their own interpretation, when they need to prove that bug is out of the box behaviour, now i would like to get this out of box functionality .

[Michael Mueller]

Hi Giedrius.

This is my understanding:

you can use QREL's in a where clause, but there are some limitations and you need to use a special syntax.

the generic syntax looks like:

srel_attr.[back_srel_attr]brel_attr.search_attr IN ( .... )

where

• srel_attr : is an srel attribute of the factory you want to search in (fac1) , which refers to objects of another factory(fac2)
• back_srel_attr : is an srel attribute of fac2 which refers back to the same factory as the srel_attr of fac1 is reffering to (fac2)
• search_attr : is a whataever attribute of fac2 you want to compare your values to.

looks more complicate than it is , here is an example .Imagine  you have a parent CI with a given UUID. This parent has some direct child CI's in bmhier. Now you want to get all cr's where the affected resource is a child CI of the given parent.

affected_resource.[child]parent_hier.parent IN ( U'1D5A46C05D491345B482810AC7C04E7E' )

• affected_resource : is the srel_attr
• parent_hier is the brel_attr, which more or less only defines the table/factory to join with, here bmhier
• child is the back_srel_attr which joins back to the affected resource of the cr's
• parent is the search_attr you  want to compare to.

 

Hope that helps a bit.

kind regards

...............Michael

[Giedrius Bekintis]

Hi Michael, your example is BREL example since parent_hier is BREL attribute to hier (Asset_Assignment) table. I would like to use bm_parent_hier it is QREL attribute to bmhier (Business_Management). By the way your query: "affected_resource.[child]parent_hier.parent IN ( U'1D5A46C05D491345B482810AC7C04E7E' )" will work also without [child], i think is not needed anymore after multiple dedicated lrels tables was introduced to the system. See my question about thisIN statement for BRELS and brackets.

[Michael Mueller]

Hi Giedrius.

Oh I'm so sorry for my mistake. so just use it this way

 

affected_resource.[child]bm_parent_hier.parent IN ( U'0C429107774301F58D5200505637B852')"

 

Should work as explained. And substitute brel with qrel in my description

and yes the brackets are not needed when using brel's

Sorry for the confusion

..............Michael

[Giedrius Bekintis]

Thank you, Michael, it seems QRELS working in different way i will investigate this

[Giedrius Bekintis]

Ok, now i am totaly confused, according to CA support, relations that does not exist on physical layer can not be used for stored queries. But Michael allready proved that it can .

[Michael Mueller]

I have a different understanding. I wouldn't say that QREL's and BREL'S are not physical. In fact they are stored in the db. Sure there is no matching physical DB table-field name for a QREL attribute name.But a QREL attribute  only defines a kind of join or relationship rule for combining two different tables, the used attribitute/field - and table -names for these definitions are all physical existent in your DB.

You may already encounter so called LOCAL attributes. These are factory/object attribute which only exist at runtime and are not persistent,and so not stored and not existent in the DB. These kind of attributes cannot be used in any query, because they cannot be translated to a valid sql statement.

I hope this helps a bit to clear up your confusion .

Kind Regards

.....................Michael

[Legacy User]

afaik QREL is a Query relation and haven't any physical location, instead of LREL (list relation).

[Michael Mueller]

well, if they wouldn't be physical, which means , stored in the db,you wouldn't be able to use them in  where clauses.

A qrel attribute holds a list of objects which matches a given query. But this query is of course related to physical stored data. isn't it?

From this point of view a QREL and BREL and LREL isn't that much different regarding their physical representation.

lets take an example

cnt.workload is defined as QREL <- cr {assignee = ? and active = 1}

this means the qrel attribute workload of a given contact holds a list of all active tickets, where the assignee is equal to the given contact.

Of course the assignee of a ticket is stored in the db and therefor is physical!

Sure the ca_contact table does not know a field named workload. But that doesn't mean you would not able to use this attribute in a whereclause,because it can be translated into valid and working sql

[Legacy User]

that's true but only partially because LREL have physically stored in db as (lrel_id, related_obj1_id, related_obj2_id, [optional_attrs]) :)

I have tried to build whereclause query using QREL but still no success, could you provide an example with workload attribute?

Regards.

[Daniel Becker Bighelini]

Hi Michael,

In list_isscat.htmpl this where clause dont works... Why?

id.[object_attrval]workflow_templates.task IN ('APP')

------------------------------
CA Service Desk Manager Especialist Developer
PROCERGS
------------------------------

[Michael Mueller]

Hi Daniel.
Well, if I try the same , I get an error in stdlogs :
----
05/09 11:41:46.92 sdm170 sqlagt:select59 17575 ERROR orclclass.c 2198 Fetch error dbfetchDLNX. ORA: 1722:ORA-01722: invalid number , sql_clause = SELECT isscat.id FROM isscat, wftpl WHERE wftpl.object_attrval = isscat.code AND isscat.code IN (SELECT wftpl.object_attrval FROM wftpl WHERE wftpl.task = N'APP')
---
I am wondering that this error is not returned to the UI nor to bop_odump....
Anyway, I understand the following:
wftpl.object_attrval holds id's of the related isscat 's
but isscat's rel_attr is code, not id.
Therefore the system translates part of the query to  isscat.code IN ( SELECT wftpl.object_attrval ).
So there seems to be the following circumstance, which is obviously not supported:
The wftpl.obj_attrval attribute is not an srel, nor does it holds the rel_attr value of the referenced object.
I hope that clarifies at least, why it is not working.....
I don't know, if there is a chance to use a different query to get all Issue Categories which have at least one  approval task, if this is your business use-case.
Regards
...Michael





------------------------------
Principal Services Consultant
HCL Enterprise Studio
Germany
------------------------------

[Daniel Becker Bighelini]

Michael,

Unfortunately, I did not discover any other clause that identified all categories that contained at least one approval task ('APP').
Do you have any tips?

------------------------------
CA Service Desk Manager Especialist Developer
PROCERGS
------------------------------

[Michael Mueller]

Hi Daniel.

This is the only idea I have so far.

It might sound a bit strange, but you could:

Add to majic

 

OBJECT isscat {

    FACTORY isscat_dbb {

 

        STANDARD_LISTS {

            SORT_BY "sym" ;

            WHERE "delete_flag = 0" ;

            MLIST OFF ;

            RLIST OFF ;

        };

 

        REL_ATTR id ;

        COMMON_NAME sym ;

        FUNCTION_GROUP  "reference" ;

        };

} ;

 

And with that in place the following will give you the expected result:

bop_odump domsrvr isscat_dbb "id.[object_attrval]workflow_templates.task IN ('APP') and id.[object_attrval]workflow_templates.object_attrname='isscat'"

 

just checked on my test system…

As I said: you could….. 😊

 

Regards

…………Michael

------------------------------
Principal Services Consultant
HCL Enterprise Studio
Germany
------------------------------

[Daniel Becker Bighelini]

I understood the solution ... Basically the secret is to change the factory's REL_ATTR to 'id' in this case of isscat.
But unfortunately it would not meet my requirement as I need to include this search criteria in the list_isscat.htmpl form.
I even "could" do this but it would be a lot of work as I would have to replace some isscat forms.

------------------------------
CA Service Desk Manager Especialist Developer
PROCERGS
------------------------------