SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01799: a column may not be outer-joined to a subquery

jPcSDec 2 2015 — edited Dec 2 2015

I have the following query is based on the original SQL Server query

This way its work well

but when uncommented send me the following error

ORA-01799: a column may not be outer-joined to a subquery

01799. 00000 - "a column may not be outer-joined to a subquery"

*Cause: <expression>(+) <relop> (<subquery>) is not allowed.

*Action: Either remove the or make a view out of the subquery.

In V6 and before, the was just ignored in this case.

so I ask for help to guide me in my problem

SELECT CA.CategoryID ,

C.ParentID ,

C.CategoryLevel ,

A.AttributeID ,

COALESCE(AA.AliasName, A.AttributeName) AttributeName ,

A.InputTypeID ,

A.ValidationTypeID ,

A.IsUnique ,

A.IsRequired ,

CASE ca.AttributeID

WHEN 1 THEN cid.CategoryName

WHEN 9 THEN cid.MetaTitle

ELSE CAVN.AttributeValue

END NVARCHARValue ,

CASE ca.AttributeID

WHEN 2 THEN cid.DESCRIPTION

WHEN 3 THEN TO_CLOB(cid.ShortDescription)

WHEN 10 THEN TO_CLOB(cid.MetaKeyword)

WHEN 11 THEN TO_CLOB(cid.MetaDescription)

ELSE TO_CLOB(CAVT.AttributeValue)

END TEXTValue ,

CASE ca.AttributeID

WHEN 16 THEN c.IsshowInMenu

WHEN 17 THEN c.IsShowInSearch

WHEN 18 THEN c.IsShowInCatalog

WHEN 36 THEN c.IsService

ELSE TO_CHAR(CAVB.AttributeValue)

END BooleanValue ,

CAVI.AttributeValue INTValue ,

CASE ca.AttributeID

WHEN 19 THEN c.ActiveFrom

WHEN 20 THEN c.ActiveTo

ELSE NVL(TO_DATE(CAVDT.AttributeValue,'DD/MM/YY'), TO_DATE('01-JAN-10','DD/MM/YY'))

END DATEValue ,

CAVD.AttributeValue DECIMALValue ,

CASE ca.AttributeID

WHEN 12 THEN c.BaseImage

WHEN 21 THEN c.SmallImage

WHEN 22 THEN c.ThumbnailImage

ELSE CAVF.AttributeValue

END FILEValue ,

CASE c.IsService

WHEN 'true' THEN 10

WHEN 'false' THEN 9

ELSE NULL

END OPTIONValues

FROM Aspx_Category C

JOIN Aspx_CategoryInformationDetail cid

ON cid.CategoryID = c.CategoryID

AND cid.CultureName = CASE 'en-US'

WHEN ( SELECT DISTINCT CultureName

FROM Aspx_CategoryInformationDetail

WHERE CultureName = 'en-US'

AND CategoryID = 32 ) THEN 'en-US'

ELSE 'en-US'

END

JOIN Aspx_CategoryAttributes CA

ON C.CategoryID = CA.CategoryID

AND C.CategoryID = 32

JOIN Aspx_Attributes A

ON CA.AttributeID = A.AttributeID

LEFT JOIN Aspx_AttributeAlias AA

--ON AA.CultureName = CASE 'en-US'

-- WHEN ( SELECT DISTINCT CultureName

-- FROM Aspx_AttributeAlias

-- WHERE CultureName = 'en-US'

-- AND AA.AttributeID = A.AttributeID ) THEN 'en-US'

-- ELSE 'en-US'

-- END

on AA.AttributeID = CA.AttributeID --and

-------

LEFT OUTER JOIN Aspx_CategoryAttributesValue_T CAVT

ON CA.AttributeID = CAVT.AttributeID

AND CA.CategoryID = CAVT.CategoryID

AND ( CAVT.StoreID = 1 )

AND ( CAVT.PortalID = 1 )

AND CAVT.IsDeleted = 'false'

