AnsweredAssumed Answered

Help with a Dimension Query

Question asked by mscann on Feb 5, 2013
Latest reply on Feb 14, 2013 by Dave
Hi All,

I need help resolving Error: "NPT-217: This query produced duplicate dimensional data. The results shown here may be invalid or incomplete/"

This is NSQL for a Portlet in which I'm getting this error. It has another Portlet using the same
table:
SELECT @SELECT:DIM:USER_DEF:IMPLIED:APP_STATUS:SUBPAGE:SUBPAGE@,
@SELECT:METRIC:USER_DEF:IMPLIED:ESS:ESS@,
@SELECT:METRIC:USER_DEF:IMPLIED:DSS:DSS@,
@SELECT:METRIC:USER_DEF:IMPLIED:CCS:CCS@,
@SELECT:METRIC:USER_DEF:IMPLIED:INF:INF@,
@SELECT:METRIC:USER_DEF:IMPLIED:CTA:CTA@,
@SELECT:METRIC:USER_DEF:IMPLIED:MedPlus:MedPlus@,
@SELECT:METRIC:USER_DEF:IMPLIED:EAT:EAT@,
@SELECT:METRIC:USER_DEF:IMPLIED:IES:IES@,
@SELECT:METRIC:USER_DEF:IMPLIED:AMP:AMP@,
@SELECT:METRIC:USER_DEF:IMPLIED:HOS:HOS@,
@SELECT:METRIC:USER_DEF:IMPLIED:PandP:PandP@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:APP_STATUS:BRANCH_ID_DSS:BRANCH_ID_DSS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:APP_STATUS:BRANCH_ID_CCS:BRANCH_ID_CCS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:APP_STATUS:BRANCH_ID_INF:BRANCH_ID_INF@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:APP_STATUS:BRANCH_ID_CTA:BRANCH_ID_CTA@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:APP_STATUS:BRANCH_ID_MEDPLUS:BRANCH_ID_MEDPLUS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:APP_STATUS:BRANCH_ID_EAT:BRANCH_ID_EAT@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:APP_STATUS:BRANCH_ID_IES:BRANCH_ID_IES@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:APP_STATUS:BRANCH_ID_ESS:BRANCH_ID_ESS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:APP_STATUS:BRANCH_ID_AMP:BRANCH_ID_AMP@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:APP_STATUS:BRANCH_ID_HOS:BRANCH_ID_HOS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:APP_STATUS:BRANCH_ID_PandP:BRANCH_ID_PandP@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:APP_STATUS:SUBPAGE_ID:SUBPAGE_ID@
FROM (
Select SUBPAGE, SUBPAGE_ID
,((1-decode(MAX(DECODE(BRANCH_UNIT_ID,5000200,null_ct/all_ct,null)),0,1,MAX(DECODE(BRANCH_UNIT_ID,5000200,null_ct/all_ct,null))))*100) DSS
,((1-decode(MAX(DECODE(BRANCH_UNIT_ID,5000190,null_ct/all_ct,null)),0,1,MAX(DECODE(BRANCH_UNIT_ID,5000190,null_ct/all_ct,null))))*100) CCS
,((1-decode(MAX(DECODE(BRANCH_UNIT_ID,5000192,null_ct/all_ct,null)),0,1,MAX(DECODE(BRANCH_UNIT_ID,5000192,null_ct/all_ct,null))))*100) INF
,((1-decode(MAX(DECODE(BRANCH_UNIT_ID,5001233,null_ct/all_ct,null)),0,1,MAX(DECODE(BRANCH_UNIT_ID,5001233,null_ct/all_ct,null))))*100) CTA
,((1-decode(MAX(DECODE(BRANCH_UNIT_ID,5000841,null_ct/all_ct,null)),0,1,MAX(DECODE(BRANCH_UNIT_ID,5000841,null_ct/all_ct,null))))*100) MedPlus
,((1-decode(MAX(DECODE(BRANCH_UNIT_ID,5001465,null_ct/all_ct,null)),0,1,MAX(DECODE(BRANCH_UNIT_ID,5001465,null_ct/all_ct,null))))*100) EAT
,((1-decode(MAX(DECODE(BRANCH_UNIT_ID,5001882,null_ct/all_ct,null)),0,1,MAX(DECODE(BRANCH_UNIT_ID,5001882,null_ct/all_ct,null))))*100) IES
,((1-decode(MAX(DECODE(BRANCH_UNIT_ID,5000191,null_ct/all_ct,null)),0,1,MAX(DECODE(BRANCH_UNIT_ID,5000191,null_ct/all_ct,null))))*100) ESS
,((1-decode(MAX(DECODE(BRANCH_UNIT_ID,5001817,null_ct/all_ct,null)),0,1,MAX(DECODE(BRANCH_UNIT_ID,5001817,null_ct/all_ct,null))))*100) AMP
,((1-decode(MAX(DECODE(BRANCH_UNIT_ID,5002153,null_ct/all_ct,null)),0,1,MAX(DECODE(BRANCH_UNIT_ID,5002153,null_ct/all_ct,null))))*100) HOS
,((1-decode(MAX(DECODE(BRANCH_UNIT_ID,5000193,null_ct/all_ct,null)),0,1,MAX(DECODE(BRANCH_UNIT_ID,5000193,null_ct/all_ct,null))))*100) PandP
,(MAX(DECODE(BRANCH_UNIT_ID,5000200,BRANCH_UNIT_ID,null))) BRANCH_ID_DSS
,(MAX(DECODE(BRANCH_UNIT_ID,5000190,BRANCH_UNIT_ID,null))) BRANCH_ID_CCS
,(MAX(DECODE(BRANCH_UNIT_ID,5000192,BRANCH_UNIT_ID,null))) BRANCH_ID_INF
,(MAX(DECODE(BRANCH_UNIT_ID,5001233,BRANCH_UNIT_ID,null))) BRANCH_ID_CTA
,(MAX(DECODE(BRANCH_UNIT_ID,5000841,BRANCH_UNIT_ID,null))) BRANCH_ID_MEDPLUS
,(MAX(DECODE(BRANCH_UNIT_ID,5001465,BRANCH_UNIT_ID,null))) BRANCH_ID_EAT
,(MAX(DECODE(BRANCH_UNIT_ID,5001882,BRANCH_UNIT_ID,null))) BRANCH_ID_IES
,(MAX(DECODE(BRANCH_UNIT_ID,5000191,BRANCH_UNIT_ID,null))) BRANCH_ID_ESS
,(MAX(DECODE(BRANCH_UNIT_ID,5001817,BRANCH_UNIT_ID,null))) BRANCH_ID_AMP
,(MAX(DECODE(BRANCH_UNIT_ID,5002153,BRANCH_UNIT_ID,null))) BRANCH_ID_HOS
,(MAX(DECODE(BRANCH_UNIT_ID,5000193,BRANCH_UNIT_ID,null))) BRANCH_ID_PandP
From
(
SELECT SUBPAGE, SUBPAGE_ID, BRANCH_UNIT_ID, SUM(DECODE(ATTRIB_NOT_COMPLETE,0,0,1)) NULL_CT, COUNT(1) ALL_CT
FROM (
/*SUBPAGES UNIONED (RELEASE CANDIDATE)*/
SELECT 'Application Self Assessment' subpage, 5134000 subpage_id,b.code app_id
,NVL2(ar2_breadth_of_usage,0,1)+NVL2(ar2_bus_criticality,0,1)+NVL2(ar2_comp_advantage,0,1)+NVL2(ar2_cur_effective,0,1)+NVL2(ar3_avai_reliability,0,1)+NVL2(ar3_complexity,0,1)+NVL2(ar3_cro_func_int,0,1)+NVL2(ar3_curr_func_adeq,0,1)+NVL2(ar3_ease_use_resp,0,1)+NVL2(ar3_flexibility_fac,0,1)+NVL2(ar3_flexibility_tac,0,1)+NVL2(ar3_fu_func_adeq,0,1)+NVL2(ar3_help_doc_support,0,1)+NVL2(ar3_info_ad_curr,0,1)+NVL2(ar3_info_adq,0,1)+NVL2(ar3_inter_oper,0,1)+NVL2(ar3_maintainability,0,1)+NVL2(ar3_operability,0,1)+NVL2(ar3_performance,0,1)+NVL2(ar3_relia_avail,0,1)+NVL2(ar3_scalability,0,1)+NVL2(ar3_security,0,1)+NVL2(ar3_security_tac,0,1)+NVL2(ar3_training,0,1) ATTRIB_NOT_COMPLETE /*0 means false*/
,OU.ID obs_id, E.BRANCH_UNIT_ID
FROM ODF_CA_APPLICATION a,INV_INVESTMENTS b,PRJ_OBS_ASSOCIATIONS obs,PRJ_OBS_UNITS ou,PRJ_OBS_UNITS_FLAT e WHERE a.id = b.id
AND b.is_active = 1 AND a.retirement_date is null AND b.odf_object_code = 'application' AND OU.TYPE_ID = 5000009 /*FUNCTIONAL OBS*/ AND A.ID = OBS.RECORD_ID
AND OBS.UNIT_ID = OU.ID AND OU.ID = E.UNIT_ID
-- AND ( E.BRANCH_UNIT_ID IN
-- (SELECT ID FROM PRJ_OBS_UNITS WHERE PARENT_ID = 5000188 /*IT Units*/)
-- OR E.BRANCH_UNIT_ID IN
-- (5000841,5001882 /*medplus,insurance - CLA removed Spec Lab on 11-6-2012*/))


UNION SELECT 'General' subpage, 5105002 subpage_id,b.code app_id
,F.ATTRIB_NOT_COMPLETE+NVL2(ar2_application_arc2,0,1)+NVL2(ar2_green_zone,0,1)+NVL2(ar2_required_uptime,0,1)+NVL2(ar2_security_admin2,0,1)+NVL2(availability,0,1)+NVL2(contact_it,0,1)+NVL2(data_access_levels,0,1)+NVL2(external_accessibili,0,1)+NVL2(lifecycle_status,0,1)+NVL2(origin,0,1)+NVL2(other_hosting,0,1)+NVL2(other_origin,0,1)+NVL2(other_processes,0,1)+NVL2(other_user_groups,0,1)+NVL2(owner_business,0,1)+NVL2(production_month_yea,0,1)+NVL2(registration_date,0,1)+NVL2(release_cycle,0,1) ATTRIB_NOT_COMPLETE /*0 means false*/
,OU.ID obs_id, E.BRANCH_UNIT_ID
FROM ODF_CA_APPLICATION a,INV_INVESTMENTS b,PRJ_OBS_ASSOCIATIONS obs,PRJ_OBS_UNITS ou,PRJ_OBS_UNITS_FLAT e,
(/* Formatted on 1/21/2010 2:57:26 PM (QP5 v5.115.810.9015) */
SELECT a.id,NVL2 (b.pk_id, 0, 1) ATTRIB_NOT_COMPLETE
FROM INV_INVESTMENTS a,(SELECT pk_id FROM ODF_MULTI_VALUED_LOOKUPS a
WHERE A.ATTRIBUTE in ('ar2_line_of_business','core_processes_kim3','host_kim3','user_group_proc_kim3')
AND OBJECT = 'application' GROUP BY pk_id) b WHERE a.id = b.pk_id(+) AND A.ODF_OBJECT_CODE = 'application'
) f
WHERE a.id = b.id
AND b.is_active = 1 AND a.retirement_date is null AND b.odf_object_code = 'application' AND OU.TYPE_ID = 5000009 /*FUNCTIONAL OBS*/ AND A.ID = OBS.RECORD_ID
AND OBS.UNIT_ID = OU.ID AND OU.ID = E.UNIT_ID
-- AND ( E.BRANCH_UNIT_ID IN
-- (SELECT ID FROM PRJ_OBS_UNITS WHERE PARENT_ID = 5000188 /*IT Units*/)
-- OR E.BRANCH_UNIT_ID IN
-- (5000841,5001882 /*medplus,insurance - CLA removed Spec Lab on 11-6-2012*/))

AND f.id = a.id

UNION SELECT 'Risk' subpage, 5134002 subpage_id,b.code app_id
,NVL2(ar2_custom_level,0,1)+NVL2(ar2_database_size,0,1)+NVL2(ar2_nbr_concur_users,0,1)+NVL2(ar2_nbr_indvid_users,0,1)+NVL2(ar2_trans_volume,0,1)+NVL2(ar2_transaction_size,0,1)+NVL2(ar3_ccn_data,0,1)+NVL2(ar3_phi_data,0,1)+NVL2(ar3_pii_data,0,1)+NVL2(ar3_ssn_data,0,1)+NVL2(ar3_subject_to_sox,0,1)+NVL2(qd_it_loto,0,1)+NVL2(qd_it_rgoc,0,1)+NVL2(qd_it_osmls1,0,1)+NVL2(qd_it_osmls2,0,1)+NVL2(qd_it_osmt1,0,1)+NVL2(qd_it_omst2,0,1)+NVL2(qd_it_itatssrfti,0,1)+NVL2(qd_it_iwtabufrrbtcrr,0,1)+NVL2(qd_it_doodios,0,1)+NVL2(qd_it_dtahapfdfeol,0,1) ATTRIB_NOT_COMPLETE /*0 means false*/
,OU.ID obs_id, E.BRANCH_UNIT_ID
FROM ODF_CA_APPLICATION a,INV_INVESTMENTS b,PRJ_OBS_ASSOCIATIONS obs,PRJ_OBS_UNITS ou,PRJ_OBS_UNITS_FLAT e WHERE a.id = b.id
AND b.is_active = 1 AND a.retirement_date is null AND b.odf_object_code = 'application' AND OU.TYPE_ID = 5000009 /*FUNCTIONAL OBS*/ AND A.ID = OBS.RECORD_ID
AND OBS.UNIT_ID = OU.ID AND OU.ID = E.UNIT_ID AND( E.BRANCH_UNIT_ID IN (SELECT ID FROM PRJ_OBS_UNITS WHERE PARENT_ID = 5000188 /*IT Units*/)
OR E.BRANCH_UNIT_ID IN (5000841,5001882 /*medplus,insurance*/))

UNION SELECT 'Technology' subpage, 5134004 subpage_id,b.code app_id
,NVL2(ar2_app_intgr_adaptr,0,1)+NVL2(ar2_app_platform,0,1)+NVL2(ar2_app_server,0,1)+NVL2(ar2_backup_recvry,0,1)+NVL2(ar2_bi_auto_notify,0,1)+NVL2(ar2_bus_act_monitor,0,1)+NVL2(ar2_bus_intel_suite,0,1)+NVL2(ar2_bus_process_mgmt,0,1)+NVL2(ar2_bus_rules_engine,0,1)+NVL2(ar2_business_svcs,0,1)+NVL2(ar2_client_os,0,1)+NVL2(ar2_confidentiality,0,1)+NVL2(ar2_cont_syndication,0,1)+NVL2(ar2_content_mgmt,0,1)+NVL2(ar2_data_acc_mdlwr,0,1)+NVL2(ar2_data_analysis,0,1)+NVL2(ar2_data_mining,0,1)+NVL2(ar2_data_sync,0,1)+NVL2(ar2_data_visual,0,1)+NVL2(ar2_datastore,0,1)+NVL2(ar2_db_connectivity,0,1)+NVL2(ar2_document_imaging,0,1)+NVL2(ar2_edi,0,1)+NVL2(ar2_ent_app_intgr,0,1)+NVL2(ar2_ent_info_portal,0,1)+NVL2(ar2_etl,0,1)+NVL2(ar2_fax_server,0,1)+NVL2(ar2_file_transfer,0,1)+NVL2(ar2_frameworks_libs,0,1)+NVL2(ar2_ftp_client,0,1)+NVL2(ar2_groupware,0,1)+NVL2(ar2_hdwr_plat_clntui,0,1)+NVL2(ar2_hdwr_plfrm_app,0,1)+NVL2(ar2_hdwr_plfrm_db,0,1)+NVL2(ar2_help,0,1)+NVL2(ar2_id_and_auth,0,1)+NVL2(ar2_interface_engine,0,1)+NVL2(ar2_job_scheduler,0,1)+NVL2(ar2_load_balancing,0,1)+NVL2(ar2_master_data_mgmt,0,1)+NVL2(ar2_modeling_diagram,0,1)+NVL2(ar2_msg_orient_midwr,0,1)+NVL2(ar2_msging_stds,0,1)+NVL2(ar2_other_bi_data,0,1)+NVL2(ar2_other_clientware,0,1)+NVL2(ar2_other_serverware,0,1)+NVL2(ar2_othr_ap_lfcy_mgt,0,1)+NVL2(ar2_othr_ext_intf_in,0,1)+NVL2(ar2_othr_ext_intf_ot,0,1)+NVL2(ar2_out_components,0,1)+NVL2(ar2_package_deploy,0,1)+NVL2(ar2_peripherals,0,1)+NVL2(ar2_platform,0,1)+NVL2(ar2_problem_mgmt,0,1)+NVL2(ar2_processors,0,1)+NVL2(ar2_progrm_language,0,1)+NVL2(ar2_project_mgmt,0,1)+NVL2(ar2_protocol_stacks,0,1)+NVL2(ar2_protocols,0,1)+NVL2(ar2_qrying_reporting,0,1)+NVL2(ar2_query_language,0,1)+NVL2(ar2_remote_access,0,1)+NVL2(ar2_remote_control,0,1)+NVL2(ar2_req_analysis,0,1)+NVL2(ar2_script_language,0,1)+NVL2(ar2_search_engine,0,1)+NVL2(ar2_server_os,0,1)+NVL2(ar2_soft_config_mgmt,0,1)+NVL2(ar2_software_testing,0,1)+NVL2(ar2_systems_mgmt,0,1)+NVL2(ar2_utilities,0,1)+NVL2(ar2_web_browsers,0,1)+NVL2(ar2_web_server,0,1)+NVL2(ar2_web_server_pages,0,1)+NVL2(ar2_web_svc_framew,0,1)+NVL2(ar2_web_svc_stds,0,1)+NVL2(ar2_website_anlytics,0,1)+NVL2(partition_code,0,1)+NVL2(qd_it_pw_reset,0,1) ATTRIB_NOT_COMPLETE /*0 means false*/
,OU.ID obs_id, E.BRANCH_UNIT_ID
FROM ODF_CA_APPLICATION a,INV_INVESTMENTS b,PRJ_OBS_ASSOCIATIONS obs,PRJ_OBS_UNITS ou,PRJ_OBS_UNITS_FLAT e WHERE a.id = b.id
AND b.is_active = 1 AND a.retirement_date is null AND b.odf_object_code = 'application' AND OU.TYPE_ID = 5000009 /*FUNCTIONAL OBS*/ AND A.ID = OBS.RECORD_ID
AND OBS.UNIT_ID = OU.ID AND OU.ID = E.UNIT_ID AND( E.BRANCH_UNIT_ID IN (SELECT ID FROM PRJ_OBS_UNITS WHERE PARENT_ID = 5000188 /*IT Units*/)
OR E.BRANCH_UNIT_ID IN (5000841,5001882 /*medplus,insurance*/))
) X
GROUP BY SUBPAGE, SUBPAGE_ID, BRANCH_UNIT_ID

) SUBX
GROUP BY SUBPAGE, SUBPAGE_ID


) sub1
WHERE 1=1
AND @FILTER@

Outcomes