We have a this query which works fine in SQL server but in Oracle gives 'ORA-00904: "LPA_L2"."INITIAL_SEVERITY_ID": invalid identifier'

from r in Risk
from rls in r.RiskLikelihoodSeverities
select RiskMatrix.First(rm => rm.SeverityID == rls.InitialSeverityID).RiskLevel.RiskLevel

which produces this SQL

SELECT (SELECT "LPA_L4"."RiskLevel"
FROM
 (SELECT "LPA_L5"."RISK_COLOUR" AS "RiskColour",
  "LPA_L5"."RISK_LEVEL" AS "RiskLevel",
  "LPA_L5"."RISK_LEVEL_ABBREVIATION" AS "RiskLevelAbbreviation",
  "LPA_L5"."RISK_LEVEL_ID" AS "RiskLevelID",
  "LPA_L5"."STATUS" AS "Status"
FROM
 ( "AQD"."RM_RISK_LEVEL" "LPA_L5" 
INNER JOIN
 "AQD"."RM_RISK_MATRIX" "LPA_L6"  ON  "LPA_L5"."RISK_LEVEL_ID"="LPA_L6"."RISK_LEVEL_ID")
WHERE
 ( ( ( ( "LPA_L6"."SEVERITY_ID" = "LPA_L2"."INITIAL_SEVERITY_ID")))) AND rownum <= 1) "LPA_L4"
WHERE
 rownum <= 1) AS "LPFA_1"
FROM
 ( "AQD"."RM_RISK" "LPA_L1" 
INNER JOIN
 "AQD"."RM_RISK_LIKELIHOOD_SEVERITY" "LPA_L2"  ON  "LPA_L1"."RISK_NO"="LPA_L2"."RISK_NO")

Asking around Oracle forums we were told 'You create the alias LPA_L2 outside the scope of the inline view LPFA_1. You cannot reference those columns inside the LPFA_1 select.' SQL server SQL runs fine

DECLARE @p2 BigInt; SET @p2=1
DECLARE @p4 BigInt; SET @p4=1
SELECT (SELECT TOP(@p2) [LPA_L4].[RiskLevel]
FROM
 (SELECT TOP(@p4) [LPA_L5].[Risk_Colour] AS [RiskColour],
  [LPA_L5].[Risk_Level] AS [RiskLevel],
  [LPA_L5].[Risk_Level_Abbreviation] AS [RiskLevelAbbreviation],
  [LPA_L5].[Risk_Level_ID] AS [RiskLevelID],
  [LPA_L5].[Status]
FROM
 ( [AQD].[rm_Risk_Level] [LPA_L5] 
INNER JOIN
 [AQD].[rm_Risk_Matrix] [LPA_L6]  ON  [LPA_L5].[Risk_Level_ID]=[LPA_L6].[Risk_Level_ID])
WHERE
 ( ( ( ( [LPA_L6].[Severity_ID] = [LPA_L2].[Initial_Severity_ID]))))) [LPA_L4]) AS [LPFA_1]
FROM
 ( [AQD].[rm_Risk] [LPA_L1] 
INNER JOIN
 [AQD].[rm_Risk_Likelihood_Severity] [LPA_L2]  ON  [LPA_L1].[Risk_No]=[LPA_L2].[Risk_No])

LLBL 3.0 20-May-2011 Release

I have attached a script to create the 4 tables involved.

Is this 3.0? or 3.1 as in your signature? Would you please post the exact runtime library build number.

Also which version of Oracle is this?

Are you sure the posted linq code is exactly the one you used to get this SQL? Coz the query contains ...and rownum<=1 twice, while linq contains .First() once.

Walaa wrote:

Is this 3.0? or 3.1 as in your signature? Would you please post the exact runtime library build number.

Also which version of Oracle is this?

Are you sure the posted linq code is exactly the one you used to get this SQL? Coz the query contains ...and rownum<=1 twice, while linq contains .First() once.

ORMSupportClasses: both 3.1.11.225 and 3.0.11.0518 Oracle: both 10 and 11 Yep sure about linq, this is the sql from the exception:

Query: SELECT (SELECT "LPA_L4"."RiskLevel" FROM (SELECT "LPA_L5"."RISK_COLOUR" AS "RiskColour", "LPA_L5"."RISK_LEVEL" AS "RiskLevel", "LPA_L5"."RISK_LEVEL_ABBREVIATION" AS "RiskLevelAbbreviation", "LPA_L5"."RISK_LEVEL_ID" AS "RiskLevelID", "LPA_L5"."STATUS" AS "Status" FROM ( "AQD"."RM_RISK_LEVEL" "LPA_L5"  INNER JOIN "AQD"."RM_RISK_MATRIX" "LPA_L6"  ON  "LPA_L5"."RISK_LEVEL_ID"="LPA_L6"."RISK_LEVEL_ID") WHERE ( ( ( ( "LPA_L6"."SEVERITY_ID" = "LPA_L2"."INITIAL_SEVERITY_ID")))) AND rownum <= 1) "LPA_L4" WHERE rownum <= 1) AS "LPFA_1" FROM ( "AQD"."RM_RISK" "LPA_L1"  INNER JOIN "AQD"."RM_RISK_LIKELIHOOD_SEVERITY" "LPA_L2"  ON  "LPA_L1"."RISK_NO"="LPA_L2"."RISK_NO")

