I need to join two reference tables in the process to produce a final reference table. For reasons beyond my control, the reference fields in the 'ref_table' the nulls values could grow.
For example, attr5_name, attr4_name and their attribute values are nulls for now and in future, it may have nulls in attr3_name all the way to attr2_name perhaps.
What are the options to tackle unforeseeable nulls could grow in attr_name in the join I'm concerned with nulls. If the columns are null I want them to match on their actual values.
I have written a query with data examples as they are but could you please approve the proof of concept this is how you join multiple fields that could have nulls attributes in the reference tables.
WITH ref_table AS
(SELECT 'CPS0030277' AS tag, 'Class of Service' AS attr1_name, 'DCOS' AS attr1_val,
'Charge Zone' AS attr2_name, 'Z1' AS attr2_val, 'Access Speed' AS attr3_name,
'2 Mbps' AS attr3_val, 'Access Configuration' AS attr4_name,
'Fully Redundant' AS attr4_val, NULL AS attr5_name,
NULL AS attr5_val, '44PQ1' AS product_code,
2012.59 AS price
FROM dual
UNION
SELECT 'CPS0030277', 'Class of Service', 'RFC 6CoS', 'Charge Zone', 'Z1', 'Access Speed', '2 Mbps', 'Access Configuration', 'Fully Redundant', NULL, NULL, '44PQ1', 1051.59
FROM dual
UNION
SELECT 'CPS0032057', 'Access Speed', '100 Mbps', 'Management Level', 'Standard', 'Charge Zone', 'Z2', NULL, NULL, NULL, NULL, 'I2M100', 3012.59
FROM dual
),
ref_attributes AS
(SELECT 'CPS0030277' AS tag ,
'Class of Service' AS attr1 ,
'Charge Zone' AS attr2 ,
'Access Speed' AS attr3 ,
'Access Configuration' AS attr4 ,
NULL AS attr5
FROM DUAL
UNION
SELECT 'CPS0032057', 'Access Speed', 'Management Level', 'Charge Zone', NULL, NULL
FROM DUAL
) ,
GOT_MY_REF AS
(SELECT ref.attr1_val ,
ref.attr2_val ,
ref.attr3_val ,
ref.attr4_val ,
ref.attr5_val ,
ref.product_code ,
ref.price
FROM ref_attributes attr
JOIN ref_table ref
ON ref.tag = attr.tag
AND ( ref.attr1_name = attr.attr1
OR ref.attr1_name IS NULL )
AND ( ref.attr2_name = attr.attr2
OR ref.attr2_name IS NULL )
AND ( ref.attr3_name = attr.attr3
OR ref.attr3_name IS NULL )
AND (ref.attr4_name = attr.attr4
OR ref.attr4_name IS NULL )
AND ( ref.attr5_name = attr.attr5
OR ref.attr5_name IS NULL ) )
SELECT * FROM GOT_MY_REF;