SQL Form > Example Tab queries

This section give some examples of tab queries.

These are customized queries that appears in existing (other) IMS forms.

Query in node form

Query settings:

AddToTab:

NODEFYSICAL

SQLCHECK:

SELECT 1 FROM NODENAME WHERE MANAGEMENTSYS = 'CISCO MPLS' AND ID = #[#NODENAME#]#

AND TYPE <> 'VRF'

The SQL check is used to perform a more simple query, so that IMS quickly can determine whether the

query tab will be visible or not.

This to avoid to run a complex (slow!) query when opening the form.

In this example MANAGEMENTSYS = 'CISCO MPLS' makes that the query tab is only presented when the

node belongs to the Cisco MPLS management system

The ID = #[#NODENAME#]# is added to only link the opened node in IMS Node form to the query tab.

SQL Query:

SELECT distinct intd.ims_portname port , intd.status admin_status ,

intd.line_protocole line_protocole_status , intd.MTU , intd.BW , intd.description

FROM IF_CISCO_MPLS.CISVW_INTERFACE_DETAILS intd

JOIN IF_CISCO_MPLS.CISVW_COMMON_REF_RAW_DATA crrd

ON crrd.NODEOBJECT_REFID = intd.NODE_ID

JOIN NODENAME nn ON crrd.hostname = nn.nodename AND nn.managementsys = 'CISCO MPLS'

WHERE nn.id = #[#NODENAME#]#

AND NOT ( intd.description is null and intd.ip is null )

AND regexp_replace( intd.ims_portname, '[0-9/.]' ) NOT IN ( 'LO' , 'BVI' , 'BE' , 'PC' , 'VI')

ORDER by port

This example query read some additional information from external database schema to present in IMS

Node form.

The nn.id = #[#NODENAME#]# is added to only show the node information related to the opened node in

the IMS Node Form.

In this example nn.managementsys = 'CISCO MPLS' makes that the query data is only presented when the

node belongs to the Cisco MPLS management system.

Query in circuit form

Query settings:

AddToTab:

CIRCUITDETAILSTAB

SQLCHECK:

SELECT 1 FROM CIRCUITS WHERE CIRCUITID=#[#CIRCUITS#]#

The SQL check is used to perform a more simple query, so that IMS quickly can determine whether the

query tab will be visible or not.

This to avoid to run a complex (slow!) query when opening the form.

The CIRCUITID=#[#CIRCUITS#]# is added to only link the opened circuit in IMS Circuit form to the query tab.

SQL Query:

SELECT SUBSTR(SEQNRS,2,LENGTH(SEQNRS)) seq_nr, CIRCUITNAME,SPEED,

SUBSTR(REVERSE(TIJDSLOTEN2),1,LENGTH(TIJDSLOTEN2)-1) channels ,

F.SEQNR , EQUIPMENTNAME,f.shelfname,f.cardport, N.TYPE,

E.KINDOFFEQUIPMENT equipmentkind, sort_code

FROM CIRCUITS C , FYSICAL F, NODENAME N, EQUIPMENTKIND E,

(SELECT CARRIER ,

SYS_CONNECT_BY_PATH(TO_CHAR( SEQNR,'0000000' ) ,'/') SORT_code,

SYS_CONNECT_BY_PATH(REVERSE(DECODE (TIMESLOTFROM ,TIMESLOTTO,

TO_CHAR(TIMESLOTFROM) , '(' || TIMESLOTFROM || '-' || TIMESLOTTO || ')' )) ,'/') TIJDSLOTEN2

, SYS_CONNECT_BY_PATH(TO_CHAR(SEQNR) ,'/') SEQNRS

FROM CARRIERS CAR

START WITH CAR.CIRCUITID=#[#circuits#]#

CONNECT BY NOCYCLE CAR.CIRCUITID=PRIOR CAR.CARRIER AND CAR.CARRIER>0

AND NOT EXISTS( SELECT 1 FROM CIRCUITS WHERE CIRCUITID=CAR.CARRIER AND SPEED ='CBL1')

) DRAGERS

WHERE C.CIRCUITID = DRAGERS.CARRIER

AND C.CIRCUITID=F.CIRCUITID(+)

AND F.EQUIPMENTNAME=N.NODENAME(+)

AND N.TYPE=E.TYPE(+)

ORDER BY SORT_code, F.SEQNR

This example query find all upper carrier connections of an opened connection in IMS Circuit Form.

The CAR.CIRCUITID=#[#circuits#]# is added to only show the circuit(carrier) information related to the

opened circuit in the IMS Node Form.