Hello, i encounter an error while saving a query dataset. the SQL code is correct and it execute on postgresql, also if i put only one subquery the dataset save and work correctly proving that there is no issue with the sql itself.
error: sbi.ds.test.error.duplication
query:
select c.entityid, c.machineid, c.area, c.location, sequencenumber, removaldate, coinsamount, notesamount, tinremoved, tininstalled, recyclerbefore,
recyclerafter,active,serialnumber, description, model, d.area, precinct, d.location, tariffid, controlledspaces, latitude, longitude, locationcomment,
tariffversion, status, coinsvaultlimit, notesvaultlimit, firmwareversion, allversions, ipaddress, scr, sim, custom1, custom2, custom3, custom4,
notes, pbpzoneid, pbplocationid, sendflags, customername, gstnumber, vaultkeycode, recyclersize, lastmessagetime, lasttransactiontime, runnumber,
coinsvaulttotal, notesvaulttotal, notescount, statustime, statuserrormsg, statuswarningmsg
from (SELECT entityid, machineid, area, location, sequencenumber, removaldate, coinsamount, notesamount, tinremoved, tininstalled, recyclerbefore, recyclerafter
from ( SELECT entityid, machineid, area, location, sequencenumber, removaldate, coinsamount, notesamount, tinremoved, tininstalled, recyclerbefore, recyclerafter,
ROW_NUMBER() OVER(PARTITION BY machineid ORDER BY removaldate DESC) AS RowNum
FROM parking.x002fparkingx002fcashremoval
) AS T
WHERE RowNum = 1
) as c
inner join (select entityid, active, machineid, serialnumber, description, model, area, precinct, location, tariffid, controlledspaces, latitude, longitude, locationcomment, tariffversion, status, coinsvaultlimit, notesvaultlimit, firmwareversion, allversions, ipaddress, scr, sim, custom1, custom2, custom3, custom4, notes, pbpzoneid, pbplocationid, sendflags, customername, gstnumber, vaultkeycode, recyclersize, lastmessagetime, lasttransactiontime, runnumber, coinsvaulttotal, notesvaulttotal, notescount, statustime, statuserrormsg, statuswarningmsg
from ( SELECT *,
ROW_NUMBER() OVER(PARTITION BY machineid ORDER BY lasttransactiontime DESC) AS RowNum
FROM parking.x002fparkingx002fdevice
) AS T
WHERE RowNum = 1) as d
on c.machineid = d.machineid;
