Oracle SQL WITH Clause : a column may not be outer-joined to a subquery

Question

I want to use with clause in order to shorten column names which will be used for calculations

with Calc as(
SELECT entries.person_id,

MAX(DECODE(elements.element_name,'Basic Salary',values.screen_entry_value,0))/12 Salary,
MAX(DECODE(elements.element_name,'Mobile Allowance',values.screen_entry_value,0)) Mobile
FROM entries

JOIN values ON values.ELEMENT_ENTRY_ID = entries.ELEMENT_ENTRY_ID
JOIN elements ON elements.ELEMENT_TYPE_ID = entries.ELEMENT_TYPE_ID
JOIN value_types ON value_types.INPUT_VALUE_ID = values.INPUT_VALUE_ID
AND value_types.ELEMENT_TYPE_ID = entries.ELEMENT_TYPE_ID

WHERE elements.language = 'US'
AND elements.element_name IN (
'Mobile Allowance',
'Transportation Allowance',
'Housing Allowance',
'Basic Salary'
)
AND value_types.base_name = 'Amount'

GROUP BY entries.person_id

)

select Salary, Mobile 

from persons 

JOIN Calc ON Calc.person_id = persons.person_id

whenever I add with clause block of code to my SQL query it gives error: a column may not be outer-joined to a subquery, I'm not even using outer join, what's the issue?

Answer 1

Didn't test anything (just corrected the code) - try it like this...

WITH 
    calc AS 
        (   Select 
                e.PERSON_ID,
                MAX(DECODE(elm.ELEMENT_NAME,'Basic Salary', v.SCREEN_ENTRY_VALUE, 0)) / 12 "SALARY",
                MAX(DECODE(elm.ELEMENT_NAME,'Mobile Allowance', v.SCREEN_ENTRY_VALUE, 0)) "MOBILE"
            From 
                ENTRIES e
            Inner Join 
                VALUES_TABLE v ON(v.ELEMENT_ENTRY_ID = e.ELEMENT_ENTRY_ID)      -- values is reserved word - check the spelling of your table name
            Inner Join 
                ELEMENTS elm ON(elm.ELEMENT_TYPE_ID = e.ELEMENT_TYPE_ID)
            Inner Join 
                VALUE_TYPES vt ON(vt.INPUT_VALUE_ID = v.INPUT_VALUE_ID AND vt.ELEMENT_TYPE_ID = e.ELEMENT_TYPE_ID)
            Where 
                elm.LANGUAGE = 'US' AND 
                elm.ELEMENT_NAME IN('Mobile Allowance', 'Transportation Allowance', 'Housing Allowance', 'Basic Salary') AND 
                vt.BASE_NAME = 'Amount'
            Group By e.PERSON_ID
        )
SELECT      SALARY, MOBILE 
FROM        PERSONS p
INNER JOIN  calc c ON(c.PERSON_ID = p.PERSON_ID)