AND CAVT.CategoryID = 32

--AND CAVT.CultureName = CASE 'en-US' WHEN ( SELECT distinct CultureName

-- FROM Aspx_CategoryAttributesValue_T

-- WHERE CultureName = 'en-US'

-- AND CategoryID = 32 ) THEN 'en-US' ELSE 'en-ES' END

LEFT JOIN Aspx_CategoryAttributesValue_D CAVD

ON CA.AttributeID = CAVD.AttributeID

AND CA.CategoryID = CAVD.CategoryID

AND ( CAVD.StoreID = 1 )

AND ( CAVD.PortalID = 1 )

AND CAVD.IsDeleted = 'false'

AND CAVD.CategoryID = 32

-- AND CAVD.CultureName = CASE 'en-US'

-- WHEN ( SELECT DISTINCT CultureName

-- FROM Aspx_CategoryAttributesValue_D

-- WHERE CultureName = 'en-US'

-- AND CategoryID = 32 ) THEN 'en-US'

--ELSE 'en-US'

-- END

LEFT JOIN Aspx_CategoryAttributesValue_N CAVN

ON CA.AttributeID = CAVN.AttributeID

AND CA.CategoryID = CAVN.CategoryID

AND ( CAVN.StoreID = 1 )

AND ( CAVN.PortalID = 1 )

AND CAVN.IsDeleted = 'false'

AND CAVN.CategoryID = 32

-- AND CAVN.CultureName = CASE 'en-US'

-- WHEN ( SELECT DISTINCT CultureName

-- FROM Aspx_CategoryAttributesValue_N

-- WHERE CultureName = 'en-US'

-- AND CategoryID = 32 ) THEN 'en-US'

--ELSE 'en-US'

---END

LEFT JOIN Aspx_CategoryAttributesValue_I CAVI

ON CA.AttributeID = CAVI.AttributeID

AND CA.CategoryID = CAVI.CategoryID

AND ( CAVI.StoreID = 1 )

AND ( CAVI.PortalID = 1 )

AND CAVI.IsDeleted = 'false'

AND CAVI.CategoryID = 32

-- AND CAVI.CultureName = CASE 'en-US'

-- WHEN ( SELECT DISTINCT CultureName

-- FROM Aspx_CategoryAttributesValue_I

-- WHERE CultureName = 'en-US'

-- AND CategoryID = 32 ) THEN 'en-US'

-- ELSE 'en-US'

-- END

LEFT JOIN Aspx_CategoryAttributesValue_F CAVF

ON CA.AttributeID = CAVF.AttributeID

AND CA.CategoryID = CAVF.CategoryID

AND ( CAVF.StoreID = 1 )

AND ( CAVF.PortalID = 1 )

AND CAVF.IsDeleted = 'false'

AND CAVF.CategoryID = 32

-- AND CAVF.CultureName = CASE 'en-US'

-- WHEN ( SELECT DISTINCT CultureName

-- FROM Aspx_CategoryAttributesValue_F

-- WHERE CultureName = 'en-US'

-- AND CategoryID = 32 ) THEN 'en-US'

-- ELSE 'en-US'

-- END

LEFT JOIN Aspx_CategoryAttributesValue_D CAVDT

ON CA.AttributeID = CAVDT.AttributeID

AND CA.CategoryID = CAVDT.CategoryID

AND ( CAVDT.StoreID = 1 )

AND ( CAVDT.PortalID = 1 )

AND CAVDT.IsDeleted = 'false'

AND CAVDT.CategoryID = 32

--AND CAVDT.CultureName = CASE 'en-US'

-- WHEN ( SELECT DISTINCT CultureName

-- FROM Aspx_CategoryAttributesValue_D

-- WHERE CultureName = 'en-US'

-- AND CategoryID = 32 ) THEN 'en-US'

--ELSE 'en-US'

--END

LEFT JOIN Aspx_CategoryAttributesValue_B CAVB

