Assign default values to SQL VIEW columns

60 pts.
Tags:
DB2/400
Oracle Views
SQL
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try using “CREATE OR REPLACE VIEW…”

———– kccrosser —-
Each system has a different way of replacing views. Oracle uses “Create or Replace View”, and in some systems it may be easiest just to run a “Drop View xxx”, “Create View xxx” sequence.

As noted below, the “Coalesce” function will give you the desired results. Your view def with the coalesce functions inserted would look like the following. Note that the coalesce for SHPWTYP will return one blank character as coded, since you specified to return a “blank”.

———————————————————————-
create view PDGRDDV12 as
select
GDLVL1, GDLVL2, WHFLOC, Coalesce(WHWTYP,’ ‘) as SHPWTYP, GDITEM,
GDSTYL, GDCOLR, GDLOGO, GDSIZ#,
cast(sum(Coalesce(GDSCQY,0)) as dec(7,0)) as GDSCQY,
cast(sum(Coalesce(GDPQTY,0)) 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#;
————————————————————————

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Tgee
    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#; ------------------------------------------------------------------------
    60 pointsBadges:
    report
  • TomLiotta
    I want GDSCQY and GDPQTY to default to 0 instead of NULL. I also want SHPWTYP to default to blank instead of NULL. That seems similar to saying that you want the view to present false data to the consumer. I realize that that's a very poor way of wording it, but it seems troublesome to me. I would expect the consumer to want to make decisions about how to treat nulls. How could the consumer tell the difference between null and 0 or blank otherwise? If it's just that you want a specific view to do this, then use a function like COALESCE() in the view. Tom
    125,585 pointsBadges:
    report
  • Kccrosser
    I don't see this as an attempt to present false data. The view is summing and consolidating information, and it is possible that some source data fields may contain nulls. Treating a null field as a zero or a blank in a view is very common and can really simplify the code/reports that run against the view, as the downstream code then does not need to handle null values.
    3,830 pointsBadges:
    report
  • TomLiotta
    I don’t see this as an attempt to present false data. Neither do I in most cases, though it's still troubling. When nulls are allowed as valid states for a column, it ought to be for a meaningful reason (IMO). Replacing with zero/blank would change the meaning in such a case. But that's what functions such as COALESCE() are used for, so it seemed like an appropriate suggestion. Tom
    125,585 pointsBadges:
    report
  • Kccrosser
    As soon as you use a "left outer join", you are creating a case where a non-null field (that is - a field that must be non-null when a row of data exists) may be null in the query results. This looks like a item/warehouse query - if some items aren't found in a given warehouse, setting the apparent quantity fields to zero for those pairings rather than null makes for a more consistent result set, and allows the Sum function to work. (Generally Sum(null) will generate an error.)
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following