informix sql stuff

gistfile1.txt

run sql select lg.name as LineGroup,n.dnorpattern,dhd.hlog from linegroup as lg inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid inner join numplan as n on lgmap.fknumplan = n.pkid inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid inner join device as d on dmap.fkdevice=d.pkid inner join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid order by lg.name

run sql car select pkid,lastredirectingpartypattern,originalcalledpartypattern,finalcalledpartypattern from tbl_billing_error where originalcalledpartypattern="5025093359"



run sql car select DBINFO('utc_to_datetime',datetimeOrigination),callingPartyNumber,finalCalledPartyNumber from tbl_billing_error where callingPartyNumber like '%17990%' order by datetimeOrigination


run sql car select DBINFO('utc_to_datetime',datetimeOrigination),origdevicename,callingPartyNumber,finalCalledPartyNumber from tbl_billing_error order by datetimeOrigination


run sql car select DBINFO('utc_to_datetime',datetimeOrigination),callingPartyNumber,finalCalledPartyNumber from tbl_billing_error where finalCalledPartyNumber like '%5093359%' order by datetimeOrigination

run sql car select count(*) from tbl_billing_error 

run sql car select DBINFO('utc_to_datetime',datetimeOrigination),origdevicename,callingPartyNumber,finalCalledPartyNumber,duration from tbl_billing_data where datetimeOrigination > 1597622000 order by datetimeOrigination

run sql car select DBINFO('utc_to_datetime',datetimeOrigination),origdevicename,callingPartyNumber,finalCalledPartyNumber,duration,error_codes from tbl_billing_error where datetimeOrigination > 1597622000 order by datetimeOrigination


run sql car select DBINFO('utc_to_datetime',datetimeOrigination),origdevicename,callingPartyNumber,finalCalledPartyNumber,duration from tbl_billing_data where origdevicename like '%SEP%' order by datetimeOrigination

run sql car select count(*) from tbl_billing_data where origdevicename like '%cube%'

run sql car select DBINFO('utc_to_datetime',datetimeOrigination),origdevicename,destdevicename,callingPartyNumber,finalCalledPartyNumber,duration,error_codes from tbl_billing_error where destdevicename is null order by datetimeOrigination


cdrrecordtype globalcallid_callmanagerid globalcallid_callid origlegcallidentifier datetimeorigination orignodeid origspan origipaddr callingpartynumber callingpartyunicodelouse_value origprecedencelevel origmediatransportaddress_ip origmediatransportaddress_port origmediacap_payloadcapability origmediacap_maxframesperpacket origmediacap_g723bitr_bandwidth origvideocap_resolution origvideotransportaddress_ip origvideotransportaddress_port origrsvpaudiostat origrsvpvideostat destlegidentifier destnodeid destspan destinalcalledpartynumber finalcalledpartyunicodeloginuserid destcause_location destcause_value destprecedencelevel destmediatransportaddress_ip destmediatransportaddress_port desiacap_maxframesperpacket destmediacap_g723bitrate destvideocap_codec destvideocap_bandwidth destvideocap_resolution destvideotransportaddress_ip destvideotransportaddress_porat datetimeconnect datetimedisconnect lastredirectdn pkid                                 originalcalledpartynumberpartition callingpartynumberpartition finalcalledpartynumbeduration origdevicename         destdevicename  origcallterminationonbehalfof destcallterminationonbehalfof origcalledpartyredirectonbehalfof lastredirectredirectonbehalfof odirectredirectreason destconversationid globalcallid_clusterid joinonbehalfof comment seq_id load_id    error_record_id orignumberpacketssent orignumberoctetssent orignumberpived orignumberpacketslost origjitter origlatency destnumberpacketssent destnumberoctetssent destnumberpacketsreceived destnumberoctetsreceived destnumberpacketslost destjittauthorizationlevel clientmattercode origdtmfmethod destdtmfmethod origvarvqmetrics destvarvqmetrics callsecuredstatus origconversationid origmediacap_bandwidth destmediacap_bpulsedcallingpartynumber outpulsedcalledpartynumber origipv4v6addr destipv4v6addr origvideocap_codec_channel2 origvideocap_bandwidth_channel2 origvideocap_resolution_channel2el2 origvideotransportaddress_port_channel2 origvideochannel_role_channel2 destvideocap_codec_channel2 destvideocap_bandwidth_channel2 destvideocap_resolution_channel2 destvitvideotransportaddress_port_channel2 destvideochannel_role_channel2 incomingprotocolid incomingprotocolcallref          outgoingprotocolid outgoingprotocolcallref          cun lastredirectingroutingreason huntpilotdn huntpilotpartition error_codes calledpartypatternusage outpulsedoriginalcalledpartynumber outpulsedlastredirectingnumber wascallquetynumber_uri originalcalledpartynumber_uri finalcalledpartynumber_uri lastredirectdn_uri mobilecallingpartynumber finalmobilecalledpartynumber origmobiledevicename destmobileestmobilecallduration mobilecalltype originalcalledpartypattern finalcalledpartypattern lastredirectingpartypattern huntpilotpattern