Hi all, I am handling numbers of transactions per time. I need to build up a table with rows for each device and each transaction type ("indicators"). There should be one column for every hour (0-23), all filtered for a given date.

This code gives me the total of all trx according to the given filter data. But how can I set a filter for each hour i=0..23 to just sum up SingleTrxSumFields.Number WHERE SingleTrxSumFields.Hour == i in the columns 4..27???

      RelationCollection relations = new RelationCollection();
      relations.Add(GeraetestammdatenEntity.Relations.SingleTrxSumEntityUsingDevId);

      PredicateExpression filter = new PredicateExpression();
      filter.Add(SingleTrxSumFields.DevId == levelId);

      filter.Add(SingleTrxSumFields.Year == year);
      filter.Add(SingleTrxSumFields.Month == month);
      filter.Add(SingleTrxSumFields.Day == day);

      ResultsetFields fields = new ResultsetFields(5);
      fields.DefineField(GeraetestammdatenFields.DevId, 0);
      fields.DefineField(GeraetestammdatenFields.Equipmentnummer, 1);
      fields.DefineField(GeraetestammdatenFields.Typ, 2);
      fields.DefineField(SingleTrxSumFields.IndikatorEf, 3);
[b]
      fields.DefineField(SingleTrxSumFields.Number, 4, AggregateFunction.Sum);
[/b]
      IGroupByCollection groupByClause = new GroupByCollection();
      groupByClause.Add(fields[0]);
      groupByClause.Add(fields[1]);
      groupByClause.Add(fields[2]);
      groupByClause.Add(fields[3]);

      DataTable dynamicList = new DataTable();
      TypedListDAO dao = new TypedListDAO();
      dao.GetMultiAsDataTable(fields, dynamicList, 0, null, filter,
        relations, true, groupByClause, null, 0, 0);

