Home Datapipelines
New Blog Posts: Merging Reports - Part 1 and Part 2

Sorting on Calc Field

edited September 2005 in Datapipelines
Hi,

I was wondering if it is possible to sort on calc field (expression). I
have a table which has a datetime field (MySQL). I am trying to use
calcs to extract the year (using YEAR(TableName.DateField)) and month
(using MONTH(TableName.DateField)) and sort on those calcs.

The sorting does not seem to work when I sort on these expressions.

The generated SQL query is attempting to sort by a number rather than
the expression (or the expression alias).

Here is an extract of the SQL statement generated by ReportBuilder:

SELECT Activity.ActivityDate, Activity.Description, YEAR(ActivityDate)
YEAR_ActivityDate
FROM Activity Activity
ORDER BY 67

Thank you.

Comments

  • edited September 2005

    For a 'calc field', the column position of the field as it appears in the
    Select clause is used in the Order By

    example:

    Select CustNo,
    Sum(AmountPaid)
    from Orders
    Order By 2

    In the above statement, the Order By 2 refers to the calc field, which
    appears as the 2nd field listed by the Select clause



    --
    Nard Moseley
    Digital Metaphors Corporation
    http://www.digital-metaphors.com



    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited September 2005
    I see. However, for some reason I cannot join a detail pipeline to a
    master pipeline that is sorted by a calc field.

  • edited September 2005

    That is a limitation of the visual linking. DataPipeline linking requires
    that the detail data be sorted by the linking field(s). The automatica
    linking feature works by generating special linking SQL for the detail
    query.

    The solution for this case is to manully link the datapipelines via code.
    Here is an example...

    http://www.digital-metaphors.com/tips/LinkDADEPipelines.zip



    --
    Nard Moseley
    Digital Metaphors Corporation
    http://www.digital-metaphors.com


    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.