CA Service Management

  • 1.  Query on Where clause of send_wait() spl function

    Posted Jan 16, 2017 10:20 AM

    Hello Support,

     

    Help on constructing the where clause in spl function, I would like to fetch the maximum value in the table.

    the equivalent sql server query

    SELECT MAX(column_name) FROM table_name;

    any help on constructing the where clause equivalent of above one.

     

    Thank you



  • 2.  Re: Query on Where clause of send_wait() spl function

    Posted Jan 17, 2017 01:26 AM

    Hi CodeGeek,

     

    Unfortunately the where clause in 'send_wait' doesn't support 'max' or similar constructs.  (Incidentally, a quick way to test whether spl supports a particular 'where' clause is to run it using the 'bop_odump' utility.)  What is the particular problem that you're trying to solve?

     

    Regards,

    James



  • 3.  Re: Query on Where clause of send_wait() spl function

    Posted Jan 17, 2017 04:47 AM

    Thanks James, I'm trying to fetch the max value  of fire time in  Attached_Events SLA events of a particular ticket. we've a custom column in detail page, I've to  assign the above value as projected SLA date/time of a ticket.



  • 4.  Re: Query on Where clause of send_wait() spl function

    Posted Jan 17, 2017 10:59 AM

    Hi,

    I've build a macro to get max/min value but in your case is better to create new DOMSET and fetch last / first value from sorted list. But this one should work too:

     

    #define MSG_ERROR for(msg_i=0;msg_i<msg_length();msg_i++) { logf(ERROR, "msg[%d]: %s", msg_i, msg[msg_i]); }

    object z_fetch_val(string action, string factory, string attr_name, string where_clause) {
         int zcount, msg_i, i, fetch_val;
         object zobj, zfound, fetch_obj;
         send_wait(0, top_object(), "call_attr", factory, "sync_fetch", "STATIC", where_clause, -1, 0);
         fetch_val = 0;
         if (msg_error()) {
              MSG_ERROR
         } else {
              zcount = msg[1];
              if (zcount > 0) {
                   zfound = msg[0];
                   for (i=0;i<zcount;i++) {
                        send_wait(0, zfound, "dob_by_index", "DEFAULT", i, i);
                        if (msg_error()) {
                             MSG_ERROR
                        } else {
                             zobj = msg[0];
                             send_wait(0, zobj, "call_attr", attr_name, "get_val");
                             if (msg_error()) {
                                  MSG_ERROR
                             } else {
                                  if ( (action == "MAX") && ( (fetch_val == 0) || (fetch_val < (int)msg[0]) ) ) {
                                       fetch_obj = zobj;
                                       fetch_val = msg[0];
                                  } else if ( (action == "MIN") && ( (fetch_val == 0) || (fetch_val > (int)msg[0]) ) ) {
                                       fetch_obj = zobj;
                                       fetch_val = msg[0];                                   
                                  }
                             }
                        }
                   }
              }
         }
         return fetch_obj;
    }

     

    Usage example:

    macro:

    void z_get_val() {
         int msg_i;
         object max_atev, min_atev;
         max_atev = z_fetch_val("MAX", "atev", "fire_time", "obj_id = 'cr:3194616' AND group_name = 'UC' AND status_flag = 2");
         if (!is_null(max_atev)) {
              send_wait(0, max_atev, "call_attr", "fire_time", "get_val");
              printf("max : %s\n", msg[0]);
         }
         min_atev = z_fetch_val("MIN", "atev", "fire_time", "obj_id = 'cr:3194616' AND group_name = 'UC' AND status_flag = 2");
         if (!is_null(min_atev)) {
              send_wait(0, min_atev, "call_attr", "fire_time", "get_val");
              printf("min : %s\n", msg[0]);
         }
    }

     

    input:

     

    output:

    max : 01/18/2017 19:00:00
    min : 01/18/2017 13:48:00

     

    Regards,

    cdtj



  • 5.  Re: Query on Where clause of send_wait() spl function

    Posted Jan 17, 2017 11:53 AM

    thanks for the inputs cdtj , will share my progress. Thank you