Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View - Zero Length not allowed (Oracle 11g)
| Materialized View - Zero Length not allowed [message #485455] |
Wed, 08 December 2010 04:44  |
|
|
Hi,
I am trying to create one Materialized view from a Select Statement and I am getting the following error
SQL Error: ORA-01723: zero-length columns are not allowed
I have checked the table and found nothing like varchar2(0) or char(0) , but the column in my select statment may get a Null value. Will it result in error like above ?
Normal View : I have created this because I think a materialized view would not be created if we have a Sub query.
CREATE OR REPLACE VIEW mx_test
AS
SELECT t."EXTN_SERVICE_REQUEST_NO",
t."EXTN_SERVICE_TYPE",
t."EXTN_SERVICE_LEVEL",
t."EXTN_REFERENCE_1",
t."EXTN_REFERENCE_2",
t."EXTN_REFERENCE_3",
t."EXTN_REFERENCE_4",
t."SHIPMENT_NO",
t."SHIP_DATE",
t."RECEIPT_NO",
t."RECEIPT_DATE",
t."COMPANY",
t."INSTRUCTION_TEXT",
t."DATE_RECV_COURIER_2",
t."INVOICE_NO",
t."ETA",
t."TRX_QTY",
t."DELIVERY_TYPE",
t."CASE_ID",
t."REVISION_NO",
t."BATCH_NO",
t."WEIGHT",
t."WEIGHT_UOM",
t."COUNTRY_OF_ORIGIN",
t."ITEM_ID",
t."ITEM_DESCRIPTION",
t."ASR_ID",
t."SERIAL_NO",
t."PRODUCT_CLASS",
t."LOT_NUMBER",
t."SHIPNODE_KEY",
t."RECEIVING_NODE",
t."ORDERED_QTY",
t."CUSTOMER_PO_NO",
t."UNIT_PRICE",
t."UNIT_COST",
t."DOCUMENT_TYPE",
t."ENTERPRISE_KEY",
t."ORDER_DATE",
t."BOL_NO",
t."COUNTRY",
t."OPEN_RECEIPT_FLAG",
t."RECEIPT_STATUS",
t."EXTN_OH",
t."INDICATOR",
t."MODIFYUSERID",
t."SITE_ID",
t."ORDER_HEADER_KEY",
t."SHIPMENT_LINE_NO",
t."STATUS" ,
i.extn_mat_class_code ,
j.description AS "SR Status"
FROM
(SELECT oh.extn_service_request_no ,
oh.extn_service_type ,
oh.extn_service_level ,
s.extn_reference_1 ,
s.extn_reference_2 ,
s.extn_reference_3 ,
s.extn_reference_4 ,
s.shipment_no ,
(
CASE
WHEN (oh.document_type = '0005')
THEN NULL
ELSE s.ship_date
END ) AS ship_date ,
rh.receipt_no ,
(
CASE
WHEN DECODE(rh.status_date, TO_DATE('01-JAN-00 00:00:00','dd-mon-yy hh24:mi:ss'),NULL,RH.STATUS_DATE)='01-JAN-00'
THEN NULL
ELSE rh.status_date
END ) AS receipt_date ,
pi.company ,
UDF_CONCAT_IND_DTL_VT(ol.order_line_key, ' ') AS instruction_text ,
(
CASE
WHEN DECODE(pad.date_recv_courier_2, TO_DATE('01-JAN-00 00:00:00','dd-mon-yy hh24:mi:ss'),NULL,PAD.DATE_RECV_COURIER_2)='01-JAN-00'
THEN NULL
ELSE pad.date_recv_courier_2
END ) AS date_recv_courier_2 ,
oi.invoice_no ,
(
CASE
WHEN DECODE(s.requested_delivery_date, TO_DATE('01-JAN-00 00:00:00','dd-mon-yy hh24:mi:ss'),NULL,s.requested_delivery_date)='01-JAN-00'
THEN NULL
ELSE s.requested_delivery_date
END ) AS ETA ,
(
CASE
WHEN rl.lpn_no <> ' '
AND rl.serial_no = ' '
THEN rl.quantity
WHEN rl.lpn_no <> ' '
AND rl.serial_no <> ' '
THEN 1
WHEN rl.lpn_no = ' '
AND rl.serial_no <> ' '
THEN 1
ELSE ordered_qty
END ) AS trx_qty ,
(SELECT code_short_description -----------------------------------------> view 1
FROM yfs_common_code
WHERE code_type = 'DEL_TYPE'
AND code_value = s.extn_delivery_type
AND organization_code = oh.enterprise_key
) AS delivery_type ,
rl.lpn_no AS case_id ,
rl.revision_no ,
rl.batch_no ,
s.total_weight AS weight ,
s.total_weight_uom AS weight_uom ,
rl.country_of_origin ,
ol.item_id ,
ol.item_description ,
(SELECT asr_id -------------------------------------------------------------------- > view 2
FROM yaf_item_vw ya
WHERE ya.item_id = ol.item_id
AND ya.uom = ol.uom
AND ya.organization_code = oh.enterprise_key
) AS asr_id ,
rl.serial_no ,
rl.product_class ,
rl.lot_number ,
ol.shipnode_key ,
ol.receiving_node,
NULL AS ordered_qty ,
ol.customer_po_no ,
ol.unit_price ,
ol.unit_cost ,
oh.document_type ,
oh.enterprise_key ,
(SELECT MIN(order_date) --------------------------------------------------------------------> view 3
FROM yfs_order_header
WHERE extn_service_request_no = oh.extn_service_request_no
AND SUBSTR(order_no, 1, 2) = 'SR'
) order_date ,
s.bol_no ,
pi.country ,
rh.open_receipt_flag ,
rh.status receipt_status ,
s.extn_oh ,
'INBOUND' AS indicator ,
oh.modifyuserid ,
ol.extn_site_id AS site_id ,
oh.order_header_key ,
sl.shipment_line_no ,
os.status
FROM yfs_shipment s ,
yfs_shipment_line sl ,
yfs_order_line ol ,
yfs_order_header oh ,
yfs_receipt_header rh ,
extn_yfs_pod_agent_details pad ,
yfs_person_info pi,
yfs_order_invoice oi ,
yfs_receipt_line rl ,
yaf_order_header_status_vw_vt os
WHERE s.shipment_key = sl.shipment_key
AND sl.order_line_key = ol.order_line_key
AND sl.order_header_key = ol.order_header_key
AND sl.order_header_key = oh.order_header_key
AND s.shipment_key = rh.shipment_key(+)
AND s.shipment_key = pad.shipment_key(+)
AND s.to_address_key = pi.person_info_key(+)
AND s.shipment_key = oi.shipment_key(+)
AND ol.order_line_key = rl.order_line_key(+)
AND rh.receipt_header_key = rl.receipt_header_key
AND oh.order_header_key = os.order_header_key(+)
AND NOT(NOT(oh.document_type = '0005')
AND NOT(oh.document_type = '0006'))
AND TRIM(oh.extn_service_request_no) != TRIM(oh.order_no)
UNION
SELECT oh.extn_service_request_no ,
oh.extn_service_type ,
oh.extn_service_level ,
s.extn_reference_1 ,
s.extn_reference_2 ,
s.extn_reference_3 ,
s.extn_reference_4 ,
s.shipment_no ,
s.ship_date ,
NULL AS RECEIPT_NO ,
NULL AS RECEIPT_DATE ,
pi.company ,
UDF_CONCAT_IND_DTL_VT(ol.order_line_key, ' ') AS instruction_text ,
(
CASE
WHEN DECODE(pad.date_recv_courier_2, TO_DATE('01-JAN-00 00:00:00','dd-mon-yy hh24:mi:ss'),NULL,PAD.DATE_RECV_COURIER_2)='01-JAN-00'
THEN NULL
ELSE pad.date_recv_courier_2
END ) AS date_recv_courier_2 ,
oi.invoice_no ,
(
CASE
WHEN DECODE(s.requested_delivery_date, TO_DATE('01-JAN-00 00:00:00','dd-mon-yy hh24:mi:ss'),NULL,s.requested_delivery_date)='01-JAN-00'
THEN NULL
ELSE s.requested_delivery_date
END ) AS ETA ,
CASE
WHEN sts.shipment_tag_serial_key <> ' '
THEN -sts.quantity
WHEN cd.shipment_container_key IS NULL
THEN -ol.ordered_qty
ELSE -cd.quantity
END AS trx_qty --modify 20081013
,
(SELECT code_short_description ----------------------------> view 1
FROM yfs_common_code
WHERE code_type = 'DEL_TYPE'
AND code_value = s.extn_delivery_type
AND organization_code = oh.enterprise_key
) AS delivery_type ,
sc.container_scm AS case_id ,
sts.revision_no ,
sts.batch_no ,
s.total_weight AS weight ,
s.total_weight_uom AS weight_uom ,
cd.country_of_origin ,
CASE
WHEN cd.shipment_container_key IS NULL
THEN ol.item_id
ELSE cd.item_id
END item_id --modify 20081013
,
ol.item_description ,
(SELECT asr_id -------------------------------------> view 2
FROM yaf_item_vw ya
WHERE ya.item_id = ol.item_id
AND ya.uom = ol.uom
AND ya.organization_code = oh.enterprise_key
) AS asr_id ,
sts.serial_no ,
cd.product_class ,
sts.lot_number ,
ol.shipnode_key ,
ol.receiving_node ,
NULL AS ordered_qty ,
ol.customer_po_no ,
ol.unit_price ,
ol.unit_cost ,
oh.document_type ,
oh.enterprise_key ,
(SELECT MIN(order_date) ---------------------------------------------------------> view 3
FROM yfs_order_header
WHERE extn_service_request_no = oh.extn_service_request_no
AND SUBSTR(order_no, 1, 2) = 'SR'
) order_date ,
s.bol_no ,
pi.country ,
NULL AS OPEN_RECEIPT_FLAG ,
NULL AS RECEIPT_STATUS ,
s.extn_oh ,
'OUTBOUND' AS indicator ,
oh.modifyuserid ,
ol.extn_site_id AS site_id ,
oh.order_header_key ,
sl.shipment_line_no ,
os.status
FROM yfs_shipment s ,
yfs_shipment_line sl ,
yfs_order_line ol ,
yfs_order_header oh,
extn_yfs_pod_agent_details pad ,
yfs_person_info pi ,
yfs_order_invoice oi ,
yfs_shipment_container sc ,
yfs_container_details cd ,
yfs_shipment_tag_serial sts ,
yaf_order_header_status_vw_vt os
WHERE s.shipment_key = sl.shipment_key
AND sl.order_line_key = ol.order_line_key
AND sl.order_header_key = ol.order_header_key
AND sl.order_header_key = oh.order_header_key
AND s.shipment_key = pad.shipment_key(+)
AND s.to_address_key = pi.person_info_key(+)
AND s.shipment_key = oi.shipment_key(+)
AND s.shipment_key = sc.shipment_key(+)
AND sc.shipment_container_key = cd.shipment_container_key(+)
AND cd.container_details_key = sts.container_detail_key(+)
AND (sc.shipment_key IS NULL
OR cd.order_line_key = ol.order_line_key)
AND oh.order_header_key = os.order_header_key(+)
AND NOT(NOT(oh.document_type = '0001')
AND NOT(oh.document_type = '0006'))
AND TRIM(oh.extn_service_request_no) != TRIM(oh.order_no)
) t ,
(SELECT item_id , ---------------------------------------------------- > view 4
extn_mat_class_code ,
O.ORGANIZATION_CODE
FROM yfs_item i,
yfs_organization o
WHERE i.organization_code = o.catalog_organization_code
) i,
(SELECT oh.order_header_key, ------------------------------------------ > view 5
oh.extn_service_request_no,
ys.status,
ys.description
FROM yfs_order_header oh,
yfs_order_release_status ors,
yfs_status ys,
yfs_pipeline yp
WHERE ys.status = ors.status
AND oh.order_header_key = ors.order_header_key(+)
AND ors.status = ys.status(+)
AND ys.process_type_key = yp.process_type_key(+)
AND yp.pipeline_key = ors.pipeline_key
AND ors.status <> '1400'
AND ors.status_quantity > 0
AND oh.document_type = '0009.ex'
)j
WHERE i.item_id = t.item_id
AND T.ENTERPRISE_KEY = I.ORGANIZATION_CODE
AND j.extn_service_request_no(+) = t.extn_service_request_no
Materialized View :
CREATE materialized VIEW mx_ashok
AS
SELECT * FROM mx_test
Error :
Error starting at line 1 in command:
CREATE materialized VIEW mx_ashok
AS
SELECT * FROM mx_test
Error at Command Line:3 Column:16
Error report:
SQL Error: ORA-01723: zero-length columns are not allowed
01723. 00000 - "zero-length columns are not allowed"
*Cause:
*Action:
Do I need to use NVLs for all columns which may result in NULL value ? please suggest.
|
|
|
|
|
|
| Re: Materialized View - Zero Length not allowed [message #485457 is a reply to message #485456] |
Wed, 08 December 2010 04:55  |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Cast would be better than NVL. The real problem here is your select statement has functions in it. Oracle stops being sure of the size of the column when you run it throught a function. Cast re-establishes what the size should be.
So use cast or you could create an empty table with the correct structure and then build the MV on top of it using the ON PREBUILT TABLE clause.
I'd also use the select statement from the view in the MV rather than select from the view.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Materialized View - Zero Length not allowed [message #485473 is a reply to message #485470] |
Wed, 08 December 2010 07:47 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
ashoka_bl wrote on Wed, 08 December 2010 14:21Cool..I even CASTed
case
when decode(rh.status_date, to_date('01-JAN-00 00:00:00','dd-mon-yy hh24:mi:ss'),cast( null as date)
,rh.status_date)='01-JAN-00'
THEN cast ( NULL as date)
ELSE rh.status_date
END ) AS receipt_date
Any concerns ??
Well, I am quite puzzled what is this condition in CASE statement supposed to check (with or without CAST to DATE).
DECODE now returns (with CAST) DATE value; without CAST, it returned VARCHAR2 (the data type of the first value - NULL has character type by default). '01-JAN-00' is VARCHAR2.
If '01-JAN-00' would be converted to DATE data type (only with CAST, but not necessarily), it might fail if NLS_DATE_FORMAT value does not fit its format. But, as DECODE will never return January, 1st 2000, this condition will always be FALSE.
Similar story will happen if the DECODE result would be converted to VARCHAR2 (this always happens without CAST). But then, dates January, 1st 1900 (1800, 1700, 2100) will evaluate this condition to TRUE. But, why not state it more transparently then?
By the way, year has four digits. Using all of them may avoid many obscurities and troubles when treating dates from different centuries.
|
|
|
|
| Re: Materialized View - Zero Length not allowed [message #485475 is a reply to message #485473] |
Wed, 08 December 2010 08:05 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
flyboy wrote on Wed, 08 December 2010 13:47If '01-JAN-00' would be converted to DATE data type (only with CAST, but not necessarily), it might fail if NLS_DATE_FORMAT value does not fit its format. But, as DECODE will never return January, 1st 2000, this condition will always be FALSE.
Actually the decode can return that if the status_date is 01-JAN-2000 with a time other than midnight.
I do agree that the general logic makes no sense though.
[Updated on: Wed, 08 December 2010 08:06] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Fri Jun 27 12:43:41 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
