Hi,

I'm trying to generate this sql

SELECT poz.Id,
(
    SELECT TEXT FROM "SMTAPP"."XF_COMMENTS"  WHERE SMTAPP"."XF_COMMENTS"."DATE" = 
             (SELECT MAX("SMTAPP"."XF_COMMENTS"."DATE") AS "Date" FROM "SMTAPP"."XF_COMMENTS" WHERE "SMTAPP"."XF_COMMENTS"."RECORD_ID" = poz.Id  )
) xfUzivId FROM HF_POZIADAVKY poz

but I struglle with getting the value from parrent query to the nested query.

I'm setting the alias for the parrent query but it doesnt seems to work. Im getting the error Couldn't create any correlation filter lambda because the nested query didn't have any correlation filters. I've been looking at the CorrelatedOVer method but I cannot figure out how to use it in this case.

            var qf = new QueryFactory();

            var maxDateQuery = qf.Create().Select(XfCommentsFields.Date).Where(XfCommentsFields.RecordId == XfCommentsFields.RecordId.SetObjectAlias("x")).Max();

            var commentQuery = qf.Create()
                      .Select(XfCommentsFields.XfUzivId)
                      .Where(XfCommentsFields.Date == maxDateQuery);

            var mq = qf.Create()
                       .Select(
                           HfPoziadavkyFields.Id.SetObjectAlias("x"),
                           commentQuery
                ).As("x");
                    

            var d = _adapter.FetchAsDataTable(mq);

which runtime library version are you using?

3.5.14.113

You should use ScalarQueryExpression. See some examples at http://www.llblgening.com/archive/2009/09/llblgen-pro-expressions-and-scalar-queries/ . Maybe those are not exactly to your scenario, but that gives you an idea.

Ive tried to use it the way

            var maxDateQuery = qf.Create().Select(XfCommentsFields.Date).Where(XfCommentsFields.RecordId == XfCommentsFields.RecordId.SetObjectAlias("x")).Max();

            var mainQuery = qf.Create()
                       .Select(
                           HfPoziadavkyFields.Id,
                           qf.XfComments
                            .CorrelatedOver(HfPoziadavkyFields.XfUzivId == XfCommentsFields.XfUzivId).TargetAs("x")
                            .Select(XfCommentsFields.XfUzivId)
                            .Where(XfCommentsFields.Date == maxDateQuery)
                );

But the query doesnt even include the nested query.

SELECT "SMTAPP"."HF_POZIADAVKY"."ID" AS "Id", 1 AS "LLBLV_1", "SMTAPP"."HF_POZIADAVKY"."XF_UZIV_ID" AS "XfUzivId" FROM "SMTAPP"."HF_POZIADAVKY"

I think my first aproach was good, it missed just the alias.

why are you specifying an alias at all? You're not joining multiple times the same entity... Also 'TargetAs' isn't what you should use on CorrelatedOver, as the TargetAs will be for the query CorrelatedOver is called on.

If u look at the SQL query, I'm using the table alias to pass the value from the parrent query to the nested query

WHERE "SMTAPP"."XF_COMMENTS"."RECORD_ID" = poz.Id

where poz is the table alias of the parrent query.

I've played with the CorrelatedOver, but I dont get how it works. I need to go 2 levels deep.

this is what I got, I feel I'm close :-)

            var qf = new QueryFactory();

            var pr = qf.Create()
                       .Select(
                           HfPoziadavkyFields.Id,
                           qf.XfComments
                             .CorrelatedOver(HfPoziadavkyFields.Id == XfCommentsFields.RecordId)
                             .Select(XfCommentsFields.XfUzivId)
                             .Where(XfCommentsFields.Id == 
                                qf.Create()
                                .Select(XfCommentsFields.Date)
                                .CorrelatedOver(XfCommentsFields.RecordId == HfPoziadavkyFields.Id)
                                .Max())
                );

but the SQL is just the parrent query

SELECT "SMTAPP"."HF_POZIADAVKY"."ID" AS "Id", 1 AS "LLBLV_1" FROM "SMTAPP"."HF_POZIADAVKY"

I dont get how to use the CorrelatedOver for two levels.

