SubQuery Returning Mre than 1 Value

[tcgoth]

Hello everyone,

I am receiving the "Subquery returning more than 1 value" error from the following code. I understand that the error is being created by customers where multiple records might exist for "serv_type IN ( 'C201','C209','C212','C218').

Question 1: Is there any way to modify the subquery so that I can return the multiple records BUT still only return the max(seq) within that serv_type? For each serv_type I need to return only the most recent transaction.

Output would look like:

Joe Smith C201 etc.
Joe Smith C209 etc.
Carol King C209 etc.

Question 2: Is there any way to formulate an easy SQL statement to find all of the customers that contain multiple records contained in this serv_type?

Example of customer that would be returned:
Joe Smith C201
Joe Smith C209

Example of customer that would Not be Returned:
Candy Smith C209

CREATE VIEW dbo.rv_v2max_homesale_price_referral
AS
SELECT     *,
                          (SELECT     mny_est_sale_price
                            FROM          rv_v2form_0003_rpt_of_sale_detail
                            WHERE      rv_v2form_0003_rpt_of_sale_detail.serv_type in ( 'c201','C209','C212','C218') AND 
                                                   rv_v2form_0003_rpt_of_sale_detail.serv_uid = rv_v2max_seq_form0003.serv_uid AND seq = maxseq_0003) AS Est_Sale_Price,
                          (SELECT     dt_act_sale_date
                            FROM          rv_v2form_0003_rpt_of_sale_detail
                            WHERE      rv_v2form_0003_rpt_of_sale_detail.serv_type in ( 'c201','C209','C212','C218') AND 
                                                   rv_v2form_0003_rpt_of_sale_detail.serv_uid = rv_v2max_seq_form0003.serv_uid AND seq = maxseq_0003) AS Act_Sale_Date,
                          (SELECT     mny_act_sale_price
                            FROM          rv_v2form_0003_rpt_of_sale_detail
                            WHERE      rv_v2form_0003_rpt_of_sale_detail.serv_type in ( 'c201','C209','C212','C218') AND 
                                                   rv_v2form_0003_rpt_of_sale_detail.serv_uid = rv_v2max_seq_form0003.serv_uid AND seq = maxseq_0003) AS Act_Sale_Price,
                          (SELECT     mny_est_referral_fee_due1
                            FROM          rv_v2form_0003_rpt_of_sale_detail
                            WHERE      rv_v2form_0003_rpt_of_sale_detail.serv_type in ( 'c201','C209','C212','C218') AND 
                                                   rv_v2form_0003_rpt_of_sale_detail.serv_uid = rv_v2max_seq_form0003.serv_uid AND seq = maxseq_0003) AS Est_Referral_Fee,
                          (SELECT     mny_act_referral_fee_due1
                            FROM          rv_v2form_0003_rpt_of_sale_detail
                            WHERE      rv_v2form_0003_rpt_of_sale_detail.serv_type in ( 'c201','C209','C212','C218') AND 
                                                   rv_v2form_0003_rpt_of_sale_detail.serv_uid = rv_v2max_seq_form0003.serv_uid AND seq = maxseq_0003) AS Act_Referral_Fee,
                          (SELECT     dt_est_sale_date
                            FROM          rv_v2form_0003_rpt_of_sale_detail
                            WHERE      rv_v2form_0003_rpt_of_sale_detail.serv_type in ( 'c201','C209','C212','C218') AND 
                                                   rv_v2form_0003_rpt_of_sale_detail.serv_uid = rv_v2max_seq_form0003.serv_uid AND seq = maxseq_0003) AS Est_Sale_Date,
                          (SELECT     dt_referral_fee_due1
                            FROM          rv_v2form_0003_rpt_of_sale_detail
                            WHERE      rv_v2form_0003_rpt_of_sale_detail.serv_type in ( 'c201','C209','C212','C218') AND 
                                                   rv_v2form_0003_rpt_of_sale_detail.serv_uid = rv_v2max_seq_form0003.serv_uid AND seq = maxseq_0003) AS Est_Referral_Date,
                          (SELECT     dt_referral_fee_due2
                            FROM          rv_v2form_0003_rpt_of_sale_detail
                            WHERE      rv_v2form_0003_rpt_of_sale_detail.serv_type in ( 'c201','C209','C212','C218') AND 
                                                   rv_v2form_0003_rpt_of_sale_detail.serv_uid = rv_v2max_seq_form0003.serv_uid AND seq = maxseq_0003) AS Act_Referral_Date
FROM         dbo.rv_V2max_seq_form0003

 

[SQLSister]

subqueries can only return one record. If you want multiple records per person do joins instead. Use an inner join if you know records will exist in both tables other wise use an outer join. To better help you, it would help if you gave us the relationsional structure, sample data inthat structure and the results you wanted.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief