Hello all!
I am needing some help with a query to join some tables together. This is returning the a row if all values have a match. I know for a fact that the sub query will not always have a match with the query and I would like to have a Null returned if no match is found. I had someone suggest the but that doesn’t seem to work. Help!
Select SP.SVC_PT_ID, SP.DEVICE_ID, DV.MFG_SERIAL_NUM, dc.name, substr(dc.name,26), ctpt.CTPT_name, ctpt.CTPT_multipler, ctpt.CTPT_ID
From EIP.SVC_PT_DEVICE_REL SP,
eip.device\_class dc,
EIP.DEVICE DV,
(Select SP.SVC_PT_ID ctpt_scv_pt_id, SP.DEVICE_ID CTPT_ID, dc.name CTPT_name, substr(dc.name,26) CTPT_multipler
From EIP.SVC_PT_DEVICE_REL SP,
eip.device\_class dc,
EIP.DEVICE DV
Where SP.EFF_START_TIME <= '30-SEP-17'
and SP.EFF_END_TIME is null
and dc.id = DV.device_class_id
and DV.ID = SP.DEVICE_ID
and dc.name like '%CT/PT%'
ORDER BY SP.SVC_PT_ID) ctpt
Where SP.EFF_START_TIME <= '30-SEP-17'
and SP.EFF_END_TIME is null
and dc.id = DV.device_class_id
and DV.ID = SP.DEVICE_ID
and dc.name not like '%CT/PT%'
and SP.SVC_PT_ID = ctpt.ctpt_scv_pt_id(+)
ORDER BY SP.SVC_PT_ID;
