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?
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)