ERROR: more than one Row returned by a subquery used as an Expression

Asked

Viewed 1,605 times

0

GOAL

I have two tables (tb_get_gap, tb_getCountSRVS), where in the first table count to get the desired values and in the second I have integer values, I just want to take the values of the two and subtract

QUERY

select 
(cast(srvs as int) - (SELECT COUNT(*) hostname FROM tb_get_gap
LEFT JOIN tb_get_customers
ON tb_get_gap.customer = tb_get_customers.cust_cmdb WHERE tb_get_customers.customer in (select customer from tb_get_customers) and tb_get_gap.exception = 'NO'
GROUP BY tb_get_gap.customer ORDER BY tb_get_gap.customer ASC)) 
from tb_getCountSRVS order by cust_code asc

OUTPUT

>[Error] Script lines: 1-6 --------------------------
 ERROR: more than one row returned by a subquery used as an expression
 Line: 1
database postgresql

  • Wouldn’t you have to correlate your subquery with some table value tb_getCountSRVS? Your subquery is counting for each existing Customer value in tb_get_gap and returning such list.

  • I believe so, because if I put LIMIT 1 inside the subquery it returns the values, but decreases the same values of all.

  • Then check which field of table tb_getCountSRVS refers the value srvs for you to correlate with your subquery (kicking, since I don’t know your tables, I would say Customer).

  • SELECT COUNT(*) hostname - cast(tb_getCountSRVS.srvs as int)
FROM tb_get_gap
LEFT JOIN tb_getCountSRVS
ON tb_get_gap.customer = tb_getCountSRVS.cust_code WHERE tb_getCountSRVS.customer in (select customer from tb_getCountSRVS) and tb_get_gap.exception = 'NO'
GROUP BY tb_get_gap.Customer ORDER BY tb_get_gap.

  • OUTPUT: >[Error] Script Lines: 1-5 -------------------------- ERROR: syntax error at or near "-" Line: 1

1 answer

1


Kicking, since you have not provided enough information as is recommended on this site [minimum, complete and verifiable example], I think it may be:

SELECT (cast(srvs AS int) - 
    (SELECT COUNT(*) hostname FROM tb_get_gap
    LEFT JOIN tb_get_customers ON tb_get_gap.customer = tb_get_customers.cust_cmdb 
    WHERE tb_get_customers.customer =  tb_getCountSRVS.customer AND tb_get_gap.exception = 'NO'
    )
) 
FROM tb_getCountSRVS ORDER BY cust_code ASC;

  • Oops, it worked out, thank you very much!!

Browser other questions tagged database postgresql

You are not signed in. Login or sign up in order to post.