Problema en Consulta SQL

[EdVaMaTe]

Tengo una base de datos de nómina con el siguiente script, el cual en una base de datos funciona, y lo ejecuto en otra base de datos de copia y no lo hace. No he podido encontrar el error. Agradecería cualquier sugerencia.

Código SQL:

-- View: etitc_basicpayrollsiif_vt
-- DROP VIEW etitc_basicpayrollsiif_vt;
 
CREATE OR REPLACE VIEW etitc_basicpayrollsiif_vt AS 
 SELECT DISTINCT 1000003 AS ad_client_id, 1000003 AS ad_org_id, 
    'es_CO'::text AS ad_language, m.hr_process_id, e.hr_payroll_id, 
    e.c_bpartner_id, m.validfrom, m.validto, b.name, b.VALUE, 
    ( SELECT m.qty
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000208::NUMERIC) AS quatity, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000247::NUMERIC) AS salary, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000252::NUMERIC) AS foodsubsidy, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000143::NUMERIC) AS transportsubsidy, 
    ( SELECT m.qty
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000215::NUMERIC) AS extrahoursquantity, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000192::NUMERIC) AS totalvalueextrahours, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000144::NUMERIC) AS servicebonus, 
    ( SELECT SUM(m.amount) AS amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND (m.hr_concept_id = ANY (ARRAY[1000230::NUMERIC, 1000232::NUMERIC]))) AS bonustechnicalnosalary, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000228::NUMERIC) AS bonuscoordination, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000231::NUMERIC) AS bonustechnicalsalary, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000261::NUMERIC) AS totalaccrued, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000171::NUMERIC) AS employeehealthquote, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000275::NUMERIC) AS employeeboardquote, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1001154::NUMERIC) AS employeeisaquote, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000189::NUMERIC) AS pensionsolidarityfund, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000169::NUMERIC) AS coeeducol, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000276::NUMERIC) AS credivalores, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000197::NUMERIC) AS bancopopularcharge, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000279::NUMERIC) AS daviviendacharge, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000198::NUMERIC) AS compensarcharge, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000304::NUMERIC) AS complementaryplanhealth, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000168::NUMERIC) AS canapro, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000280::NUMERIC) AS afc, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000305::NUMERIC) AS bayport, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000251::NUMERIC) AS tradeunion, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000250::NUMERIC) AS bolivarinsurance, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000175::NUMERIC) AS liensandfines, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000277::NUMERIC) AS withholdingwages, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000260::NUMERIC) AS totaldeductions, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000199::NUMERIC) AS netpay, 
    ( SELECT m.amount
           FROM hr_movement m
          WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000199::NUMERIC) AS pay
   FROM hr_employee e
   JOIN hr_payroll p ON p.hr_payroll_id = e.hr_payroll_id
   JOIN c_bpartner b ON b.c_bpartner_id = e.c_bpartner_id
   LEFT JOIN hr_movement m ON m.c_bpartner_id = e.c_bpartner_id;
 
ALTER TABLE etitc_basicpayrollsiif_vt
  OWNER TO adempiere;
 
[B]ERROR:[/B]
ERROR:  una subconsulta utilizada como expresión retornó más de un registro
********** Error **********
 
ERROR: una subconsulta utilizada como expresión retornó más de un registro
SQL state: 21000
 

[Libras]

Lo que pasa es que uno de tus subqueries como estos:

Código SQL:

( SELECT m.amount
FROM hr_movement m
WHERE m.c_bpartner_id = e.c_bpartner_id AND m.hr_concept_id = 1000251::NUMERIC) AS tradeunion
 

Esta regresando mas de un valor y eso no es valido en el formato en que tienes la consulta.

que manejador de bases de datos estas usando? esto porque creo que ese query se podria mejorar usando case en lugar de tanto subquery que eso le impacta demasiado en el tiempo de respuesta.

[EdVaMaTe]

Estoy manejando PostgreSQL.
Una idea de como podría ser con Case.
Gracias