Hi,

i was searching forum i tought i have found the answer to my problems but that didn't happen. Read links that should gave me an answer: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3382&HighLight=1 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1533&HighLight=1 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6370&HighLight=1

I have the following problem converting to c# code (need this T-Sql's = the same result):

1)

select tbl.* from Table tbl
where tbl.YearFrom <= 2007
AND tbl.Id in (select TOP 1 tbl2.id
                  from Table  tbl2
                  where tbl2.Note = tbl.Note
                  and tbl2.YearFrom <= 2007
                  order by tbl2.YearFrom desc)

or 2)

select tbl1.*  
from Table tbl1 
WHERE tbl1.YearFrom <= 2007
AND tbl1.YearFrom = 
    (select max( tbl2.YearFrom ) 
    FROM Table tbl2 
    WHERE tbl2.Note = tbl1.Note
     and tbl2.YearFrom <= 2007)

or 3)

select tbl1.*
from Table tbl1 
WHERE tbl1.YearFrom <= 2007
GROUP BY tbl1.Id, tbl1.Note, tbl1.YearFrom  
HAVING tbl1.YearFrom = (select max( tbl2.YearFrom ) 
                FROM Table tbl2 WHERE tbl2.Note = tbl1.Note)

Code lines that shoul cover these 3 queries are: 1)

            IPredicateExpression subQuery = new PredicateExpression();
            subQuery.Add(PredicateFactory.CompareExpression(TableFieldIndex.Note, ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(TableFieldIndex.Note))));
            subQuery.Add(PredicateFactory.CompareValue(TableFieldIndex.YearFrom, ComparisonOperator.LessEqual, poslovnaGodina));

            ISortClause innerSortComponent =  SortClauseFactory.Create(
                    TableFieldIndex.YearFrom, SortOperator.Descending);
            ISortExpression innerSorter = new SortExpression(innerSortComponent);

            IEntityField2 field1 = EntityFieldFactory.Create(TableFieldIndex.Id);
            field1.ObjectAlias = "A1";

            IEntityField2 field2 = EntityFieldFactory.Create(TableFieldIndex.Id);
            field2.ObjectAlias = "A2";

            RelationPredicateBucket filter = new RelationPredicateBucket(); 
            filter.PredicateExpression.Add( 
                new FieldCompareSetPredicate(
                field1, null,
                field2, null,
                SetOperator.In,
                subQuery,
                null,
                "A1", 
                1,
                innerSorter
                ));

            filter.PredicateExpression.AddWithAnd(PredicateFactory.CompareValue(TableFieldIndex.YearFrom, ComparisonOperator.LessEqual, poslovnaGodina, "A1"));

            _adapter.FetchEntityCollection(_amortizacijskeGrupe, filter, 0, null);

2)

            IPredicateExpression subQuery = new PredicateExpression();
            subQuery.Add(PredicateFactory.CompareExpression(TableFieldIndex.Note, ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(TableFieldIndex.Note))));
            subQuery.Add(PredicateFactory.CompareValue(TableFieldIndex.YearFrom, ComparisonOperator.LessEqual, poslovnaGodina));

            IEntityField2 field1 = EntityFieldFactory.Create(TableFieldIndex.YearFrom);
            field1.ObjectAlias = "A1";

            IEntityField2 field2 = EntityFieldFactory.Create(TableFieldIndex.YearFrom);
            field2.ObjectAlias = "A2";
            field2.AggregateFunctionToApply = AggregateFunction.Max;

            RelationPredicateBucket filter = new RelationPredicateBucket(); 
            filter.PredicateExpression.Add( 
                new FieldCompareSetPredicate(
                field1, null,
                field2, null,
                SetOperator.Equal,
                subQuery,
                null,
                "A1", 
                1,
                null
                ));

            filter.PredicateExpression.AddWithAnd(PredicateFactory.CompareValue(TableFieldIndex.YearFrom, ComparisonOperator.LessEqual, poslovnaGodina, "A1"));

            _adapter.FetchEntityCollection(_amortizacijskeGrupe, filter, 0, null);

3) i haven't tried to write...

Though I have tried to play with aliases inside out I haven't get the right solution.

Generated T-SQL: 1)

