Column alias

Hi,

A result set contains a few joined tables. To sort by a column of joined entity I have to add a projection. With pagination, generated SQL code throws an exception - "column ambiguously defined", in case the result set has columns with identical name (from different entities).

    /// <summary>
    /// Sets an additional projection to include a sorting field in ORDER BY sql statement
    /// </summary>
    private void ApplyProjectionForSorting()
    {
        if (Query == null || Query.Order == null || String.IsNullOrEmpty(Query.Order.ToString())) return;

        var projectionDetails = Query.Order.ToString().Split(' ').First().Split('.');

        var field = (projectionDetails.Length == 1) ? projectionDetails[0] : projectionDetails.Last();
        var entityName = (projectionDetails.Length > 1) ? projectionDetails[projectionDetails.Length - 2].ToUpper() : String.Empty;

        if (String.IsNullOrEmpty(entityName))
        {
            if (field.ToLower() != "id")
            {
                Query.Projection.Add(field);
            }
            else
            {
                ApplyParentChildCriteriaForSorting();
            }
        }
        else
        {
            if (_mappings.ContainsKey(entityName))
            {
                **Query.Projection.Add(_mappings[entityName], field);**
            }
        }
    }

Generated SQL:

SELECT t0.*, ROWNUM RowNumber FROM ( SELECT DISTINCT t0.Id, t1.Id FROM ST.PRODUCTDTLDISCOUNT t0 INNER JOIN ST.PRODUCTDTL t1 ON t0.PRODUCTDTLID = t1.Id WHERE t0.APPLICATIONDISCOUNT_ID = 191 ORDER BY t1.Id ) t0 WHERE ROWNUM <= 20;

Is that possible to add a column alias for projection to get SQL code like

.... SELECT DISTINCT t0.Id, t1.Id as t1_id FROM ST ....

Thanks

This is a problem if you have a projection with more than one column with the same name (e.g. Id specified twice in your case above), and you are specifying that the query should be distinct and you are using SQL Server which requires we create a wrapping query to support paging.

Unfortunately due to the way that we have to handle SQL Server paging this means that this is not currently supported and we do not currently provide any mechanism for specifying column aliasing as this is something which LightSpeed needs to handle internally. We hope to support this in the future and I have added an item on to our backlog to look at this but for now you will need to use a workaround of either using a stored procedure if that is possible (it may not given your need for the application driven filtering), or to perform the paging operation client side (which will lead to negative performance that may be significant depending on the number of rows which that join would return).

Jeremy

Thank you!