Conditional sum in query

[GOMEZEL_DINO]

Hello!

Is possible in Aurelius to do a conditional sum?

Example:

Select PRODUCT_ID,  Sum(QTY * If(STOCK_INFLUENCE = "-", -1, 1)) As ON_STOCK 
From doc_items
where PRODUCT_ID=7

I'm trying to sum the quantity based on the document type (+qty if document is incoming, -qty if it is outgoing).

Kind regards

[Weetch_Russell (Russell Weetch)]

You should be able to do that using

var
  Statement: IDBStatement;
  DatasetStatement: IDBDatasetStatement;
  ADataset: TDataset;
begin
  Statement := Connection.CreateStatement;
  Statement.SetSQLCommand(SQL);
    
  DatasetStatement := (Statement as IDBDatasetStatement);
  DatasetStatement.GetDataset.Open;
  ADataset := DatasetStatement.GetDataset;
end;

[GOMEZEL_DINO]

I meant to do this using Linq. Is possible to conditionally sum with Linq?

[wlandgraf (Wagner Landgraf)]

Yes, for condition part you should use the Condition projection:

https://doc.tmssoftware.com/biz/aurelius/guide/queries.html#condition

      TProjections.Condition(
        Linq['StockInfluence'] = '-',
        Linq.Literal<Integer>(-1),
        Linq.Literal<Integer>(1))

[GOMEZEL_DINO]

Thank you!