Clarity Service Management

Expand all | Collapse all

Stored Query - List of Change orders without any children

Jump to Best Answer
  • 1.  Stored Query - List of Change orders without any children

    Posted 01-15-2016 05:18 AM

    Hello everybody!

    I'm trying to create a Stored Query that lists the Change Orders without any children.

     

    I've tried with the following syntax:

    children.length = 0

    but I get the following error message: AHD05800: Bad where clause. invalid constraint. Unable to resolve children .

     

    I even tried with the following syntax:

    children.0.chg_ref_num = \'\'

    but I obtain the same message error.

     

    Does anybody have any ideas?

    Is the only possible solution to add a custom field (zchildren_num) to set by a custom trigger?

     

    Thank you everybody!



  • 2.  Re: Stored Query - List of Change orders without any children

    Posted 01-15-2016 03:09 PM

    I'm not sure how you will get the information but, I believe I can tell you why looking for the Child isn't working. In the database, the change order does not contain the relationship to the child. The change order contains the relationship to the parent. Since a change order can have one and only one parent, I imagine that was the cleanest way to store the relationship. the SQL would be something like:

    select c.id

    from chg c

    where c.id not in

    (select distinct p.parent

    from chg p

    where p.parent is not null)

     

     

    Hopefully that helps you some.



  • 3.  Re: Stored Query - List of Change orders without any children

    Posted 01-15-2016 05:44 PM

    I don't think you can use a stored query for this purpose as the

    schema is

    children             QREL <- chg {parent = ?}

    so children is a QREL and for QREL I don't think you can use

    in stored query. You could use parent though:

    parent               SREL -> chg.id SERVICE_PROVIDER_ELIGIBLE

    as parent is a SREL. But I am not sure this is what you wanted.



  • 4.  Re: Stored Query - List of Change orders without any children

    Posted 01-21-2016 11:55 AM

    Hi masem04 - We're you able to resolve using the suggestions? If so please mark appropriate response as Correct Answer. Thanks! Chris

     

     



  • 5.  Re: Stored Query - List of Change orders without any children
    Best Answer

    Posted 01-21-2016 03:03 PM

    Here is the thread about QREL quries : Re: IN clause applied to QREL

    And I beleive there is no way to build stored query over QREL attribute.