Parrent -Nested1 --Nested2

To reach from Parrent to Nested2

You have a nested query in the projection: the select on qf.XfComments is a separate query which results in multiple values. This means that the engine will execute 2 queries and merge them in memory. So you should see 2 queries being executed, not 1. Do you see 2 queries? (the 1 AS "LLBLV_1" is a placeholder in the first query's resultset which is replaced with the nested query results for each row).

CorrelatedOver is a method which will result in a where clause predicate which ties a subquery to the parent query over the predicate (the subquery 'correlates' over the predicate produced by the predicate specified to CorrelatedOver).

There's one problem with your query which I find odd:

.Where(XfCommentsFields.Id ==
                                qf.Create()
                                .Select(XfCommentsFields.Date)
                                .CorrelatedOver(XfCommentsFields.RecordId == HfPoziadavkyFields.Id)
                                .Max())

Here you create a predicate which compares XfCommentsFields.Id with the max of XfCommentsFields.Date. That doesn't sound right.

Looking at your SQL query in the top post, there's a problem there:

SELECT 
    poz.Id,
    (
        SELECT TEXT FROM "SMTAPP"."XF_COMMENTS" 
        WHERE "SMTAPP"."XF_COMMENTS"."DATE" =
                (
                    SELECT MAX("SMTAPP"."XF_COMMENTS"."DATE") AS "Date" 
                    FROM "SMTAPP"."XF_COMMENTS" 
                    WHERE "SMTAPP"."XF_COMMENTS"."RECORD_ID" = poz.Id 
                )
    ) xfUzivId 
FROM HF_POZIADAVKY poz

(fixed a quote in the first WHERE).

You have two queries over SMTAPP.XF_COMMENTS. Some databases automatically assume you're using the inner most element with the name specified, but it's a bit confusing, i.e. the correlation predicate WHERE "SMTAPP"."XF_COMMENTS"."RECORD_ID" = poz.Id, does it refer to the outer SMTAPP.XF_COMMENTS or the inner one? Some db's assume automatically the inner one, but others don't. It's best to alias one:

SELECT 
    poz.Id,
    (
        SELECT TEXT FROM "SMTAPP"."XF_COMMENTS" 
        WHERE "SMTAPP"."XF_COMMENTS"."DATE" =
                (
                    SELECT MAX(C."DATE") AS "Date" 
                    FROM "SMTAPP"."XF_COMMENTS" C
                    WHERE C."RECORD_ID" = poz.Id 
                )
    ) xfUzivId 
FROM HF_POZIADAVKY poz

In QuerySpec, 'As()' on a query is aliasing the query's resultset, 'TargetAs()' is specifying the alias for the target of the query. However we can do that with the field as well, as the field is all we specify here and the field implicitly defines the target. So your query becomes:

var pr = qf.Create()
            .Select(
                HfPoziadavkyFields.Id,
                qf.Create()
                    .Select(XfCommentsFields.Text)
                    .Where(XfCommentsFields.Date ==
                        qf.Create()
                            .Select(XfCommentsFields.Date.Source("C"))
                            .CorrelatedOver(XfCommentsFields.RecordId.Source("C")==HfPoziadavkyFields.Id)
                            .Max())
                    .As("xfUzivId"));

(not tested, but I think this is what you wanted). It aliases the inner most query on XfComments with 'C'

This will execute 2 queries, as the xfUzivId query is not a scalar query but will return potentially multiple rows.

Yeah, the predicate should use XfCommentsFields.Date not XfCommentsFields.Id.

I ran the query, tried to fix it with no luck. Its throwing this error.

Couldn't create any correlation filter lambda because the nested query didn't have any correlation filters. Please specify a filter in the nested query to tie the nested query to the parent query

And I'm still kind a confused how aliasing using the .Source("C") method work.

I undestand that it will use this value instead of the table name e.g. "C"."ID", but I don't understand how to create the corresponding table alias.

The table should be aliased automatically.

The following:

var q = qf.Create() 
         .Select(
             OrderFields.OrderDate.Source("C"));

Generates:

SELECT [C].[OrderDate] FROM [Northwind].[dbo].[Orders]  [C]