ON CA.AttributeID = CAVB.AttributeID

AND CA.CategoryID = CAVB.CategoryID

AND ( CAVB.StoreID = 1 )

AND ( CAVB.PortalID = 1 )

AND CAVB.IsDeleted = 'false'

AND CAVB.CategoryID = 32

--AND CAVB.CultureName = CASE 'en-US'

-- WHEN ( SELECT DISTINCT CultureName

-- FROM Aspx_CategoryAttributesValue_B

-- WHERE CultureName = 'en-US'

-- AND CategoryID = 32 ) THEN 'en-US'

-- ELSE 'en-US'

-- END

LEFT JOIN Aspx_CategoryAttributesValue_O CAVO

ON CA.AttributeID = CAVO.AttributeID

AND CA.CategoryID = CAVO.CategoryID

AND ( CAVO.StoreID = 1 )

AND ( CAVO.PortalID = 1 )

AND CAVO.IsDeleted = 'false'

AND CAVO.CategoryID = 32

--AND CAVO.CultureName = CASE 'en-US'

-- WHEN ( SELECT DISTINCT CultureName

-- FROM Aspx_CategoryAttributesValue_O

-- WHERE CultureName = 'en-US'

-- AND CategoryID = 32 ) THEN 'en-US'

-- ELSE 'en-US'

-- END

WHERE ( CA.CategoryID = 32 )

AND C.PortalID = 1

AND C.StoreID = 1

AND C.IsDeleted = 'false';

this is the result

32	31	1	1	Name	1	8	false	true	Games			01-JAN-10	9
32	31	1	2	Description	2	8	false	true		Games		01-JAN-10	9
32	31	1	3	Short Description	2	8	false	true		Games		01-JAN-10	9
32	31	1	9	Meta Title	1	8	false	false				01-JAN-10	9
32	31	1	10	Meta Keywords	2	8	false	false				01-JAN-10	9
32	31	1	11	Meta Description	2	8	false	false				01-JAN-10	9
32	31	1	12	Base Image	8	8	false	false				01-JAN-10	9
32	31	1	16	Include in Navigation Menu	4	8	false	false			true	01-JAN-10	9
32	31	1	17	Is Shown in Search	4	8	false	false			true	01-JAN-10	9
32	31	1	18	Is Shown in Catalog	4	8	false	false			true	01-JAN-10	9
32	31	1	19	Active From	3	8	false	true				14-JUL-15	9
32	31	1	20	Active To	3	8	false	true				14-JUL-20	9
32	31	1	21	Small Image	8	8	false	false				01-JAN-10	9
32	31	1	22	Thumbnail Image	8	8	false	false				01-JAN-10	9
32	31	1	36	Is Service	6	8	false	false			false	01-JAN-10	9
32	31	1	1	¿¿	1	8	false	true	Games			01-JAN-10	9
32	31	1	2	¿¿¿¿	2	8	false	true		Games		01-JAN-10	9
32	31	1	3	¿¿¿	2	8	false	true		Games		01-JAN-10	9
32	31	1	9	META¿¿	1	8	false	false				01-JAN-10	9
32	31	1	10	Meta¿¿¿	2	8	false	false				01-JAN-10	9
32	31	1	11	META¿¿	2	8	false	false				01-JAN-10	9
32	31	1	12	¿¿¿¿	8	8	false	false				01-JAN-10	9
32	31	1	16	¿¿¿¿¿¿¿	4	8	false	false			true	01-JAN-10	9
32	31	1	17	¿¿¿¿¿¿	4	8	false	false			true	01-JAN-10	9
32	31	1	18	¿¿¿¿¿	4	8	false	false			true	01-JAN-10	9
32	31	1	19	¿¿¿	3	8	false	true				14-JUL-15	9
32	31	1	20	¿¿¿¿	3	8	false	true				14-JUL-20	9
32	31	1	21	¿¿¿	8	8	false	false				01-JAN-10	9
32	31	1	22	¿¿	8	8	false	false				01-JAN-10	9
32	31	1	36	¿¿¿¿	6	8	false	false			false	01-JAN-10	9

