maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_ro_misplacedunitscount()
Parameters
Name
Type
Mode
Definition
/* Returns the counting of units being seen by customers the shouldn't be seeing them */ with k as (WITH q as (SELECT d.serialnumber,u.idunit,u.alias econum,u.idclient uowner,ug.idunitgroup, ug.idclient ugidclient FROM unit u JOIN unit_unitgroup uu ON uu.idunit = u.idunit JOIN unitgroup ug ON ug.idunitgroup = uu.idunitgroup JOIN device d ON d.idunit = u.idunit WHERE u.idclient <> ug.idclient except SELECT d.serialnumber,u.idunit,u.alias econum,u.idclient uowner,ug.idunitgroup, ug.idclient ugidclient FROM unit u JOIN unit_unitgroup uu ON uu.idunit = u.idunit JOIN unitgroup ug ON ug.idunitgroup = uu.idunitgroup JOIN shipperconf sc ON sc.idclientowner = u.idclient JOIN device d ON d.idunit = u.idunit WHERE u.idclient <> ug.idclient ) select q.serialnumber, q.idunit,q.uowner,c.name::varchar(25) custname,idunitgroup,ugidclient,c1.name custreceiver from q JOIN client c ON c.idclient = q.uowner JOIN client c1 ON q.ugidclient = c1.idclient order by ugidclient) select count(*)::integer from k;