I tried this alternative, but the aggregate function ignores the settings in the filter (so sums up on all devices and all year, month, days...

      RelationCollection relations = new RelationCollection();
      relations.Add(GeraetestammdatenEntity.Relations.SingleTrxSumEntityUsingDevId);

      PredicateExpression filter = new PredicateExpression();
      filter.Add(SingleTrxSumFields.DevId == levelId);

      filter.Add(SingleTrxSumFields.Year == year);
      filter.Add(SingleTrxSumFields.Month == month);
      filter.Add(SingleTrxSumFields.Day == day);

      ResultsetFields fields = new ResultsetFields(28);
      fields.DefineField(GeraetestammdatenFields.DevId, 0);
      fields.DefineField(GeraetestammdatenFields.Equipmentnummer, 1);
      fields.DefineField(GeraetestammdatenFields.Typ, 2);
      fields.DefineField(SingleTrxSumFields.IndikatorEf, 3);
[b]
      int next = 4;
      for (int i = 0; i < 24; i++)
      {
        int col = i + next;
        fields.DefineField(SingleTrxSumFields.Number, col, i.ToString());
        ScalarQueryExpression exp =
          new ScalarQueryExpression(
            SingleTrxSumFields.Number.SetAggregateFunction(AggregateFunction.Sum),
            SingleTrxSumFields.Hour == i);
        fields[col].ExpressionToApply = exp;
      }
[/b]
      IGroupByCollection groupByClause = new GroupByCollection();
      groupByClause.Add(fields[0]);
      groupByClause.Add(fields[1]);
      groupByClause.Add(fields[2]);
      groupByClause.Add(fields[3]);

      DataTable dynamicList = new DataTable();
      TypedListDAO dao = new TypedListDAO();
      dao.GetMultiAsDataTable(fields, dynamicList, 0, null, filter,
        relations, true, groupByClause, null, 0, 0);

Trying to fix this, I added the filter information to the ScalarQueryExpression. Now the DevId filter settings & the hour filter settings were perfect, but the indicator is ignored, which I can not explain or understand at all...

like last example ...
      int next = 4;
      for (int i = 0; i < 24; i++)
      {
        int col = i + next;
        fields.DefineField(SingleTrxSumFields.Number, col, i.ToString());
        ScalarQueryExpression exp =
          new ScalarQueryExpression(
            SingleTrxSumFields.Number.SetAggregateFunction(AggregateFunction.Sum),
            new PredicateExpression(filter)
              .Add(new PredicateExpression(SingleTrxSumFields.DevId == fields[0]))
              .Add(new PredicateExpression(SingleTrxSumFields.IndikatorEf == fields[3]))
              .Add(new PredicateExpression(SingleTrxSumFields.Hour == i))
              );
        fields[col].ExpressionToApply = exp;
      }
...

I am running out of ideas... can anyone help me??? Thanks in advance!!! Anja

can you post the sql query you want to produce?

.Add(new PredicateExpression(SingleTrxSumFields.DevId == fields[0]))

I think this is not needed as you are filtering on a specific DevId at your filter.

.Add(new PredicateExpression(SingleTrxSumFields.IndikatorEf == fields[3]))

I think the problem is that you are using the same table in the main dynamicList and in the ScalarQueryExpression's. You can confirm that if you see the generated SQL (Ref: LLBLGenPro Help - Using generated code - Troubleshooting and Debugging). So you should use aliases to distinguish both (or at lest one) of them. Use the overload of the DefineField() method which accept an object alias.

Hi again, @daelmo: thanks, I will try to integrate aliases and see what they come up with @goose: it's basically the problem of turning rows into columns... here is a simplified SQL query:

SELECT T1.DevId,
   (SELECT Sum(Number) FROM Trx T2 WHERE Hour=0 AND T1.DevId=T2.DevId;),
   (SELECT Sum(Number) FROM Trx T2 WHERE Hour=1 AND T1.DevId=T2.DevID;),
   ...
   (SELECT Sum(Number) FROM Trx T2 WHERE Hour=23 AND T1.DevId=T2.DevID;),
FROM Trx T1;

Is there an quick & easy way??? Anja

As David said and as you showed in your SQL code, you should use Aliases, either in the DynamicList fields definitions or in the inner queries (ScalarQueryExpression).

Hi all, unfortunately, I can't get further with the aliases... propably because I don't really know how to use them with llbl gen... I added this

fields.DefineField(SingleTrxSumFields.IndikatorEf, 3, "Indikator");

and tried to use the alias

.Add(SingleTrxSumFields.IndikatorEf == SingleTrxSumFields.IndikatorEf.SetObjectAlias("Indikator"))

and received a sqlException saying that "Indikator.IndikatorEF" could not be bound... How do I do it propperly??? Thanks in advance! Anja

... how can I unmark the thread as "done"???

I'm not sure if you want to mark this as done or it was done and you posted to ask how you can un-mark it as un-done/re-opened

Anyway, the best thing I can do to cover both of the above possibilities is to mark it as done, and if you want to un-marked (re-opened) just post a reply and it will be automatically be re-opened.

Hi Walaa, thanks for your response. No, I wanted it NOT DONE (and by posting a new msg it was again set to not done :-) I am still having the problem. I just don't know how to use the aliases ... Anja

It should look like the following:

         EntityField number = SingleTrxSumFields.Number;
         number.SetObjectAlias("T2");

         ScalarQueryExpression exp =
         new ScalarQueryExpression(
            number.SetAggregateFunction(AggregateFunction.Sum),
            new PredicateExpression(filter)
             .Add(new PredicateExpression(SingleTrxSumFields.DevId.SetObjectAlias("T2") == fields[0]))
             .Add(new PredicateExpression(SingleTrxSumFields.IndikatorEf.SetObjectAlias("T2") == fields[3]))
             .Add(new PredicateExpression(SingleTrxSumFields.Hour.SetObjectAlias("T2") == i))
             );

Hello again, I changed my code to this and again receive the sqlexception that the variable T2.Number could not be bound.

      for (int i = 0; i < 24; i++)
      {
        // SQL Exception: T2.Number can not be bound...
        int col = i + next;
        EntityField number = SingleTrxSumFields.Number;
        number.SetObjectAlias("T2");

        ScalarQueryExpression exp =
        new ScalarQueryExpression(
           number.SetAggregateFunction(AggregateFunction.Sum), //.SetObjectAlias("T2"), // makes no difference to the error!
           new PredicateExpression()
//          .Add(new PredicateExpression(SingleTrxSumFields.DevId.SetObjectAlias("T2") == fields[0]))
            .Add(new PredicateExpression(SingleTrxSumFields.DevId.SetObjectAlias("T2") == SingleTrxSumFields.DevId))
//          .Add(new PredicateExpression(SingleTrxSumFields.IndikatorEf.SetObjectAlias("T2") == fields[3]))
//          .Add(new PredicateExpression(SingleTrxSumFields.Hour.SetObjectAlias("T2") == i))
//          .Add(new PredicateExpression(SingleTrxSumFields.Hour == i)) // only this line works! :-) has no aliases :-)
            );
        //number.ExpressionToApply = exp; // -> SQL Stack Overflow Exception
        
        fields.DefineField(number, col, i.ToString());
      }

I commented some lines as they only complicate the problem... Any ideas? Anja

Would you please post the generated SQL Query?

Also which LLBLGen Pro runtime library version are you using?

anja wrote:

Hi all, I am handling numbers of transactions per time. I need to build up a table with rows for each device and each transaction type ("indicators"). There should be one column for every hour (0-23), all filtered for a given date.

This code gives me the total of all trx according to the given filter data. But how can I set a filter for each hour i=0..23 to just sum up SingleTrxSumFields.Number WHERE SingleTrxSumFields.Hour == i in the columns 4..27???

      RelationCollection relations = new RelationCollection();
      relations.Add(GeraetestammdatenEntity.Relations.SingleTrxSumEntityUsingDevId);

      PredicateExpression filter = new PredicateExpression();
      filter.Add(SingleTrxSumFields.DevId == levelId);

      filter.Add(SingleTrxSumFields.Year == year);
      filter.Add(SingleTrxSumFields.Month == month);
      filter.Add(SingleTrxSumFields.Day == day);

      ResultsetFields fields = new ResultsetFields(5);
      fields.DefineField(GeraetestammdatenFields.DevId, 0);
      fields.DefineField(GeraetestammdatenFields.Equipmentnummer, 1);
      fields.DefineField(GeraetestammdatenFields.Typ, 2);
      fields.DefineField(SingleTrxSumFields.IndikatorEf, 3);
[b]
      fields.DefineField(SingleTrxSumFields.Number, 4, AggregateFunction.Sum);
[/b]
      IGroupByCollection groupByClause = new GroupByCollection();
      groupByClause.Add(fields[0]);
      groupByClause.Add(fields[1]);
      groupByClause.Add(fields[2]);
      groupByClause.Add(fields[3]);

      DataTable dynamicList = new DataTable();
      TypedListDAO dao = new TypedListDAO();
      dao.GetMultiAsDataTable(fields, dynamicList, 0, null, filter,
        relations, true, groupByClause, null, 0, 0);

I don't really grasp what the need for the Sum is. You already have a field with the # of transactions per hour, namely SingleTrxSumFields.Number, am I correct?

Could you give an example of how your table looks like? When I was setting up 2 tables to try to write a query which would produce the right grouping, I realized there's no need for groupby, you just want to fetch the data without groupby and then have to rotate the table 90 degrees to get the rows per hour horizontally.

Hi Otis,

Otis wrote:

I don't really grasp what the need for the Sum is. You already have a field with the # of transactions per hour, namely SingleTrxSumFields.Number, am I correct?

I do have different devices & different types of transactions and want to add up all transactions that took place at a certain time/hour ... depending on filters set by the user.

next issue will be to show numbers of try per day where I will definitely have to sum up the hours...

Could you give an example of how your table looks like? When I was setting up 2 tables to try to write a query which would produce the right grouping, I realized there's no need for groupby, you just want to fetch the data without groupby and then have to rotate the table 90 degrees to get the rows per hour horizontally.

Actual table: SingleTrxSumID bigint (=primaryKey) DevID bigint Year int Month int Day int Hour int IndikatorEF nvarchar(5) Number int

Version: 2.5final (DEMO) DB: SQL Server Express

I really appreciate all your efforts Thank you! ANJA

2.5 Demo is not the runtime library version Please check the following link to know how to get the RTL version number: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7720

And don't forget about this:

Walaa wrote:

Would you please post the generated SQL Query?

Consult the manual "Using the generated code -> Troubleshooting and debugging" to know how to get the generated SQL query.

Also be careful to not include scalar query filter at the main filter (are not the same). This could cause the alias error. I reformatted your code. Please try this:

ResultsetFields fields = new ResultsetFields(28);
fields.DefineField(GeraetestammdatenFields.DevId, 0, "DevId", "T1");
fields.DefineField(GeraetestammdatenFields.Equipmentnummer, 1 ,"Equipmentnummer" ,"T1");
fields.DefineField(GeraetestammdatenFields.Typ, 2, "Typ", "T1");
fields.DefineField(SingleTrxSumFields.IndikatorEf, 3);

PredicateExpression scalarQueryFilter = new PredicateExpression();
scalarQueryFilter.Add(SingleTrxSumFields.DevId.SetObjectAlias("T2") == fields[0]))
scalarQueryFilter.Add(SingleTrxSumFields.IndikatorEf.SetObjectAlias("T2") == fields[3]))

