I was trying to create the following view ,
CREATE OR REPLACE FORCE VIEW "POS"."LACKS" ("SERIAL", "ITEM_ID", "ITEM_ENG_NAME", "MIN_QTY_FOR_DEMAND", "MIN_QTY_UNIT_ID")
AS
WITH item_units_plus AS
(SELECT item_id,
unit_id,
factor,
LEAD (factor, 1, 1E99) OVER ( PARTITION BY item_id ORDER BY factor ) AS next_factor,
ROW_NUMBER () OVER ( PARTITION BY item_id ORDER BY factor DESC ) AS rnk
FROM item_units
)
SELECT id.serial,
id.item_id,
i.item_eng_name,
i.min_qty_form_demand ,
i.min_qty_unit_id ,
SUBSTR ( SYS_CONNECT_BY_PATH ( TRUNC ( MOD ( id.qty, iup.next_factor ) / iup.factor )
|| ' '
|| u.unit_name, ', ' ), 3 ) AS qty
FROM item_detail id
JOIN items i
ON i.item_id = id.item_id
AND id.qty <> 0
AND id.qty <
(SELECT i.min_qty_form_demand * -- this is the error place , it should be i.min_qty_for_demand not form_demand .
(SELECT iu.factor
FROM item_units iu
WHERE iu.item_id = i.item_id
AND iu.unit_id = i.MIN_QTY_UNIT_ID
)
FROM items i
WHERE i.item_id = id.item_id
)
JOIN item_units_plus iup
ON iup.item_id = id.item_id
JOIN units u
ON u.unit_code = iup.unit_id
WHERE CONNECT_BY_ISLEAF = 1
AND id.expiry_date < TRUNC (SYSDATE)
START WITH iup.rnk = 1
CONNECT BY iup.rnk = PRIOR iup.rnk + 1
AND id.serial = PRIOR id.serial;
and it created successfully , although there is an " X " mark on it in the navigator , and when i tried to query the view , sql developer mentioned that the view has a compiling error .
sql developer did not point to the place of the error , and did not tell me what the error is .
while when i tried to execute the view again with Toad , it showed me the error place and the name of the column which causes the error ( there was a column name written wrong ) .