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