We think this is caused by the encapsulation of the query with the rownum filter. There are some threads about this on the forum. we fixed it to be inlined (to avoid this problem) however that can't always be done, causing this to re-occur. Not sure whether this is solveable in another way...

SELECT  
    (
        SELECT "LPA_L4"."RiskLevel" 
        FROM (
                SELECT   "LPA_L5"."RISK_COLOUR" AS "RiskColour", "LPA_L5"."RISK_LEVEL" AS "RiskLevel", "LPA_L5"."RISK_LEVEL_ABBREVIATION" AS "RiskLevelAbbreviation", 
                        "LPA_L5"."RISK_LEVEL_ID" AS "RiskLevelID", "LPA_L5"."STATUS" AS "Status" 
                FROM ( "AQD"."RM_RISK_LEVEL" "LPA_L5" INNER JOIN "AQD"."RM_RISK_MATRIX" "LPA_L6" 
                            ON "LPA_L5"."RISK_LEVEL_ID"="LPA_L6"."RISK_LEVEL_ID") 
                WHERE    (((("LPA_L6"."SEVERITY_ID" = "LPA_L2"."INITIAL_SEVERITY_ID")))) 
                        AND rownum <= 1
            ) "LPA_L4" 
        WHERE rownum <= 1 -- <<<<<<<<<< Causing the error
    ) AS "LPFA_1" 
    FROM ( "AQD"."RM_RISK" "LPA_L1" INNER JOIN "AQD"."RM_RISK_LIKELIHOOD_SEVERITY" "LPA_L2" 
        ON "LPA_L1"."RISK_NO"="LPA_L2"."RISK_NO")

