Clarity PPM

Expand all | Collapse all

GEL Script to update a table column based on conditions

  • 1.  GEL Script to update a table column based on conditions

    Posted 03-22-2019 04:25 PM

    Hello, 

     

    Could some please provide me sample of a GEL script that updates a database column  based on Risks column value?

     

    For example:

     

     

    If (likelihood * consequence) <5 then update Table_X set String_Column_1 = “SAFE”

    If (likelihood * consequence) is BETWEEN 5 and 10 then update Table_X set String_Column_1 = “ALERT”

    If (likelihood * consequence) >10 then update Table_X set String_Column_1 = “DANGER”

     

    I would appreciate,  if you can please advise.

     

    Thanks and regards,

    MJ

    +61402251100



  • 2.  Re: GEL Script to update a table column based on conditions

    Posted 03-25-2019 11:50 PM

    Is this linked to your other Community Topic:

    Calculated Risk - Pre and Post treatment - Value in NUX - not number. 

     

    I would undertake using a case statement in the SQL in the GEL script

    case (likelihood * consequence) when  <5 then 'SAFE'

                                                         when < 10 then 'ALERT'

        ELSE 'DANGER' end risk_string

     

    'risk_string' would then be used to in your XOG to update the Risk object.

     

    There are 'case statements' also in GEL, but I try to do a lot of my logic in SQL where I can check the results on the database to ensure correct values are returned.

     

    Note, I may not have the format of the case statement 100% correct, so adjust as required.

     

    As indicated in the previous topic, I have undertaken the above approach.

     

    As an additional suggestion, I would be storing a non-descriptive string or number, rather than things like 'SAFE', 'ALERT' and 'DANGER'.  These 'codes' may change over time, so may not be relevant in the future.