and this is the sql server query

SELECT CA.CategoryID
  ,C.ParentID
  ,C.CategoryLevel
  ,A.AttributeID
  ,coalesce(AA.AliasName, A.AttributeName) AS AttributeName
  ,A.InputTypeID
  ,A.ValidationTypeID
  ,A.IsUnique
  ,A.IsRequired
  ,CASE ca.AttributeID
  WHEN 1
  THEN cid.CategoryName
  WHEN 9
  THEN cid.MetaTitle
  ELSE CAVN.AttributeValue
  END AS NVARCHARValue
  ,CASE ca.AttributeID
  WHEN 2
  THEN cid.[Description]
  WHEN 3
  THEN cid.ShortDescription
  WHEN 10
  THEN cid.MetaKeyword
  WHEN 11
  THEN cid.MetaDescription
  ELSE CAVT.AttributeValue
  END AS TEXTValue
  ,CASE ca.AttributeID
  WHEN 16
  THEN c.IsshowInMenu
  WHEN 17
  THEN c.IsShowInSearch
  WHEN 18
  THEN c.IsShowInCatalog
  WHEN 36
  THEN c.IsService
  ELSE CAVB.AttributeValue
  END AS BooleanValue
  ,CAVI.AttributeValue AS INTValue
  ,CASE ca.AttributeID
  WHEN 19
  THEN c.ActiveFrom
  WHEN 20
  THEN c.ActiveTo
  ELSE ISNULL(CAVDT.AttributeValue, Convert(DATETIME, '1970-01-01'))
  END AS DATEValue
  ,CAVD.AttributeValue AS DECIMALValue
  ,CASE ca.AttributeID
  WHEN 12
  THEN c.BaseImage
  WHEN 21
  THEN c.SmallImage
  WHEN 22
  THEN c.ThumbnailImage
  ELSE CAVF.AttributeValue
  END AS FILEValue
  ,CASE c.IsService
  WHEN 1
  THEN 10
  WHEN 0
  THEN 9
  ELSE NULL
  END AS OPTIONValues
  FROM dbo.Aspx_Category AS C
  INNER JOIN dbo.Aspx_CategoryInformationDetails cid ON cid.CategoryID = c.CategoryID
  AND cid.CultureName = CASE 'en-US'
  WHEN (
  SELECT DISTINCT CultureName
  FROM Aspx_CategoryInformationDetails
  WHERE CultureName = 'en-US'
  AND CategoryID = 32
  )
  THEN 'en-US'
  ELSE 'en-US'
  END
  INNER JOIN dbo.Aspx_CategoryAttributes AS CA ON C.CategoryID = CA.CategoryID
  AND C.CategoryID = 32
  INNER JOIN dbo.Aspx_Attributes AS A ON CA.AttributeID = A.AttributeID
  LEFT JOIN dbo.Aspx_AttributeAlias AA ON AA.[CultureName] = CASE 'en-US'
  WHEN (
  SELECT DISTINCT CultureName
  FROM Aspx_AttributeAlias
  WHERE CultureName = 'en-US'
  AND AA.AttributeID = A.AttributeID
  )
  THEN 'en-US'
  ELSE 'en-US'
  END
  AND AA.AttributeID = CA.AttributeID
  LEFT JOIN dbo.Aspx_CategoryAttributesValue_Text AS CAVT ON CA.AttributeID = CAVT.AttributeID
  AND CA.CategoryID = CAVT.CategoryID
  AND (CAVT.StoreID = 1)
  AND (CAVT.PortalID = 1)
  AND CAVT.IsDeleted = 0
  AND CAVT.CategoryID = 32
  AND CAVT.[CultureName] = CASE 'en-US'
  WHEN (
  SELECT DISTINCT CultureName
  FROM Aspx_CategoryAttributesValue_Text
  WHERE CultureName = 'en-US'
  AND CategoryID = 32
  )
  THEN 'en-US'
  ELSE 'en-US'
  END
  LEFT JOIN dbo.Aspx_CategoryAttributesValue_Decimal AS CAVD ON CA.AttributeID = CAVD.AttributeID
  AND CA.CategoryID = CAVD.CategoryID
  AND (CAVD.StoreID = 1)
  AND (CAVD.PortalID = 1)
  AND CAVD.IsDeleted = 0
  AND CAVD.CategoryID = 32
  AND CAVD.[CultureName] = CASE 'en-US'
  WHEN (
  SELECT DISTINCT CultureName
  FROM Aspx_CategoryAttributesValue_Decimal
  WHERE CultureName = 'en-US'
  AND CategoryID = 32
  )
  THEN 'en-US'
  ELSE 'en-US'
  END
  LEFT JOIN dbo.Aspx_CategoryAttributesValue_Nvarchar AS CAVN ON CA.AttributeID = CAVN.AttributeID
  AND CA.CategoryID = CAVN.CategoryID
  AND (CAVN.StoreID = 1)
  AND (CAVN.PortalID = 1)
  AND CAVN.IsDeleted = 0
  AND CAVN.CategoryID = 32
  AND CAVN.[CultureName] = CASE 'en-US'
  WHEN (
  SELECT DISTINCT CultureName
  FROM Aspx_CategoryAttributesValue_Nvarchar
  WHERE CultureName = 'en-US'
  AND CategoryID = 32
  )
  THEN 'en-US'
  ELSE 'en-US'
  END
  LEFT JOIN dbo.Aspx_CategoryAttributesValue_Int AS CAVI ON CA.AttributeID = CAVI.AttributeID
  AND CA.CategoryID = CAVI.CategoryID
  AND (CAVI.StoreID = 1)
  AND (CAVI.PortalID = 1)
  AND CAVI.IsDeleted = 0
  AND CAVI.CategoryID = 32
  AND CAVI.[CultureName] = CASE 'en-US'
  WHEN (
  SELECT DISTINCT CultureName
  FROM Aspx_CategoryAttributesValue_Int
  WHERE CultureName = 'en-US'
  AND CategoryID = 32
  )
  THEN 'en-US'
  ELSE 'en-US'
  END
  LEFT JOIN dbo.Aspx_CategoryAttributesValue_File AS CAVF ON CA.AttributeID = CAVF.AttributeID
  AND CA.CategoryID = CAVF.CategoryID
  AND (CAVF.StoreID = 1)
  AND (CAVF.PortalID = 1)
  AND CAVF.IsDeleted = 0
  AND CAVF.CategoryID = 32
  AND CAVF.[CultureName] = CASE 'en-US'
  WHEN (
  SELECT DISTINCT CultureName
  FROM Aspx_CategoryAttributesValue_File
  WHERE CultureName = 'en-US'
  AND CategoryID = 32
  )
  THEN 'en-US'
  ELSE 'en-US'
  END
  LEFT JOIN dbo.Aspx_CategoryAttributesValue_Date AS CAVDT ON CA.AttributeID = CAVDT.AttributeID
  AND CA.CategoryID = CAVDT.CategoryID
  AND (CAVDT.StoreID = 1)
  AND (CAVDT.PortalID = 1)
  AND CAVDT.IsDeleted = 0
  AND CAVDT.CategoryID = 32
  AND CAVDT.[CultureName] = CASE 'en-US'
  WHEN (
  SELECT DISTINCT CultureName
  FROM Aspx_CategoryAttributesValue_Date
  WHERE CultureName = 'en-US'
  AND CategoryID = 32
  )
  THEN 'en-US'
  ELSE 'en-US'
  END
  LEFT JOIN dbo.Aspx_CategoryAttributesValue_Boolean AS CAVB ON CA.AttributeID = CAVB.AttributeID
  AND CA.CategoryID = CAVB.CategoryID
  AND (CAVB.StoreID = 1)
  AND (CAVB.PortalID = 1)
  AND CAVB.IsDeleted = 0
  AND CAVB.CategoryID = 32
  AND CAVB.[CultureName] = CASE 'en-US'
  WHEN (
  SELECT DISTINCT CultureName
  FROM Aspx_CategoryAttributesValue_Boolean
  WHERE CultureName = 'en-US'
  AND CategoryID = 32
  )
  THEN 'en-US'
  ELSE 'en-US'
  END
  LEFT JOIN dbo.Aspx_CategoryAttributesValue_Options AS CAVO ON CA.AttributeID = CAVO.AttributeID
  AND CA.CategoryID = CAVO.CategoryID
  AND (CAVO.StoreID = 1)
  AND (CAVO.PortalID = 1)
  AND CAVO.IsDeleted = 0
  AND CAVO.CategoryID = 32
  AND CAVO.[CultureName] = CASE 'en-US'
  WHEN (
  SELECT DISTINCT CultureName
  FROM Aspx_CategoryAttributesValue_Options
  WHERE CultureName = 'en-US'
  AND CategoryID = 32
  )
  THEN 'en-US'
  ELSE 'en-US'
  END
  WHERE (CA.CategoryID = 32)
  AND C.PortalID = 1
  AND C.StoreID = 1
  AND C.IsDeleted = 0




  select Convert(DATETIME, '1970-01-01')

  SELECT * FROM Aspx_Category;
  SELECT * FROM Aspx_CategoryAttributes WHERE CategoryID = 32;

  SELECT * FROM Aspx_Attributes;

