Query on Where clause of send_wait() spl function

[venkateswarlu v]

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

[Legacy User]

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

[venkateswarlu v]

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.

[Legacy User]

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

[venkateswarlu v]

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