add a sub query in a view to count properties per borrower

[MaryJaneDoane]

Hi Everyone -

I am trying to do a count of properties by account. I tried the following
field1,
field2,
field3,
(select count(prop_id)
from loanrev..collat
group by loanrev..collat.note_id)as numprops,
field4,
field5

It compiles with no errors, but when i try to select the view i get the following error.

Subquery returned more than 1 value. This is not permitted when the subquery is used as an expression.

 

[gmmastros]

Try...

field1,
field2,
field3,
(select count(prop_id) 
 from loanrev..collat
 [b]Where loanrev..collat.note_id = [!]TableName[/!].note_id[/b]
group by loanrev..collat.note_id)as numprops,
field4,
field5

The [!]TableName[/!] referenced in the above code would be the same table name from your where clause. This should cause the subquery to return only 1 row and allow your view to work properly.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MaryJaneDoane]

That worked perfected. Thank You so much.