Find RAN containment

These queries retrieve the details of RAN entities that are contained, by other entities.

Example: find all sectors within a given cell

This query retrieves the details of all sectors within a given cell. There is no direct relationship between a sector and a cell. Sectors are hosted by transceivers, and transceivers are contained within a base station. There is a collects relationship between cells and transceivers. The query also deals with fact that there are two different types of cell: GSM cells and UTRAN cells.

select e1.entityId sectorEntityId,
 e1.entityName sectorName,
 e2.entityId cellEntityId ,
 e2.entityName cellEntityName, 
 COALESCE(rgc.cellid, ruc.cellid),
 COALESCE(rgc.rantechnologytype,'UMTS') cellType 
from ncim.entityData e1
INNER JOIN ncim.ranSector rs ON rs.entityId = e1.entityId
INNER JOIN ncim.hostedService hs ON hs.hostedEntityId = e1.entityId
INNER JOIN ncim.entityData e3 ON e3.entityId = hs.hostingEntityId
INNER JOIN ncim.collects c ON c.collectedEntityId = e3.entityId
INNER JOIN ncim.entityData e2 ON e2.entityId = c.collectingEntityId
LEFT OUTER JOIN ncim.rangsmcell rgc ON rgc.entityId = e2.entityId
LEFT OUTER JOIN ncim.ranutrancell ruc ON ruc.entityId = e2.entityId
WHERE
(
	e2.entityName = cell_name
	AND
	(
		e2.entityType = 130
		OR
		e2.entityType = 131
	)
);

The table below describes this query.

Table 1. Description of the query
Line numbers	Description
1-6	Specify the data to show in the results, as follows: The entity ID of the sector, represented by `e1.entityId`. The name of the sector, represented by `e1.entityName` The ID of the cell, represented by `e2.entityId` The name of the cell, represented by `e2.entityName` Use the `COALESCE` function to take either GSM or UTRAN cell IDs as a return value.
7	Use the `entityData` table as the driving table for this query.
8	Limit the results of the query to RAN sectors
9-10	The alias `e3` identifies the hosting transceiver. The JOIN operations on these lines limit the results to the transceiver that hosts the RAN sectors.
11-12	The alias `e2` identifies the cells that collect the transceivers. The JOIN operations on these lines limit the results to the cells that collect the transceiver, that in turn hosts the RAN sectors.
13-14	Join the two cell tables, GSM and UTRAN. Use an outer join, as one of these tables will be empty.
15-23	Specify the cell name and include results for GSM cells (`entityType = 130`) and UTRAN cells (`entityType = 131`).

Similar queries

The following example queries retrieve relevant data for different RAN relationships, using similar syntax to the above example.

Example: find contents of the RAN radio core

This query retrieves the contents of the RAN radio core.

SELECT e.entityId,
 e.entityName, ch.className, 
 e2.entityName RANRadioCore,
 rrc.mmc, rrc.mnc 
FROM ncim.entityData e
INNER JOIN ncim.physicalChassis ch ON ch.entityId = e.entityId
INNER JOIN ncim.collects c ON c.collectedEntityId = e.entityId
INNER JOIN ncim.entityData e2 ON e2.entityId = c.collectingEntityId
INNER JOIN ncim.ranRadioCore rrc ON rrc.entityId = e2.entityId
WHERE
e2.entityType = 138

Example: find contents of the RAN circuit-switched core

This query retrieves the contents of the RAN circuit-switched core.

SELECT e.entityId,
 e.entityName, ch.className, 
 e2.entityName RANCircuitSwitchedCore,
 rcsc.mmc, rcsc.mnc 
FROM ncim.entityData e
INNER JOIN ncim.physicalChassis ch ON ch.entityId = e.entityId
INNER JOIN ncim.collects c ON c.collectedEntityId = e.entityId
INNER JOIN ncim.entityData e2 ON e2.entityId = c.collectingEntityId
INNER JOIN ncim.ranCircuitSwitchedCore rcsc ON rcsc.entityId = e2.entityId
WHERE
e2.entityType = 137

Example: find contents of the RAN packet-switched core

This query retrieves the contents of the RAN packet-switched core.

SELECT e.entityId,
 e.entityName, ch.className, 
 e2.entityName RANPacketSwitchedCore,
 rpsc.mmc,
 rpsc.mnc 
FROM ncim.entityData e
INNER JOIN ncim.physicalChassis ch ON ch.entityId = e.entityId
INNER JOIN ncim.collects c ON c.collectedEntityId = e.entityId
INNER JOIN ncim.entityData e2 ON e2.entityId = c.collectingEntityId
INNER JOIN ncim.ranPacketSwitchedCore rpsc ON rpsc.entityId = e2.entityId
WHERE
e2.entityType = 136