Home » SQL & PL/SQL » SQL & PL/SQL » View is not able to execute successfully because of using (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| View is not able to execute successfully because of using [message #636982] |
Thu, 07 May 2015 02:36  |
 |
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Dear All,
Please help for following view executed successfully in oracle11g
CREATE OR REPLACE FORCE VIEW ABC (access_number,
fault_code,
no_of_repeated_faults,
cpe_serial_num,
rtts_tt_count,
node_name,
equipment_number
----- speed_profile
)
AS
SELECT access_number, fault_code, fault_count AS no_of_repeated_faults,
cpe_serial_num, rtts_tt_count, node_name, equipment_number
----- speed_profile
FROM (SELECT subxtl.access_number, fmssfx.main_y_n, fmssfx.fault_type,
fmssfx.effective_date, blsubi.service_type,
subxtl.service_code, subxtl.service_number,
blusoc.item_number,
TRIM
((SELECT ov_get_fault_code_fn (subxtl.access_number)
FROM DUAL)
) AS fault_code,
TRIM
((SELECT ov_get_rep_fault_count_fn (subxtl.access_number)
FROM DUAL)
) fault_count,
(SELECT modem_serial_#
FROM dm_icms_ll.service_mastr_eqmpt_plmode00
WHERE expiry_date > SYSDATE
AND access_number = subxtl.access_number
AND service_code = fmssfx.service_code
AND ROWNUM < 2) AS cpe_serial_num,
TRIM
((SELECT SUBSTR
(ov_get_node_en_tt_fn (subxtl.access_number),
1,
INSTR
(ov_get_node_en_tt_fn
(subxtl.access_number),
'|',
1,
1
)
- 1
)
FROM DUAL)
) rtts_tt_count,
TRIM
((SELECT SUBSTR
(ov_get_node_en_tt_fn (subxtl.access_number),
( INSTR
(ov_get_node_en_tt_fn
(subxtl.access_number),
'|',
1,
2
)
+ 1
)
)
FROM DUAL)
) node_name,
TRIM
((SELECT SUBSTR
(SUBSTR
(ov_get_node_en_tt_fn
(subxtl.access_number),
INSTR
(ov_get_node_en_tt_fn
(subxtl.access_number),
'|',
1,
1
)
+ 1
),
1,
INSTR
(SUBSTR
(ov_get_node_en_tt_fn
(subxtl.access_number),
INSTR
(ov_get_node_en_tt_fn
(subxtl.access_number),
'|',
1,
1
)
+ 1
),
'|',
1,
1
)
- 1
)
FROM DUAL)
) AS equipment_number,
(SELECT dsl_speed
FROM (SELECT dsl_speed,
ROW_NUMBER () OVER (PARTITION BY access_number ORDER BY creation_time_stamp DESC) rnk
FROM dm_icms_ll.dcp_resend_req_sadcpl
WHERE activation_type IN ('N', 'R', 'U')
AND access_number = subxtl.access_number)
WHERE rnk = 1) AS speed_profile,
DENSE_RANK () OVER (PARTITION BY subxtl.access_number ORDER BY (CASE
WHEN fmssfx.main_y_n = 'Y'
THEN 1
ELSE 0
END) DESC ,
fmssfx.effective_date ASC) AS denserank
FROM dm_icms_ll.customer_services_ll_subxtl subxtl,
dm_icms_ll.fault_code_relation_fmssfx00 fmssfx,
dm_icms_ll.service_master_blsubi blsubi,
dm_icms_ll.service_equipment_blusoc blusoc
WHERE subxtl.disconnection_date > SYSDATE
AND blsubi.service_number = subxtl.service_number
AND blsubi.service_code = subxtl.service_code
AND blsubi.expiry_date > SYSDATE
AND fmssfx.service_code = 'TL'
AND fmssfx.service_type = blsubi.service_type
AND blusoc.service_code = subxtl.service_code
AND blusoc.service_number = subxtl.service_number
AND fmssfx.s_e_item = blusoc.item_number
--AND fmssfx.main_y_n(+) = 'Y'
AND blusoc.out_date > SYSDATE
AND fmssfx.fault_type =
(CASE
WHEN EXISTS (
SELECT 1
FROM dm_icms_ll.service_equipment_blusoc
WHERE service_number = blusoc.service_number
AND service_code = blusoc.service_code
AND item_number IN (
SELECT TRIM (product_id_s_e_of_stb1)
FROM dm_icms_ll.iptv_prdct_param_file_tvpse00
UNION
SELECT TRIM (product_id_s_e_of_stb2)
FROM dm_icms_ll.iptv_prdct_param_file_tvpse00
UNION
SELECT TRIM (product_id_s_e_of_stb3)
FROM dm_icms_ll.iptv_prdct_param_file_tvpse00
UNION
SELECT TRIM (product_id_s_e_of_stb4)
FROM dm_icms_ll.iptv_prdct_param_file_tvpse00
UNION
SELECT TRIM (product_id_s_e_of_stb5)
FROM dm_icms_ll.iptv_prdct_param_file_tvpse00)
AND out_date > SYSDATE)
THEN (CASE
WHEN EXISTS (
SELECT 1
FROM dm_icms_ll.net_miscell_switch_plmisw00
WHERE access_number =
subxtl.access_number)
THEN 'I'
WHEN EXISTS (
SELECT 1
FROM dm_icms_ll.plant_line_card_pltlcd pltlcd,
dm_icms_ll.plant_servicearea_master_plsam plsam
WHERE pltlcd.service_code =
subxtl.service_code
AND pltlcd.member_access_number =
subxtl.access_number
AND pltlcd.disconnect_date >
SYSDATE
AND pltlcd.building_name =
plsam.building_name
AND plsam.tax_district = 'FT')
THEN 'I'
WHEN EXISTS (
SELECT 1
FROM dm_icms_ll.plant_line_card_pltlcd pltlcd
WHERE pltlcd.service_code =
subxtl.service_code
AND pltlcd.member_access_number =
subxtl.access_number
AND pltlcd.service_type =
'VDSL'
AND pltlcd.disconnect_date >
SYSDATE)
THEN 'I'
END
)
ELSE (CASE
WHEN ( EXISTS (
SELECT 1
FROM dm_icms_ll.net_miscell_switch_plmisw00
WHERE access_number =
subxtl.access_number)
AND EXISTS (
SELECT 1
FROM dm_icms_ll.plant_line_card_pltlcd pltlcd
WHERE pltlcd.service_code =
subxtl.service_code
AND pltlcd.member_access_number =
subxtl.access_number
AND pltlcd.service_type =
'VDSL'
AND pltlcd.disconnect_date >
SYSDATE)
)
THEN 'V'
WHEN EXISTS (
SELECT 1
FROM dm_icms_ll.net_miscell_switch_plmisw00
WHERE access_number =
subxtl.access_number)
AND EXISTS (
SELECT 1
FROM dm_icms_ll.plant_line_card_pltlcd pltlcd
WHERE pltlcd.service_code =
subxtl.service_code
AND pltlcd.member_access_number =
subxtl.access_number
AND pltlcd.service_type <> 'VDSL'
AND pltlcd.disconnect_date >
SYSDATE)
AND EXISTS (
SELECT 1
FROM dm_icms_ll.dsl_se_speed_prmtr_sadqsc sadqsc,
dm_icms_ll.service_equipment_blusoc blusoc_in
WHERE blusoc_in.service_number =
subxtl.service_number
AND blusoc_in.service_code =
subxtl.service_code
AND blusoc_in.item_number =
sadqsc.s_e_item_code
AND blusoc.service_code =
sadqsc.service_code
AND speed_s_e = 'Y')
THEN 'D'
ELSE 'S'
END
)
END
))
WHERE 1 = 1 AND denserank = 1;
This view is getting failed because we are using logic :
(SELECT dsl_speed
FROM (SELECT dsl_speed,
ROW_NUMBER () OVER (PARTITION BY access_number ORDER BY creation_time_stamp DESC) rnk
FROM dm_icms_ll.dcp_resend_req_sadcpl
WHERE activation_type IN ('N', 'R', 'U')
AND access_number = subxtl.access_number)
WHERE rnk = 1) AS speed_profile,
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: View is not able to execute successfully because of using [message #636991 is a reply to message #636990] |
Thu, 07 May 2015 03:09  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
My DB doesn't know what the error is:
oerr This view is getting failed because we are using logic
Usage: oerr facility error
Facility is identified by the prefix string in the error message.
For example, if you get ORA-7300, "ora" is the facility and "7300"
is the error. So you should type "oerr ora 7300".
If you get LCD-111, type "oerr lcd 111", and so on.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 03:19:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
