Hi using Oracle 10g, I've pasted a simple simulation below ... I would like to insert a default value of '42' for the province 'AB', this is only one set of records so for all products that don't exist in all provinces, would like to create a row with the corresponding province on the right join. Hope this makes sense...
CREATE TABLE TBL_TEST1
(PROD_ID VARCHAR2(10), PROVINCE VARCHAR2(2), PRICE NUMBER);
INSERT ALL
INTO TBL_TEST1 (PROD_ID, PROVINCE, PRICE) VALUES ('42', 'BC', 178)
INTO TBL_TEST1 (PROD_ID, PROVINCE, PRICE) VALUES ('42', 'MT', 178)
INTO TBL_TEST1 (PROD_ID, PROVINCE, PRICE) VALUES ('42', 'NB', 178)
INTO TBL_TEST1 (PROD_ID, PROVINCE, PRICE) VALUES ('42', 'NF', 178)
INTO TBL_TEST1 (PROD_ID, PROVINCE, PRICE) VALUES ('42', 'NS', 178)
INTO TBL_TEST1 (PROD_ID, PROVINCE, PRICE) VALUES ('42', 'ON', 178)
INTO TBL_TEST1 (PROD_ID, PROVINCE, PRICE) VALUES ('42', 'PI', 178)
INTO TBL_TEST1 (PROD_ID, PROVINCE, PRICE) VALUES ('42', 'SK', 178)
SELECT * FROM DUAL;
--SELECT * FROM TBL_TEST1
CREATE TABLE TBL_PROV
(PROV VARCHAR2(2));
INSERT ALL
INTO TBL_PROV (PROV) VALUES ('AB')
INTO TBL_PROV (PROV) VALUES ('BC')
INTO TBL_PROV (PROV) VALUES ('MT')
INTO TBL_PROV (PROV) VALUES ('NB')
INTO TBL_PROV (PROV) VALUES ('NF')
INTO TBL_PROV (PROV) VALUES ('NS')
INTO TBL_PROV (PROV) VALUES ('ON')
INTO TBL_PROV (PROV) VALUES ('PI')
INTO TBL_PROV (PROV) VALUES ('SK')
SELECT * FROM DUAL;
--SELECT * FROM TBL_PROV;
SELECT
A.PROD_ID
, COALESCE(A.PROVINCE, B.PROV)
, NVL(A.PRICE,0)
FROM
TBL_TEST1 A
RIGHT OUTER JOIN
TBL_PROV B
ON A.PROVINCE = B.PROV;