View Question

[kwil38]

I'm attemping (newbie) to create a view with the following statement:

CREATE OR REPLACE VIEW vw_web_registrations AS
SELECT addr.address.house,
addr.address.fst_dir,
addr.address.street,
addr.address.street_suffix,
addr.address.snd_dir,
addr.address.unit,
addr.address.zip,
addr.phone.area_code,
addr.phone.phone_number
FROM addr.address,
addr.household,
addr.phone
WHERE addr.address.id = addr.phone.household_id
AND addr.household.household = addr.phone.household_id

The area code and phone_number fields are defined to not allow null values in the addr.phone table, however I would like null values to be possible in the view (without making any changes to the addr.phone table). Is this possible?

Thanks in advance!

 

[eagertotry]

It seems you want to list all addresses whether or not there is a corresponding phone#. Outer join should allow you to do that:

CREATE OR REPLACE VIEW vw_web_registrations AS
   SELECT addr.address.house,
          addr.address.fst_dir,
          addr.address.street,
          addr.address.street_suffix,
          addr.address.snd_dir,
          addr.address.unit,
          addr.address.zip,
          addr.phone.area_code,
          addr.phone.phone_number
   FROM addr.address,
        addr.household,
        addr.phone
   WHERE addr.address.id = addr.phone.household_id (+)
     AND addr.household.household = addr.phone.household_id (+)

HTH

 

[kwil38]

Thanks - that's exactly what I'm trying to do.