Strange SQL being generated by grouped criteria

BIZ TMS Aurelius
1

I have a Linq issue where the aliases I assign get ignored, leading to an incorrect SQL query.

This is the Linq:

  oCrit := oMgr.Find<TPosting>
    .select(
      TProjections.ProjectionList
      .Add( TProjections.Alias( TProjections.Prop( 'PROJID' ), 'PROJID' ) )
      .Add(
        TProjections.Alias(
          TProjections.Add(
            TProjections.Sum( 'DURATION' ),
            TProjections.Condition(
              Linq['ADJUSTMENT'].IsNull and Linq['STOPTIME'].IsNull,
              // project is running
              TProjections.Literal<TDateTime>( now ) - TLinq['STARTTIME'],
              // project is not running
              TProjections.Literal<integer>( 0 ) )
          ), 'SUM_DURATION' )
      )
      .Add( TProjections.Group( 'PROJID' ) )
    )
    .where( ( Linq['USERID'] = AUserID )
        and ( Linq['STARTTIME'] >= ADate )
        and ( Linq['STARTTIME'] < EndOfTheDay( ADate ) ) )
    .ListValues;

And this is the SQL generated:

SELECT 
  A.PROJID As f0_, 
  (sum(A.DURATION) + 
    (Case When (A.ADJUSTMENT Is Null And A.STOPTIME Is Null) Then
      (TIMESTAMP ''2022-10-07 15:26:13'' - A.STARTTIME) Else 0 End)) As f1_, 
  A.PROJID As f2_
FROM POSTINGS A
WHERE  ((A.USERID = :p_0 And A.STARTTIME >= :p_1) And A.STARTTIME < p_2)
GROUP BY  A.PROJID

To explain, the records contain three fields of interest, ADJUSTMENT, STOPTIME and DURATION. When ADJUSTMENT and STOPTIME are both null, I need to add now() - STARTTIME to the DURATION, then group it all by PROJID.

But the SQL generated seems to ignore the Alias() projections completely thus producing illegal SQL.

Also, why the second PROJID (f2_)?

Probably, my Linq is wrong but I can't see how and where...

2

The aliases in SQL are internal and automatically generated by Aurelius.

The alias you use in projections are only used to identify the fields through the TCriteriaResult object.

The second PROJID happens because you added it yourself by creating a Group projection. You should just group the first projection, this way:

   .Add( TProjections.Group(TProjections.Alias( TProjections.Prop( 'PROJID' ), 'PROJID' ) ))

or, even better, using the new "Linq" syntax. The one you are using is valid by "old" and is harder to read:

  .Add(Linq['PROJID'].Group.As_('PROJID')

In any case, I can't find an error in your SQL just by reading it. What is the exact problem you are having with it?