hi ,
db 10g rel2 ,
i have the following view and it's working fine , and i can select * from it , and select other columns , but when i try to
" select factor from it , it show ( " factor " invalid identifier ) , do not know why ?
- when i " select * " it retrieves the factor column normally .
- is " factor " a reserved word or something ?
CREATE OR REPLACE FORCE VIEW STOCK_BY_SERIAL_AS_LINES
(SERIAL, ITEM_ID, EXPIRY_DATE, QTY, UNIT_ID,
UNIT_NAME, "factor")
AS
WITH T1 AS
(SELECT ITEM_ID,
UNIT_ID,
unit_name ,
FACTOR,
ROW_NUMBER() OVER(PARTITION BY ITEM_ID ORDER BY FACTOR DESC) UNIT_WEIGHT
FROM ITEM_UNITS , units u
where item_units.unit_id = u.unit_code
),
T2 AS
(SELECT ID.SERIAL,
ID.ITEM_ID,
ID.EXPIRY_DATE,
ID.QTY,
T1.FACTOR,
T1.UNIT_WEIGHT,
T1.UNIT_ID , t1.unit_name
FROM ITEM_DETAIL ID,
T1
WHERE T1.ITEM_ID = ID.ITEM_ID
)
SELECT SERIAL,
ITEM_ID,
EXPIRY_DATE,
QTY,
UNIT_ID,unit_name, factor
FROM T2 MODEL RETURN UPDATED ROWS PARTITION BY(SERIAL,ITEM_ID,EXPIRY_DATE) DIMENSION BY(UNIT_WEIGHT) MEASURES(QTY,FACTOR,UNIT_ID,unit_name ,QTY REMAINING_QTY) RULES AUTOMATIC ORDER ( QTY[ANY]
ORDER BY UNIT_WEIGHT = TRUNC(REMAINING_QTY[CV()] / FACTOR[CV()]), REMAINING_QTY[UNIT_WEIGHT > 1]
ORDER BY UNIT_WEIGHT = MOD(REMAINING_QTY[CV() - 1],FACTOR[CV() - 1]) );