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

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?

CodePudding user response：

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)

CodePudding user response：

It looks like you are trying to use the WITH clause to define a common table expression (CTE) that you can use in the rest of your query. The WITH clause allows you to define a named temporary result set that you can reference within your SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

The error you are seeing, "a column may not be outer-joined to a subquery," suggests that you are trying to outer join a column to a subquery in your query. An outer join is a type of join that retrieves all rows from at least one of the tables in the join, even if there are no matching rows in the other table. In Oracle, you can use the ( ) operator to specify an outer join, like this:

SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id ( )

However, it looks like you are not using outer joins in your query. It's possible that the error is being caused by a different issue, such as a syntax error or a missing parenthesis. It would be helpful to see the full query, including any error messages you are seeing, to help identify the problem. If you can provide more context, I might be able to offer more specific advice on how to resolve the issue.