and this is the result

32	31	1	1	Name	1	8	1	Games	NULL	NULL	NULL	1970-01-01 00:00:00.000	NULL	NULL	9
32	31	1	2	Description	2	8	1	NULL	Games	NULL	NULL	1970-01-01 00:00:00.000	NULL	NULL	9
32	31	1	3	Short Description	2	8	1	NULL	Games	NULL	NULL	1970-01-01 00:00:00.000	NULL	NULL	9
32	31	1	9	Meta Title	1	8	0		NULL	NULL	NULL	1970-01-01 00:00:00.000	NULL	NULL	9
32	31	1	10	Meta Keywords	2	8	0	NULL		NULL	NULL	1970-01-01 00:00:00.000	NULL	NULL	9
32	31	1	11	Meta Description	2	8	0	NULL		NULL	NULL	1970-01-01 00:00:00.000	NULL	NULL	9
32	31	1	12	Base Image	8	8	0	NULL	NULL	NULL	NULL	1970-01-01 00:00:00.000	NULL		9
32	31	1	16	Include in Navigation Menu	4	8	0	NULL	NULL	1	NULL	1970-01-01 00:00:00.000	NULL	NULL	9
32	31	1	17	Is Shown in Search	4	8	0	NULL	NULL	1	NULL	1970-01-01 00:00:00.000	NULL	NULL	9
32	31	1	18	Is Shown in Catalog	4	8	0	NULL	NULL	1	NULL	1970-01-01 00:00:00.000	NULL	NULL	9
32	31	1	19	Active From	3	8	1	NULL	NULL	NULL	NULL	2014-09-18 00:00:00.000	NULL	NULL	9
32	31	1	20	Active To	3	8	1	NULL	NULL	NULL	NULL	2020-09-18 00:00:00.000	NULL	NULL	9
32	31	1	21	Small Image	8	8	0	NULL	NULL	NULL	NULL	1970-01-01 00:00:00.000	NULL		9
32	31	1	22	Thumbnail Image	8	8	0	NULL	NULL	NULL	NULL	1970-01-01 00:00:00.000	NULL		9
32	31	1	36	Is Service	6	8	0	NULL	NULL	0	NULL	1970-01-01 00:00:00.000	NULL	NULL	9

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 30 2015

Added on Dec 2 2015

3 comments

4,495 views