int next = 4;

for (int i = 0; i < 24; i++)
{
    int col = i + next;
    
    fields.DefineField(SingleTrxSumFields.Number, col, i.ToString());

    ScalarQueryExpression exp = new ScalarQueryExpression(
        SingleTrxSumFields.Number("T2").SetAggregateFunction(AggregateFunction.Sum)
        new PredicateExpression(scalarQueryFilter)
            .Add(new PredicateExpression(SingleTrxSumFields.Hour.SetObjectAlias("T2") == i))  );

    fields[col].SetExpression(exp);
}

RelationCollection relations = new RelationCollection();
relations.Add(GeraetestammdatenEntity.Relations.SingleTrxSumEntityUsingDevId);

IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);
groupByClause.Add(fields[2]);
groupByClause.Add(fields[3]);

PredicateExpression filter = new PredicateExpression();
filter.Add(SingleTrxSumFields.DevId.SetObjectAlias("T1") == levelId);
filter.Add(SingleTrxSumFields.Year.SetObjectAlias("T1") == year);
filter.Add(SingleTrxSumFields.Month.SetObjectAlias("T1") == month);
filter.Add(SingleTrxSumFields.Day.SetObjectAlias("T1") == day);

DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, filter,
relations, true, groupByClause, null, 0, 0);

WOW thank you that made my day! I had to remove the T1 alias as the compiler again could not bind it (I don't know), but then it worked! I'm pretty sure that the problem was my attempt to reuse the filter.... ** Thanks to all of your for your GREAT SUPPORT & wishing you all a very merry Xmas! Anja

**