The strange thing is the two rownum filters, the inner one is enough. The outer one is the one which causes the problem (deep nesting, scope change. Can't be fixed). The query is quite inefficient IMHO, as for every row in the outer FROM, a subquery is executed.

To elaborate: the scope problem is specific for oracle, where the rownum filtering query creates a new scope so the actual subquery isn't the direct scope beneath it, causing a problem with correlated subqueries, so they're not really tied together anymore (as the correlated subquery is too deep, oracle finds this a problem, sqlserver doesn't). I also think it's unavoidable here because you project the projection again wrapping the actual correlated subquery deeper.

The outer rownum filter is perhaps a bit odd, as the inner one already makes the resultset have 1 row, however it's unclear to me where it comes from without analysis of the tree. We suggest to rewrite the query differently as IMHO I don't see a way to fix this in this form (as in: I don't know what to change to the query to make it work)

Changing the query to

from r in Risk
from rls in r.RiskLikelihoodSeverities
from initialRisk in rls.InitialSeverity.RiskMatrices.DefaultIfEmpty()
                                             where initialRisk.LikelihoodID == rls.InitialLikelihoodID || initialRisk == null
                                             join irl in DataSingletons.MetaData.RiskLevel on initialRisk.RiskLevelID equals irl.RiskLevelID into initialRiskLevels
                                             from irl in initialRiskLevels.DefaultIfEmpty()
select irl.RiskLevel

works. It's annoying to have to do the join to get the LEFT JOIN but we've covered that already - http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=19256

SELECT "LPA_L5"."RISK_LEVEL" AS "RiskLevel"
FROM
 (((( "AQD"."RM_RISK" "LPA_L1" 
INNER JOIN
 "AQD"."RM_RISK_LIKELIHOOD_SEVERITY" "LPA_L2"  ON  "LPA_L1"."RISK_NO"="LPA_L2"."RISK_NO")
LEFT JOIN
 "AQD"."RM_SEVERITY" "LPA_L3"  ON  "LPA_L3"."SEVERITY_ID"="LPA_L2"."INITIAL_SEVERITY_ID")
LEFT JOIN
 "AQD"."RM_RISK_MATRIX" "LPA_L4"  ON  "LPA_L3"."SEVERITY_ID"="LPA_L4"."SEVERITY_ID")
LEFT JOIN
 "AQD"."RM_RISK_LEVEL" "LPA_L5"  ON  "LPA_L4"."RISK_LEVEL_ID" = "LPA_L5"."RISK_LEVEL_ID")
WHERE
 ( ( ( ( ( "LPA_L4"."LIKELIHOOD_ID" = "LPA_L2"."INITIAL_LIKELIHOOD_ID") OR ( ( "LPA_L4"."SEVERITY_ID" IS NULL AND "LPA_L4"."LIKELIHOOD_ID" IS NULL))))))

Strangely we have a magic instance of Oracle 10 where the original query works - haven't figured out why yet - we are assured it shouldn't.

Actually the query you posted above is what I'd go for anyway, it's more efficient. The problem is the 'RiskLevel' projection on the nested query which creates a new scope around the scope where the outer reference is, which is causing the problem.

The problem you ran into isn't a new one, we had some reports about this problem earlier, when in an older version we always wrapped the main query with a SELECT * FROM (mainquery) q WHERE q.rownum <= param

this went wrong when mainquery referred to an outer scope. I can dig up some older threads about this if you want. The solution for that was to move the where rownum <= param into th main query, however that's not always possible. Your query has an additional projection so this can't be fixed that way: if it directly projected RiskLevel from the FROM clause, it could work, but I don't see a way to formulate that in Linq.

I am surprised there's some Oracle box which eats the query though What I did understand from a post from Tom Kyte about this is that it's an Oracle issue/limitation.

Otis wrote:

I am surprised there's some Oracle box which eats the query though What I did understand from a post from Tom Kyte about this is that it's an Oracle issue/limitation.

Looks like we just got 'lucky' http://forums.genom-e.com/Lists/Genome%20Support/Attachments/2048/Oracle%20Document%20374632.1.1.pdf 'Also note that due to a fix for (internal) bug 3786011 there are some versions of Oracle 10.2.0.1 in which the parser does allow correlated inline views to be parsed.'

We've hit this problem again we have some Linq like this (Northwind)

from s in Supplier
select new {s.SupplierId, s.CompanyName, ProductsCount = s.Products.Count(), OrderDetailsCount = s.Products.Sum(p=>p.OrderDetails.Count)}

which gives:

SELECT "LPLA_1"."SUPPLIERID" AS "SupplierId",
  "LPLA_1"."COMPANYNAME" AS "CompanyName",
  (SELECT COUNT(*) AS "LPAV_"
FROM
 "DBO_NORTHWIND"."PRODUCTS" "LPLA_2"
WHERE
 ( ( "LPLA_1"."SUPPLIERID" = "LPLA_2"."SUPPLIERID"))) AS "ProductsCount",
  (SELECT SUM("LPA_L2"."LPAV_") AS "LPAV_"
FROM
 (SELECT "LPLA_4"."SUPPLIERID" AS "SupplierId",
  (SELECT COUNT("LPA_L3"."Discount") AS "LPAV_"
FROM
 (SELECT "LPLA_5"."DISCOUNT" AS "Discount",
  "LPLA_5"."ORDERID" AS "OrderId",
  "LPLA_5"."PRODUCTID" AS "ProductId",
  "LPLA_5"."QUANTITY" AS "Quantity",
  "LPLA_5"."UNITPRICE" AS "UnitPrice"
FROM
 "DBO_NORTHWIND"."ORDER_DETAILS" "LPLA_5"
WHERE
 ( ( "LPLA_4"."PRODUCTID" = "LPLA_5"."PRODUCTID"))) "LPA_L3") AS "LPAV_"
FROM
 "DBO_NORTHWIND"."PRODUCTS" "LPLA_4"
WHERE
 ( ( "LPLA_1"."SUPPLIERID" = "LPLA_4"."SUPPLIERID"))) "LPA_L2") AS "OrderDetailsCount"
FROM
 "DBO_NORTHWIND"."SUPPLIERS" "LPLA_1"

in Oracle which barfs. I can rewrite the SQL to make it work like this:

SELECT "LPLA_1"."SUPPLIERID" AS "SupplierId",
  "LPLA_1"."COMPANYNAME" AS "CompanyName",                          
                          (SELECT    COUNT(*) AS Expr1
                            FROM           "DBO_NORTHWIND"."PRODUCTS" "LPLA_2"
                            WHERE     "LPLA_1"."SUPPLIERID" = "LPLA_2"."SUPPLIERID") AS ProductsCount,
                                                      (SELECT    COUNT(*) AS Expr1
                            FROM          "DBO_NORTHWIND"."ORDER_DETAILS" "LPLA_5" INNER JOIN "DBO_NORTHWIND"."PRODUCTS" ON "DBO_NORTHWIND"."PRODUCTS"."PRODUCTID" = "LPLA_5".PRODUCTID
                            WHERE     "LPLA_1"."SUPPLIERID" = "DBO_NORTHWIND"."PRODUCTS"."SUPPLIERID") AS OrderDetailsCount
FROM
 "DBO_NORTHWIND"."SUPPLIERS" "LPLA_1"

Question is how can you generate the version of the query that works in Linq?

TomDog wrote:

Question is how can you generate the version of the query that works in Linq?

To answer my own question this works:

from s in Supplier
select new {s.SupplierId, s.CompanyName, 
ProductsCount= Product.Count(p=>p.SupplierId==s.SupplierId),
OrderDetailsCount = OrderDetail.Count(od=>od.Product.SupplierId==s.SupplierId)}

So nevermind