SQL, Oracle Views, DB2/400
I'm trying to assign default values to VIEW columns defined for left joined columns or columns created by aggregate functions. I've tried the ALTER TABLE after the VIEW was created but get an error that my VIEW is not a TABLE and the ALTER VIEW is not supported by DB2/400 v5r3. 
Looking for relevant Development Whitepapers? Visit the SearchWinDevelopment.com Research Library.
I used “CREATE VIEW”. See example below:
When this VIEW is used in other queries with LEFT OUTER JOIN, I want GDSCQY and GDPQTY to default to 0 instead of NULL. I also want SHPWTYP to default to blank instead of NULL.
———————————————————————-
create view PDGRDDV12 as
select
GDLVL1, GDLVL2, WHFLOC, WHWTYP as SHPWTYP, GDITEM,
GDSTYL, GDCOLR, GDLOGO, GDSIZ#,
cast(sum(GDSCQY) as dec(7,0)) as GDSCQY,
cast(sum(GDPQTY) as dec(7,0)) as GDPQTY
from PDGRDDP
left outer join ICWAREP on GDWHSI=WHWHSI
where GDSTTS=’5′ or GDSTTS=’6′
group by GDLVL1, GDLVL2, WHFLOC, WHWTYP, GDITEM,
GDSTYL, GDCOLR, GDLOGO, GDSIZ#;
————————————————————————
0