exec sp_executesql N'
SELECT dbo.Table.Id, dbo.Table.Note, dbo.Table.YearFrom  -- [i]note: no alias?[/i]
FROM dbo.Table
[b]
WHERE ( A1.Id IN                                                -- don't understand how did I get that?!??!
    (SELECT TOP 1 MAX(A2.* FROM dbo.Table  
    WHERE ( A1.Id IN 
        (SELECT TOP 1 MAX(A2.Id) AS Id FROM dbo.Table AS A2  
        WHERE ( dbo.Table.Note = dbo.Table.Note 
            AND dbo.Table.YearFrom <= @YearFrom1) 
        ORDER BY dbo.Table.YearFrom DESC) 
[/b]
AND A1.YearFrom <= @YearFrom2)', 
N'@YearFrom1 decimal(4,0),@YearFrom2 decimal(4,0)', @YearFrom1 = 2006, @YearFrom2 = 2006

2)

exec sp_executesql N'
SELECT dbo.Table.Id, dbo.Table.Note, dbo.Table.YearFrom
FROM dbo.Table
[b]
WHERE ( A1.YearFrom =                               -- don't understand how did I get that?!??!
    (SELECT TOP 1 MAX(A2.* 
    FROM dbo.Table  
    WHERE ( A1.YearFrom = 
[/b]
        (SELECT TOP 1 MAX(A2.YearFrom) AS YearFrom 
        FROM dbo.Table AS A2  
        WHERE ( dbo.Table.Note = dbo.Table.Note 
        AND dbo.Table.YearFrom <= @YearFrom1)) 
AND A1.YearFrom <= @YearFrom2)', N'@YearFrom1 decimal(4,0),@YearFrom2 decimal(4,0)', @YearFrom1 = 2006, @YearFrom2 = 2006

3) didn't tried it.

So hope You could help. How to manage to get aliases in from clause (is it possible) in other words how to get T-Sql i wrote at the beggining under 1) paragraph? LLBLGen ver 1.0.2.0.0.5.1.final (November, 8, 2005)

I have found that You had problem with aliases in some version, hope that my working version isn't right that one

Thx! Greatings, Preky

The best thing to start with to find out what's wrong with your code, is to examine the generated SQL. And compare it with the supposed to be generated SQL.

To examine the generated SQL, you should turn SQL tracing on, please refer to the LLBLGen Pro manual "Using the generated code -> Troubleshooting and debugging"

Well,

this is not helping at all... I think there might be some bug...(maybe it's mine but I don't see that) How I have figured that out:

            IRelationPredicateBucket filter = new RelationPredicateBucket();

            IPredicateExpression subQueryFilter = new PredicateExpression();
            subQueryFilter.Add(PredicateFactory.CompareExpression(TableFieldIndex.Note, ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(TableFieldIndex.Note))/*, "A2"*/));

            ISortClause innerSortComponent = SortClauseFactory.Create(
                TableFieldIndex.YearFrom, SortOperator.Descending);
            ISortExpression innerSorter = new SortExpression(innerSortComponent);

            IEntityField2 field1 = EntityFieldFactory.Create(TableFieldIndex.Id);

            IEntityField2 field2 = EntityFieldFactory.Create(TableFieldIndex.Id);

            filter.PredicateExpression.Add( 
                new FieldCompareSetPredicate(
                field1, null,
                field2, null,
                SetOperator.In,
                subQueryFilter,
                null,
                string.Empty /*"A1"*/, 
                1,
                innerSorter
                ));

            filter.PredicateExpression.AddWithAnd(PredicateFactory.CompareValue(TableFieldIndex.YearFrom, ComparisonOperator.LessEqual, poslovnaGodina /*, "A1"*/));

            _adapter.FetchEntityCollection(_amortizacijskeGrupe, filter, 0, null);

gives me "correct" (expected) generated T-Sql but without aliases (as they aren't defined)

exec sp_executesql N'
SELECT dbo.Table.* FROM dbo.Table  
    WHERE ( dbo.Table.Id IN (
                                                    SELECT TOP 1 dbo.Table.Id FROM dbo.Table  
                                                    WHERE ( dbo.Table.Note = dbo.Table.Note) 
                                                    ORDER BY dbo.Table.YearFrom DESC) 
    AND dbo.Table.YearFrom <= @YearFrom1)', N'@YearFrom1 decimal(4,0)', @YearFrom1 = 2006

Just a minor change of C# code above (defining aliases inside subselect)

IRelationPredicateBucket filter = new RelationPredicateBucket();

            IPredicateExpression subQueryFilter = new PredicateExpression();
            subQueryFilter.Add(PredicateFactory.CompareExpression(TableFieldIndex.Note, ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(TableFieldIndex.Note)), "A2"));

            ISortClause innerSortComponent = SortClauseFactory.Create(
                TableFieldIndex.YearFrom, SortOperator.Descending, "A2");
            ISortExpression innerSorter = new SortExpression(innerSortComponent);

            IEntityField2 field1 = EntityFieldFactory.Create(TableFieldIndex.Id);

            IEntityField2 field2 = EntityFieldFactory.Create(TableFieldIndex.Id);
            field2.ObjectAlias = "A2";

            filter.PredicateExpression.Add( 
                new FieldCompareSetPredicate(
                field1, null,
                field2, null,
                SetOperator.In,
                subQueryFilter,
                null,
                string.Empty /*"A1"*/, 
                1,
                innerSorter
                ));

            filter.PredicateExpression.AddWithAnd(PredicateFactory.CompareValue(TableFieldIndex.YearFrom, ComparisonOperator.LessEqual, poslovnaGodina /*, "A1"*/));

            _adapter.FetchEntityCollection(_amortizacijskeGrupe, filter, 0, null);

exec sp_executesql N'
SELECT dbo.Table.* FROM dbo.Table  
    WHERE ( dbo.Table.Id IN (
                                                    SELECT TOP 1 A2.Id FROM dbo.* FROM dbo.Table  
                                                    WHERE ( dbo.Table.Id IN (
                                                                                                    SELECT TOP 1 A2.Id FROM dbo.Table AS A2  
                                                                                                    WHERE ( A2.Oznaka = dbo.Table.Oznaka) 
                                                                                                    ORDER BY A2.PoslovnaGodinaOd DESC) 
                                                    AND dbo.Table.PoslovnaGodinaOd <= @PoslovnaGodinaOd1)', N'@PoslovnaGodinaOd1 decimal(4,0)', @PoslovnaGodinaOd1 = 2006

and that's the problem... somehow aliases affected of generation T-Sql and created invalid output. I hope it's my problem... can somebody check this please. I would like to now if I'm right or not.

Thx! Greating, Preky

Your code shows 1 field compare set predicate, though the sql shows 2. Please show the code which creates 2 fieldcompareset predicates.

(edit): you perhaps also copied the query wrong: SELECT dbo.Table.* FROM dbo.Table WHERE ( dbo.Table.Id IN ( SELECT TOP 1 A2.Id FROM dbo.* FROM dbo.Table

two times FROM in that query.

Otis, thank You for reply...

I have copied complete T-Sql from SQL Profiler.

Yes, in code there is one compare set predicate but after I have uncommented aliases (from the first code block copied above) i got that invalid query that seems uncomplete and copied false (acts like I have two compare set predicates defined - because of that I thought I have wrote something wrong and tried to get some info on that here on forum), but I got that in Profiler.

I ran across similar T-Sql's people were asking You and I played around with the code I have submited but it didn't get me far.

As I have seen people were either joining to different table or having subselect from some other table so they didn't used aliases as SQL knows to translate these queries but in my case i have subselect to the same table and must use aliases to tell SQL what I want to do.

Also, as my colleague tried to solve the same thing, he tried to make "self table join" also using aliases but told me that generated T-SQL didn't used defined aliases.

select a1.* from Table a1
inner join Table a2 on a1.Id = a2.Id

I also ran across post where you have realized that "alias emiter" (if I remeber the term correctly) didn't create aliases for select from just one table. Don't know if that has anything to do with it... I thought just to mention this to remeber You and help to find the problem.

I haven't managed to get alias for that first "from Table" clause also. So I thought it has maybe has to do with LLBLGen version. I don't know!?

Thx!

Greatings, Preky

Ok, to get back to the real issue: - you've posted some code. However you haven't posted the real query it generates. Could you please post that SQL for me? Use DQE tracing (see Troubleshooting and debugging) to get the query generated.

(Doesn't matter if the sql gives an error).

We then can see which code has to be adjusted because some alias is mentioned wrong.

Otis thx! here it is, hope I have understood you correctly....

            IRelationPredicateBucket filter = new RelationPredicateBucket();

            IPredicateExpression subQueryFilter = new PredicateExpression();
            subQueryFilter.Add(PredicateFactory.CompareExpression(AmortizacijskaGrupaFieldIndex.Oznaka, ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(AmortizacijskaGrupaFieldIndex.Oznaka)), "A2"));
            //subQueryFilter.Add(PredicateFactory.CompareValue(AmortizacijskaGrupaFieldIndex.PoslovnaGodinaOd, ComparisonOperator.LessEqual, poslovnaGodina, "A2"));

            ISortClause innerSortComponent = SortClauseFactory.Create(
                AmortizacijskaGrupaFieldIndex.PoslovnaGodinaOd, SortOperator.Descending, "A2");
            ISortExpression innerSorter = new SortExpression(innerSortComponent);

            IEntityField2 field1 = EntityFieldFactory.Create(AmortizacijskaGrupaFieldIndex.Id);
            //field1.ObjectAlias = "A1";

            IEntityField2 field2 = EntityFieldFactory.Create(AmortizacijskaGrupaFieldIndex.Id);
            field2.ObjectAlias = "A2";

            //RelationPredicateBucket filter = new RelationPredicateBucket(); 
            filter.PredicateExpression.Add( 
                new FieldCompareSetPredicate(
                field1, null,
                field2, null,
                SetOperator.In,
                subQueryFilter,
                null,
                string.Empty , 
                1,
                innerSorter
                ));

            filter.PredicateExpression.AddWithAnd(PredicateFactory.CompareValue(AmortizacijskaGrupaFieldIndex.PoslovnaGodinaOd, ComparisonOperator.LessEqual, poslovnaGodina));

            _adapter.FetchEntityCollection(_amortizacijskeGrupe, filter, 0, null);

        
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT TOP 1 [A2].[Id] FROM [dbo].[ostAmortizacijskeGrupe] AS [A2]  
                    WHERE ( [A2].[Oznaka] = [dbo].[ostAmortizacijskeGrupe].[Oznaka]) 
                    ORDER BY [A2].[PoslovnaGodinaOd] DESC
Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [dbo].[ostAmortizacijskeGrupe].[Id], [dbo].[ostAmortizacijskeGrupe].[Oznaka], 
                                [dbo].[ostAmortizacijskeGrupe].[Naziv], [dbo].[ostAmortizacijskeGrupe].[PoslovnaGodinaOd], 
                                [dbo].[ostAmortizacijskeGrupe].[Stopa], [dbo].[ostAmortizacijskeGrupe].[UbrzanaStopa], 
                                [dbo].[ostAmortizacijskeGrupe].[Napomena], [dbo].[ostAmortizacijskeGrupe].[KorisnikUnosa], 
                                [dbo].[ostAmortizacijskeGrupe].[VrijemeUnosa], [dbo].[ostAmortizacijskeGrupe].[KorisnikPromjene], 
                                [dbo].[ostAmortizacijskeGrupe].[VrijemePromjene], [dbo].[ostAmortizacijskeGrupe].[Timestamp], 
                                [dbo].[ostAmortizacijskeGrupe].[PoslovnaGodinaDo] FROM [dbo].[ostAmortizacijskeGrupe]  
                                WHERE ( [dbo].[ostAmortizacijskeGrupe].[Id] IN 
                                                (SELECT TOP 1 [A2].[Id] FROM [dbo].[ostAmortizacijskeGrupe] AS [A2]  
                                                                WHERE ( [A2].[Oznaka] = [dbo].[ostAmortizacijskeGrupe].[Oznaka]) 
                                                                ORDER BY [A2].[PoslovnaGodinaOd] DESC) 
                                                                AND [dbo].[ostAmortizacijskeGrupe].[PoslovnaGodinaOd] <= @PoslovnaGodinaOd1)
    Parameter: @PoslovnaGodinaOd1 : Decimal. Length: 0. Precision: 4. Scale: 0. Direction: Input. Value: 2006.
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
An unhandled exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in sd.llblgen.pro.ormsupportclasses.net11.dll

Additional information: An exception was caught during the execution of a retrieval query: Line 1: Incorrect syntax near '*'.
Incorrect syntax near the keyword 'ORDER'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

SQL PROFILER:

exec sp_executesql N'SELECT dbo.ostAmortizacijskeGrupe.Id, dbo.ostAmortizacijskeGrupe.Oznaka, dbo.ostAmortizacijskeGrupe.Naziv, 
                                                        dbo.ostAmortizacijskeGrupe.PoslovnaGodinaOd, dbo.ostAmortizacijskeGrupe.Stopa, dbo.ostAmortizacijskeGrupe.UbrzanaStopa, 
                                                        dbo.ostAmortizacijskeGrupe.Napomena, dbo.ostAmortizacijskeGrupe.KorisnikUnosa, dbo.ostAmortizacijskeGrupe.VrijemeUnosa, 
                                                        dbo.ostAmortizacijskeGrupe.KorisnikPromjene, dbo.ostAmortizacijskeGrupe.VrijemePromjene, 
                                                        dbo.ostAmortizacijskeGrupe.Timestamp, dbo.ostAmortizacijskeGrupe.PoslovnaGodinaDo 
                                                        FROM dbo.ostAmortizacijskeGrupe  
                                                        WHERE ( dbo.ostAmortizacijskeGrupe.Id IN 
                                                                            (SELECT TOP 1 A2.Id FROM dbo.* FROM dbo.ostAmortizacijskeGrupe  
                                                                                    WHERE ( dbo.ostAmortizacijskeGrupe.Id IN 
                                                                                                (SELECT TOP 1 A2.Id FROM dbo.ostAmortizacijskeGrupe AS A2  
                                                                                                 WHERE ( A2.Oznaka = dbo.ostAmortizacijskeGrupe.Oznaka) 
                                                                                                 ORDER BY A2.PoslovnaGodinaOd DESC) 
                                                                            AND dbo.ostAmortizacijskeGrupe.PoslovnaGodinaOd <= @PoslovnaGodinaOd1)', 
                                                                            N'@PoslovnaGodinaOd1 decimal(4,0)', @PoslovnaGodinaOd1 = 2006

---------------------------------------------------- WITHOUT ALIAS ----------------------------------------------------

            IRelationPredicateBucket filter = new RelationPredicateBucket();

            IPredicateExpression subQueryFilter = new PredicateExpression();
            subQueryFilter.Add(PredicateFactory.CompareExpression(AmortizacijskaGrupaFieldIndex.Oznaka, ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(AmortizacijskaGrupaFieldIndex.Oznaka))/*, "A2"*/));
            //subQueryFilter.Add(PredicateFactory.CompareValue(AmortizacijskaGrupaFieldIndex.PoslovnaGodinaOd, ComparisonOperator.LessEqual, poslovnaGodina, "A2"));

            ISortClause innerSortComponent = SortClauseFactory.Create(
                AmortizacijskaGrupaFieldIndex.PoslovnaGodinaOd, SortOperator.Descending/*, "A2"*/);
            ISortExpression innerSorter = new SortExpression(innerSortComponent);

            IEntityField2 field1 = EntityFieldFactory.Create(AmortizacijskaGrupaFieldIndex.Id);
            //field1.ObjectAlias = "A1";

            IEntityField2 field2 = EntityFieldFactory.Create(AmortizacijskaGrupaFieldIndex.Id);
            //field2.ObjectAlias = "A2";

            //RelationPredicateBucket filter = new RelationPredicateBucket(); 
            filter.PredicateExpression.Add( 
                new FieldCompareSetPredicate(
                field1, null,
                field2, null,
                SetOperator.In,
                subQueryFilter,
                null,
                string.Empty , 
                1,
                innerSorter
                ));

            filter.PredicateExpression.AddWithAnd(PredicateFactory.CompareValue(AmortizacijskaGrupaFieldIndex.PoslovnaGodinaOd, ComparisonOperator.LessEqual, poslovnaGodina));

            _adapter.FetchEntityCollection(_amortizacijskeGrupe, filter, 0, null);

            
OnLoad() FormOdabirAmortizacijskeGrupe
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT TOP 1 [dbo].[ostAmortizacijskeGrupe].[Id] FROM [dbo].[ostAmortizacijskeGrupe]  
                    WHERE ( [dbo].[ostAmortizacijskeGrupe].[Oznaka] = [dbo].[ostAmortizacijskeGrupe].[Oznaka]) 
                    ORDER BY [dbo].[ostAmortizacijskeGrupe].[PoslovnaGodinaOd] DESC
Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [dbo].[ostAmortizacijskeGrupe].[Id], [dbo].[ostAmortizacijskeGrupe].[Oznaka], 
                                [dbo].[ostAmortizacijskeGrupe].[Naziv], [dbo].[ostAmortizacijskeGrupe].[PoslovnaGodinaOd], 
                                [dbo].[ostAmortizacijskeGrupe].[Stopa], [dbo].[ostAmortizacijskeGrupe].[UbrzanaStopa], 
                                [dbo].[ostAmortizacijskeGrupe].[Napomena], [dbo].[ostAmortizacijskeGrupe].[KorisnikUnosa], 
                                [dbo].[ostAmortizacijskeGrupe].[VrijemeUnosa], [dbo].[ostAmortizacijskeGrupe].[KorisnikPromjene], 
                                [dbo].[ostAmortizacijskeGrupe].[VrijemePromjene], [dbo].[ostAmortizacijskeGrupe].[Timestamp], 
                                [dbo].[ostAmortizacijskeGrupe].[PoslovnaGodinaDo] FROM [dbo].[ostAmortizacijskeGrupe]  
                                WHERE ( [dbo].[ostAmortizacijskeGrupe].[Id] IN (
                                                SELECT TOP 1 [dbo].[ostAmortizacijskeGrupe].[Id] FROM [dbo].[ostAmortizacijskeGrupe]  
                                                WHERE ( [dbo].[ostAmortizacijskeGrupe].[Oznaka] = [dbo].[ostAmortizacijskeGrupe].[Oznaka]) 
                                                ORDER BY [dbo].[ostAmortizacijskeGrupe].[PoslovnaGodinaOd] DESC) 
                                                AND [dbo].[ostAmortizacijskeGrupe].[PoslovnaGodinaOd] <= @PoslovnaGodinaOd1)
    Parameter: @PoslovnaGodinaOd1 : Decimal. Length: 0. Precision: 4. Scale: 0. Direction: Input. Value: 2006.
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

SQL PROFILER:

exec sp_executesql N'SELECT dbo.ostAmortizacijskeGrupe.* FROM dbo.ostAmortizacijskeGrupe  
                                        WHERE ( dbo.ostAmortizacijskeGrupe.Id IN 
                                                                (SELECT TOP 1 dbo.ostAmortizacijskeGrupe.Id FROM dbo.ostAmortizacijskeGrupe  
                                                                WHERE ( dbo.ostAmortizacijskeGrupe.Oznaka = dbo.ostAmortizacijskeGrupe.Oznaka) 
                                                                ORDER BY dbo.ostAmortizacijskeGrupe.PoslovnaGodinaOd DESC) 
                                        AND dbo.ostAmortizacijskeGrupe.PoslovnaGodinaOd <= @PoslovnaGodinaOd1)', 
                                        N'@PoslovnaGodinaOd1 decimal(4,0)', @PoslovnaGodinaOd1 = 2006

Greatings, Preky

P.S. I have tried to format text as much as readable, but couldn't quite to do that P.S.S These are copied outputs I got after running code blocks above

What's that '' doing there? No DQE generates that kind of queries. As you see in the traces, the '' isn't there. In your profiler code though a '' is placed after dbo and the exception is caused by that ''. ->

An unhandled exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in sd.llblgen.pro.ormsupportclasses.net11.dll

Additional information: An exception was caught during the execution of a retrieval query: Line 1: Incorrect syntax near '*'. Incorrect syntax near the keyword 'ORDER'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Something is mangling that generated query. Do you have an override method added to the DataAccessAdapter class which overrides OnFetchEntityCollection and modifies the query?

The generated queries are correct SQL, the queries actually ran by the db aren't. Something changes the query text before it's send to the db.

Hi Otis!

Yes You are right and that * was the reason I was writting here. So your DQE is generating queries with column select list, no * marker anywhere? Have I understand you correctly? Does your DQE according that ganerated column select list generates aliases that should be used? Can you explain the metodology that is used to create aliases a bit, is that select list somehow used in step of alias creation?

Yes we have an override method that overrides OnFetchEntityCollection as we needed to shrink queries packeges sent to server (it's hard to believe that we had to do that but there was no alternative at that time).

Can you suggest what we have to do to correct things up but doing the same thing. As our coulmn select list are very, very long we set * instead of it.

Is there a way we can modify select list with * at the end of query selection without messing process of DQE?

Thx! Preky

For the looks of it, it seems your routine to pack the query makes a small mistake somewhere: it doesn't fabricate a correct query, instead it leaves some statements in the query so you'll get these weird, not executable SQL.

So if you could paste the code here which mangles the SQL, it might show where the bug is. But you can also try to debug the routine and check where it does the incorrect string manipulation.

Here are ower overrides:

public override void OnFetchTypedView(IRetrievalQuery selectQuery, IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill)
        {
            string command = selectQuery.Command.CommandText;
            string field1;
            string temp;
            if ( command.IndexOf("GROUP BY") == -1 )
            {
                int pos = 0;
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT ");
                pos = command.IndexOf("AS ", 0);
                if (pos == -1)
                {
                    pos = command.IndexOf(",", 0);
                }
                temp = command.Substring(7/*after select!*/, pos - 7);
                pos = temp.LastIndexOf(".");
                field1 = temp.Substring(0, pos);
                field1 = field1.Trim();
                sb.Append(field1);
                sb.Append(".* ");
                pos = command.IndexOf("FROM");
                sb.Append (command.Substring(pos));
                // remove []...
                sb.Replace("[", "");
                sb.Replace("]", "");
                temp = sb.ToString();
                selectQuery.Command.CommandText = temp;
            }
            base.OnFetchTypedView (selectQuery, fieldCollectionToFetch,dataTableToFill);
        }
        public override void OnFetchEntityCollection(IRetrievalQuery selectQuery, IEntityCollection2 entityCollectionToFetch)
        {
            string command = selectQuery.Command.CommandText;
            string field1;
            string temp; 
            int pos = 0;
            StringBuilder sb = new StringBuilder();

            sb.Append("SELECT ");
            pos = command.IndexOf("AS ", 0);
            if (pos == -1)
            {
                pos = command.IndexOf(",", 0);
            }
            temp = command.Substring(7/*after select!*/, pos - 7);
            pos = temp.LastIndexOf(".");
            field1 = temp.Substring(0, pos);
            field1 = field1.Trim();
            sb.Append(field1);
            sb.Append(".* ");
            pos = command.IndexOf("FROM");
            sb.Append (command.Substring(pos));
            // remove []...
            sb.Replace("[", "");
            sb.Replace("]", "");
            temp = sb.ToString();
            selectQuery.Command.CommandText = temp;
            base.OnFetchEntityCollection (selectQuery, entityCollectionToFetch);
        }
        public override void OnFetchEntity(IRetrievalQuery selectQuery, IEntityFields2 fieldsToFetch)
        {
            string command = selectQuery.Command.CommandText;
            string field1;
            string temp; 
            int pos = 0;
            StringBuilder sb = new StringBuilder();

            sb.Append("SELECT ");
            pos = command.IndexOf("AS ", 0);
            if (pos == -1)
            {
                pos = command.IndexOf(",", 0);
            } 
            temp = command.Substring(7/*after select!*/, pos - 7);
            pos = temp.LastIndexOf(".");
            field1 = temp.Substring(0, pos);
            field1 = field1.Trim();
            sb.Append(field1);
            sb.Append(".* ");
            pos = command.IndexOf("FROM");
            sb.Append (command.Substring(pos));
            // remove []...
            sb.Replace("[", "");
            sb.Replace("]", "");
            temp = sb.ToString();
            selectQuery.Command.CommandText = temp;
            base.OnFetchEntity (selectQuery, fieldsToFetch);
        }

Ok, also to find it, it's probably also easy for you to set a breakpoint in that routine and see what happens with the query that fails. The query is simply not re-constructed properly at some point, which IMHO should be easy to find with a debug step through.

Thx Otis for your help....we had bug... you have changed the way of query generation and guys didn't assume some things

Solved!

Greatings, Preky