Zapros

select

FiberAccess.linkage_id

from

  (select object_id, name from nc_objects where object_type_id = 9141263644613354123 /*SingTel.SOM Customer Account*/

    and rownum < 100000

  ) customer,

  (select obj.parent_id,

    --service_id.value as service_id,

    linkage_id.value as linkage_id--,

    --part_number.value as part_number

    from  (select object_id, parent_id from nc_objects where object_type_id = 9143363423513357170 /* VoIP CFS Instance */) obj,

        --nc_params state,

        --nc_params status,

        nc_params linkage_id,

        nc_params legacy_instance

    where --obj.object_type_id = 9143363423513357170 /* VoIP CFS Instance */

        --and obj.object_id = state.object_id

        --and state.attr_id = 9126800509413936401 /* Snapshot State */

        --and state.list_value_id = 9126816236013101302 /* Implemented */

        --and obj.object_id = status.object_id

        --and status.attr_id = 3073119249013100809 /* Instance Status */

        --and status.list_value_id = 3073119249013100803 /* Active */

        --and

        obj.object_id = linkage_id.object_id

        and linkage_id.attr_id = 9142790846013210279 /* Linkage ID */

        and obj.object_id = legacy_instance.object_id

        and legacy_instance.attr_id = 9143068644513325913 /* Legacy instance ID */

  ) voip,

  (select obj.parent_id as parent_id,

    linkage_id.value as linkage_id,

    service_id.value as service_id

  from  nc_objects obj,

        nc_params linkage_id,

        nc_params service_id,

        nc_params legacy_instance

  where obj.object_type_id = 9143318470413162402 /* Broadband CFS Instance */

        and obj.object_id = linkage_id.object_id

        and linkage_id.attr_id = 9142790846013210279 /* Linkage ID */

        and obj.object_id = service_id.object_id

        and service_id.attr_id = 9141142124413285334 /* Service ID */

        and obj.object_id = legacy_instance.object_id

        and legacy_instance.attr_id = 9143068644513325913 /* Legacy instance ID */

  ) bb_cfs,

  (select obj.parent_id,

    linkage_id.value as linkage_id,

    service_id.value as service_id

  from (select object_id, parent_id from nc_objects where object_type_id = 9143319079413173490 /* Fiber Access CFS Instance*/) obj,

        nc_params linkage_id,

        nc_params service_id

  where --object_type_id = 9143319079413173490 /* Fiber Access CFS Instance*/

        --and

        obj.object_id = linkage_id.object_id

        and linkage_id.attr_id = 9142790846013210279 /* Linkage ID */

        and obj.object_id = service_id.object_id

        and service_id.attr_id = 9141142124413285334 /* Service ID */

  ) FiberAccess

where

  --iptv.linkage_id = stb.linkage_id

  bb_cfs.linkage_id = FiberAccess.linkage_id

  and voip.linkage_id = FiberAccess.linkage_id

  and customer.object_id = FiberAccess.parent_id

  --and (select count(*) as instance from nc_objects where parent_id = FiberAccess.parent_id ) < 5 /*customer has only Fiber Access, IPTV, STB and Viop INSTANCES*/

  --and FiberAccess.parent_id = stb.parent_id

  --and (SELECT COUNT( parent_id ) AS cfs from nc_objects where parent_id = stb.parent_id and object_type_id = 9143743156413198340 /* Fixed Composite Order */) < 1 /*whithout composite oreders*/

  --and rownum <2