I have this SQL which I have tested on Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production and Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production:
WITH PA_FUNCTIONS AS
(SELECT 'Burden Cost Revenue Account' function_name, 'GBR' function_code FROM DUAL UNION ALL
SELECT 'Invent. Cost Clearing Account' function_name, 'TIC' function_code FROM DUAL UNION ALL
SELECT 'Labor Cost Clearing Account' function_name, 'TLC' function_code FROM DUAL)
, PA_SEGMENT_RULE_PAIRINGS_ALL AS
(SELECT 'GBR' function_code, 0 segment_num FROM DUAL UNION ALL
SELECT 'GBR' function_code, 1 segment_num FROM DUAL UNION ALL
SELECT 'GBR' function_code, 2 segment_num FROM DUAL UNION ALL
SELECT 'GBR' function_code, 3 segment_num FROM DUAL UNION ALL
SELECT 'GBR' function_code, 4 segment_num FROM DUAL UNION ALL
SELECT 'GBR' function_code, 5 segment_num FROM DUAL UNION ALL
SELECT 'TIC' function_code, 0 segment_num FROM DUAL UNION ALL
SELECT 'TIC' function_code, 1 segment_num FROM DUAL UNION ALL
SELECT 'TIC' function_code, 2 segment_num FROM DUAL UNION ALL
SELECT 'TIC' function_code, 3 segment_num FROM DUAL UNION ALL
SELECT 'TIC' function_code, 4 segment_num FROM DUAL UNION ALL
SELECT 'TIC' function_code, 5 segment_num FROM DUAL)
SELECT PF.function_name
, COUNT(*)
FROM PA_FUNCTIONS PF
LEFT JOIN PA_SEGMENT_RULE_PAIRINGS_ALL PR ON PF.function_code = PR.FUNCTION_CODE
GROUP BY PF.function_name;
FUNCTION_NAME COUNT(*)
----------------------------- ----------
Burden Cost Revenue Account 6
Labor Cost Clearing Account 1
Invent. Cost Clearing Account 6
3 rows selected.
I can't work out why it doesn't return:
FUNCTION_NAME COUNT(*)
----------------------------- ----------
Burden Cost Revenue Account 6
Labor Cost Clearing Account 0
Invent. Cost Clearing Account 6
Am I missing something obvious - if there is no link in the PA_SEGMENT_RULE_PAIRINGS_ALL table to the "TLC" function_code, I can't see why the SQL returns a Count of 1 for that function_name from the PA_FUNCTIONS table?
Thanks