sqlciscocucm

SQL, CUCM: query returns with no result


Following SQL query in CUCM (11.5) cli returns with the following result:

device          number          loggedin_to_lg linegroup
=============== =============== ============== ======================================
CSFABCDEF      \+49325874147  f              LG-HG_BER01_49325874147      
CSFRFETRS      \+49325800848  f              LG-HG_BER01_493225800848
run sql select d.name as Device, n.dnorpattern as Number, dhd.hlog as LoggedIn_to_LG, lg.name as LineGroup 
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 

However, if we try to inner join one more table -extensionmobilitydynamic- to this query and display one of its columns, it returns with no result.

run sql select d.name as Device, n.dnorpattern as Number, dhd.hlog as LoggedIn_to_LG, lg.name as LineGroup, e.datetimestamp 
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 
inner join extensionmobilitydynamic as e on e.fkdevice = d.pkid 
order by lg.name 

Solution

  • It's not finding any matching rows and hence the INNER JOIN fails to retrieve anything. You must try left join for the new table where there is a possibility to have no matching results. The resultant column will be NULL in this instance.

    run sql select d.name as Device, n.dnorpattern as Number, dhd.hlog as LoggedIn_to_LG, lg.name as LineGroup, e.datetimestamp 
    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 
    left join extensionmobilitydynamic as e on e.fkdevice = d.pkid 
    order by lg.name