Hi Luca, It's easy to duplicate it, let's see
Database schema: create class TestClass create property TestClass.Name string create property TestClass.IntF integer Working SQL select Name, max (IntF) from TestClass group by Name Failed SQL in version 2.1.8 but worked with version 1.7.8 select Name, $v1 from TestClass LET $v1 = max(IntF) group by Name The error is also clear, LET statement does not support any aggregate function yet com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error on parsing command at position #60: Aggregate function cannot be used in LET clause together with GROUP BY Command: select Name, $v1 from TestClass LET $v1 = max(IntF) group by Name My Best, Hung Tran On Tuesday, January 19, 2016 at 3:09:39 PM UTC+7, Luca Son wrote: > > Hi Hung Tran, > > could you post your code or do you have a small DB test to reproduce the > issue ? > > Thans > > > Il giorno martedì 19 gennaio 2016 03:30:49 UTC+1, Hung Tran ha scritto: >> >> Hi, >> >> I am migrating from version 1.7.8 to version 2.1.8. The below query is >> failed to execute >> >> SELECT $v1 as Id, >> $v2 AS EmailAddress, >> Count(*) AS Total, >> BounceType, >> IFNULL($v0,'') AS __a0, >> IFNULL($v3,'') AS LastTime, >> IFNULL(Contact,'') AS Contact >> FROM ContactAction >> LET $v0 = BounceInfo.Detail, >> $v1 = Contact, >> $v2 = Contact.Email.Address, >> $v3 = Max(Request.At) >> WHERE (Type=3 AND Campaign=#64:25) AND EntityInfo.State=0 >> GROUP BY Contact LIMIT 10 >> >> the error is >> >> com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error >> on parsing command at position #419: Aggregate function cannot be used >> in LET clause together with GROUP BY Command: SELECT $v1 as Id, $v2 AS >> EmailAddress, Count(*) AS Total, BounceType, IFNULL($v0,'') AS __a0, >> IFNULL($v3,'') AS LastTime, IFNULL(Contact,'') AS Contact FROM >> ContactAction LET $v0 = BounceInfo.Detail, $v1 = Contact, $v2 = >> Contact.Email.Address, $v3 = Max(Request.At) WHERE (Type=3 AND >> Campaign=#64:25) AND EntityInfo.State=0 GROUP BY Contact LIMIT 10 >> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^ >> >> but when I do flatten all fields >> >> SELECT Contact, >> Contact.Email.Address, >> Count(*), >> BounceType, >> BounceInfo.Detail, Max(Request.At) >> FROM ContactAction >> WHERE (Type=3 AND Campaign=#64:25) AND EntityInfo.State=0 >> GROUP BY Contact LIMIT 10 >> >> >> It is working perfectly. >> >> My Best, >> Hung Tran >> > -- --- You received this message because you are subscribed to the Google Groups "OrientDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
