Hi, I have a very simple LINQ to LLBLGen expression to get the minimum date in a certain table:

LinqMetaData data = new LinqMetaData(); var results = (from x in data.RdcMutaties select x).Min(p => p.MutatieDatum);

This code fails with the following exception:

ORA-00904: "LPA_L1"."MUTATIE_DATUM": invalid identifier

This error is due to the generated SQL:

SELECT * FROM (SELECT DISTINCT MIN **("LPA_L1"."MUTATIE_DATUM") **AS "LPAV_" FROM (SELECT "LPLA_1"."ID" AS "Id", "LPLA_1"."KENTEKEN" AS "Kenteken", "LPLA_1"."POSTCODE" AS "Postcode", "LPLA_1"."MUTATIE_DATUM" AS "MutatieDatum", "LPLA_1"."MUTATIE_TIJD" AS "MutatieTijd" FROM "KRD"."RDC_MUTATIES" "LPLA_1") "LPA_L1") WHERE ROWNUM <= 1

As you can see, the SELECT statement that has the MIN selects the field "LPA_L1"."MUTATIE_DATUM". But, because this field is aliased in the subquery (AS "MutatieDatum"), the outer query cannot find the field.

Is there something I'm missing here?

Thank you.

Diederik

Selfservicing Oracle 10g Microsoft driver Runtime version 2.6.0.0 Runtime Build 08122008

First thing to try is to get the latest build of the runtime, and give it one more test.

Thanks Walaa, that solved the problem!

Regards, Diederik

Walaa wrote:

First thing to try is to get the latest build of the runtime, and give it one more test.

Hello,

I having the same problem with invalid identifier.

Here is my linq query

var subQuery = from c in metaData.UiWageDtlTran where c.RptYr == 2015 && c.RptQtr == 3 && c.WageAdjSeqNu == (from t in metaData.UiWageDtlTran where t.EmprAcctId==c.EmprAcctId && t.RptYr == c.RptYr && t.RptQtr == c.RptQtr select t.WageAdjSeqNu).Max()

Attaching the error information from LinqPad and it has the query and error details. Below is the query I am trying to achieve in Linq.

select * from ui_wage_Dtl_tran a where a.rpt_yr = 2014 and a.rpt_qtr = 3 and a.wage_adj_seq_nu = (select max(wage_Adj_Seq_nu) from ui_wage_dtl_tran where rpt_yr = a.rpt_yr and rpt_qtr = a.rpt_qtr and empr_acct_id = a.empr_acct_id)

What is your LLBLGen version and runtime library version?

What is the exact code and generated sql?

Please next time open a new thread instead of open an old one.

I have given all the information in the attachment. Do you not see my attachment ?

In my previous post i have copied the Linq query and the SQL i am trying to achieve. Not sure what else you need from me.

Anyway I am using

Llblgen 4.2 and runtime version 4.2.0.0

AND ( "LPLA_2"."RPT_YR" = "LPA_L1"."RPT_YR")) AND ( "LPLA_2"."RPT_QTR" = "LPA_L1"."RPT_QTR"))))

If you run the SQL directly against the database, does it complain from the last correlated predicate? I wonder why it didn't complain from the RPT_YR field in the previous predicate?

It gives the same error when run against the database. if i remove rpt_qtr from the join then it complains on rpt_yr.

Here is the simple version of the query that llblgen generated. If I add Max to the sub query instead of being outside it works.

This fails.

SELECT * FROM ui_wage_Dtl_tran a WHERE a.rpt_yr = 2014 AND a.rpt_qtr = 3 AND a.wage_adj_seq_nu = (SELECT MAX (d.seq) FROM (SELECT wage_Adj_Seq_nu AS seq FROM ui_wage_dtl_tran WHERE rpt_yr = a.rpt_yr AND rpt_qtr = a.rpt_qtr AND empr_acct_id = a.empr_acct_id) d)

If i move the max inside it works

SELECT * FROM ui_wage_Dtl_tran a WHERE a.rpt_yr = 2014 AND a.rpt_qtr = 3 AND a.wage_adj_seq_nu = (SELECT max(wage_Adj_Seq_nu) AS seq FROM ui_wage_dtl_tran WHERE rpt_yr = a.rpt_yr AND rpt_qtr = a.rpt_qtr AND empr_acct_id = a.empr_acct_id)

From other threads, it seems that this is a limitation of oracle: referencing aliases defined in the outer query inside a subquery doesn't work if the alias is 2 or more outer queries away like in this case.

Possible workarounds:

a) Try to reformulate the linq query to avoid the extra sub-query.

b) Fetch the MAX(WageAdjSeqNu) in a separate query. Then use that value to filter in the actual query.

c) Use LLBLGen Runtime API or QuerySpec instead of linq. Those give you more flexibility.

That's what i have found in my research. We also have a 12c database and the query works there. Don't know if oracle removed this limitation in 12c but for now would like to stay away from more than one level correlated subquery until we know for sure it works in future releases as well.

We recently upgraded from v3 to v4.2 and I am still new to QuerySpec. If you don't mind can you provide an example on how would l write the query in QuerySpec

sunielreddy wrote:

If you don't mind can you provide an example on how would l write the query in QuerySpec

This is a similar example, extracted from [LLBLGen Instalation Folder]\Frameworks\LLBLGen Pro\ExampleQueries\QuerySpec\AdapterTests\EntityFetches.cs:

[Test]
public void GetCustomersWithOrdersFiledByTheMaxEmployeeIdToTestScalarInWhereClause()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        //LinqMetaData metaData = new LinqMetaData(adapter);
        //var q = from c in metaData.Customer
        //      join o in metaData.Order on c.CustomerId equals o.CustomerId
        //      where o.EmployeeId == (from e in metaData.Employee
        //                            where e.EmployeeId == o.EmployeeId
        //                            select e).Min(e => e.EmployeeId)
        //                            && c.Country=="Germany"
        //      select c;

        var qf = new QueryFactory();
        var q = qf.Customer
                    .From(QueryTarget.InnerJoin(qf.Order).On(CustomerFields.CustomerId == OrderFields.CustomerId))
                    .Where(OrderFields.EmployeeId.Equal(qf.Employee
                                                                .CorrelatedOver(EmployeeEntity.Relations.OrderEntityUsingEmployeeId)
                                                                .Select(EmployeeFields.EmployeeId) // to optimize projection
                                                                .Min(EmployeeFields.EmployeeId))
                                                    .And(CustomerFields.Country == "Germany"));

        // an alternative (different scalar query construction)
        //var q = qf.Customer
        //          .From(QueryTarget.InnerJoin(qf.Order).On(CustomerFields.CustomerId == OrderFields.CustomerId))
        //          .Where(OrderFields.EmployeeId.Equal(qf.Employee
        //                                                      .CorrelatedOver(EmployeeEntity.Relations.OrderEntityUsingEmployeeId)
        //                                                      .Select(EmployeeFields.EmployeeId.Min())
        //                                                      .ToScalar())    // ToScalar is required as the predicate requires a value to compare with
        //                                          .And(CustomerFields.Country == "Germany"));

        var customers = adapter.FetchQuery(q);
        int count = 0;
        foreach(var v in customers)
        {
            count++;
        }
        Assert.AreEqual(11, count);
    }