SQL missing sum/group by functionality

[Shawn Smalley]

I have a data source that works fine, but as soon as I add either a sum column or a group by statement, or both, I get SQL error SQL0122. Are these not supported?

[Johnny Major]

What is the SQL of the data source?

[Shawn Smalley]

SELECT
mprod,
mqiss,
mqreq,
mopno,
pqord - pqrec AS qoord,
rrn(FMAL01) AS rrn1,
rrn(HPOL23) AS rrn2
FROM
fmal01
LEFT OUTER JOIN hpol23 ON mprod = pprod
WHERE
(
mord = vvin_virtual('shopOrder', '99999999', 'num', 8, 0)
)


I've been replacing the vvin_virtual with the value 134888 for testing

[Johnny Major]

Which column are you trying to summarize or group by in the grid?

[Shawn Smalley]

Ideally I would summarize qoord and group by mprod

[Johnny Major]

Can you provide the statement you're trying to execute when you receive the SQL error?

Thanks

[Shawn Smalley]

SELECT
mprod,
mqiss,
mqreq,
mopno,
SUM(pqord - pqrec) AS qoord,
rrn(FMAL01) AS rrn1,
rrn(HPOL23) AS rrn2
FROM
fmal01
LEFT OUTER JOIN hpol23 ON mprod = pprod
WHERE
(
mord = vvin_virtual('shopOrder', '99999999', 'num', 8, 0)
)
GROUP BY mprod

[Johnny Major]

It's showing you that error because the SQL statement is invalid. To resolve it, you'll need to include additional columns in the 'group by' clause.