Ignition SQL query dataset expression function

Ignition
,
1

Hello all,

I would like to implement the following query in IGNITION and am encountering problems.

Using the following query I get data sorted as follows and stored in the tag RAW_DATA as dataset.

"SELECT ORDERNR, PIECES, DURATION, StartDate&Time, EndDate&Time
FROM DATABASE"

Dataset:
ORDERNR | PIECES | DURATION | StartTDate| StartTime EndDate&Time
0101 | 0 | 100 | 20230630 | 063000 | 20230630 | 083000 |
0101 | 10 | 250 | 20230630 | 103000 | 20230630 | 123000 |
0101 | 5 | 150 | 20230631 | 093000 | 20230631 | 103000 |
0102 | 20 | 200 | 20230630 | 063000 | 20230630 | 083000 |

I would like to summarize the data in an EXPRESSION function as follows.

Count all PIECES & DURATION with the same ORDERNR together.
Filter earliest StartTime and StartTime & latest EndDate EndTime of an ORDERNR.

The summarized output should then be as follows.

ORDERNR, PIECES, DURATION, StartDate, StartTime, EndDate, EndTime
0101 | 15 | 500 | 20230630 | 063000 | 20230631 | 103000 |
0102 | 20 | 200 | 20230630 | 063000 | 20230630 | 083000 |

With my approach I get unfortunately all the time an error message.

Could someone help me?

2

You should probably do this directly in the query.
It's not clear what values should be selected for the dates, can you clarify ?

try something like this:

select
  ordernr,
  sum(pieces) as 'sum pieces',
  sum(duraction) as 'sum duration',
  min(StartDate) as StartDate,
  min(StartTime) as StartTime
  max(EndDate) as EndDate,
  max(EndTime) as EndTime
from
  database
group by
  ordernr

if you REALLY want an expression... frankly I'd use a script. I'm sure @pturmel has exactly what's needed to do this in an expression with his simulation aids module, but if you're not using it, use a script.

When you have an error... it might be a good idea to tell us what it is if you want help fixing it. We're not seers.

Also, you can use markdown's table syntax when you want to display a table:

| col1 | col2 | col3 |
| -- | -- | -- |
| foo1 | foo2 | foo3 |
| bar1 | bar2 | bar3 |

will be formatted like this :

col1 col2 col3
foo1 foo2 foo3
bar1 bar2 bar3
2 Likes