9 Replies Latest reply: Mar 12, 2013 9:03 AM by 957183 RSS

    single row query returns more than one row

    957183
      hello,

      this is my sql=
      " SELECT  
       
           hpn.id AS id,
          hpn.psn_id, 
          hpn.last_name,
          hpn.person_code,
          hwe.pfn_code,
          hwe.pfn_name,
          hwe.scy_code,
          hwe.scy_name,
          hwe.hsy_name, 
          hwe.hin_id,
          hpn.first_name as FIRST_NAME1
          
          
        FROM
          persons hpn, 
        
          (SELECT
              w.hpn_id,
              s.name AS scy_name,
              s.code AS scy_code,
              p.name AS pfn_name,
              p.code AS pfn_code,
              i.name AS hsy_name,
          i.id as hin_id
            FROM workplaces w, specialities s, professions p, INSTITUTIONS i
            WHERE
              (w.scy_id=s.id)AND
              (w.pfn_id=p.id)AND
              (w.hin_id=i.id)AND
              (w.end_date IS NULL)AND
            
          ) hwe
        WHERE
          (hpn.id=hwe.hpn_id(+))
          
      "
      what i want is add subselect something like this (select card_number from REGISTRATION_CARDS x where (hpn.id=x.hpn_id(+)) )as card_number,

      but it returns error single row query returns more than one row, because one people can have more that one card_number.

      can some someone help me to write correct query please.
        • 1. Re: single row query returns more than one row
          John Stegeman
          No, we cannot help you because we don't know what you want to happen when that subquery returns more than one row.
          • 2. Re: single row query returns more than one row
            908002
            The subselect as column should return only one row.. maay be you need to removed outerjoin to make sure the query return single row... if no rows found the query returns null as column value. if any one value should be fine if condition matches for morethan one rown add a condition rownum<=1 to the query
            • 3. Re: single row query returns more than one row
              957183
              Select return persons info about persons , now i want to add the registration card number from REGISTRATION_CARDS x persons.id=x.hpn_id but select need to return all rows that before with no duplicate the person who have more than one reigistration card
              • 4. Re: single row query returns more than one row
                957183
                i added in just another subselect, but then select return all persons + duplicate persons who hawe more tan one card :)
                • 5. Re: single row query returns more than one row
                  John Stegeman
                  SQL result sets are "square" - sounds like you want to join instead of using a subquery.
                  all rows that before with no duplicate the person who have more than one reigistration card
                  JS-0497: Unable to parse sentence
                  • 6. Re: single row query returns more than one row
                    957183
                    Priview select return 12345 rows , what will be the correct construction that, new select return too 12345 rows with no cards duplicates ?
                    Thanks
                    • 7. Re: single row query returns more than one row
                      957183
                      SELECT
                          hpn.id AS id,
                          hpn.psn_id,
                          hpn.last_name,
                          hpn.person_code,
                          hpn.ident,
                          hpn.sex_id,
                          hpn.birth_date,
                          hpn.death_date,
                          hpn.report_number,
                          hpn.REPORT_REG_DATE,
                          hpn.status,
                          hpn.HPN_TYPE_ID,
                          hpn.notes,
                          hpn.hpn_cty_id,
                          hpn.pid_id,
                          hpn.foreign_ident,
                          hwe.pfn_code,
                          hwe.pfn_name,
                          hwe.scy_code,
                          hwe.scy_name,
                          hwe.hsy_name,
                          hpn.phone,
                          hpn.mobile_phone,
                          hpn.fax,
                          hpn.email,
                          hpn.address,
                          hpn.national_language_level,
                          hpn.national_language_certificate,
                          hpn.eet_id,
                          hpn.cty_id,
                          hpn.graduation_year,
                          hpn.diploma_number,
                          hpn.dsy_id,
                          hpn.dsy_id_2,
                          hpn.pfn_id,
                          hpn.sde_id,
                          hwe.hin_id,
                          hpn.first_name as FIRST_NAME1
                        
                        FROM
                          aip_healthcare_persons hpn, 
                          (SELECT
                                       
                              w.hpn_id,
                              s.name AS scy_name,
                              s.code AS scy_code,
                              p.name AS pfn_name,
                              p.code AS pfn_code,
                              i.name AS hsy_name,  
                              i.id as hin_id
                            FROM aip_hpn_workplaces w, aip_specialities s, aip_professions p, AIP_HEALTHCARE_INSTITUTIONS i
                            WHERE
                              (w.scy_id=s.id)AND
                              (w.pfn_id=p.id)AND
                             
                              (w.hin_id=i.id)AND
                              (w.end_date IS NULL)AND
                              (w.is_main_job='J')   
                          ) hwe
                        
                        WHERE
                          hpn.id=hwe.hpn_id(+)
                         
                      --38337
                      
                       SELECT
                          hpn.id AS id,
                          hpn.psn_id,
                          hpn.last_name,
                          hpn.person_code,
                          hpn.ident,
                          hpn.sex_id,
                          hpn.birth_date,
                          hpn.death_date,
                          hpn.report_number,
                          hpn.REPORT_REG_DATE,
                          hpn.status,
                          hpn.HPN_TYPE_ID,
                          hpn.notes,
                          hpn.hpn_cty_id,
                          hpn.pid_id,
                          hpn.foreign_ident,
                          hwe.pfn_code,
                          hwe.pfn_name,
                          hwe.scy_code,
                          hwe.scy_name,
                          hwe.hsy_name,
                          hpn.phone,
                          hpn.mobile_phone,
                          hpn.fax,
                          hpn.email,
                          hpn.address,
                          hpn.national_language_level,
                          hpn.national_language_certificate,
                          hpn.eet_id,
                          hpn.cty_id,
                          hpn.graduation_year,
                          hpn.diploma_number,
                          hpn.dsy_id,
                          hpn.dsy_id_2,
                          hpn.pfn_id,
                          hpn.sde_id,
                          hwe.hin_id,
                          hpn.first_name as FIRST_NAME1,
                          hrc.card_number
                        FROM
                          aip_healthcare_persons hpn,  aip.aip_hpn_registration_cards hrc,
                          (SELECT
                                       
                              w.hpn_id,
                              s.name AS scy_name,
                              s.code AS scy_code,
                              p.name AS pfn_name,
                              p.code AS pfn_code,
                              i.name AS hsy_name,  
                              i.id as hin_id
                            FROM aip_hpn_workplaces w, aip_specialities s, aip_professions p, AIP_HEALTHCARE_INSTITUTIONS i
                            WHERE
                              (w.scy_id=s.id)AND
                              (w.pfn_id=p.id)AND
                             
                              (w.hin_id=i.id)AND
                              (w.end_date IS NULL)AND
                              (w.is_main_job='J')   
                          ) hwe
                        
                        WHERE
                          hpn.id=hwe.hpn_id(+)
                          AND hpn.id =hrc.hpn_id(+)
                      --38430
                      the second query returned more rows than the first, what i don't want to. what i want is that wen i search person whit my query by card_number query returned one result. example if Josh has card numbers 001 and 002. when i search person by card_number 001 or 002, query should return 1 Josh, . bet my query returned 2 what isnt correct, i need to display only 1 person info per all card_numbers that he has

                      Edited by: 954180 on Mar 12, 2013 3:30 AM

                      Edited by: 954180 on Mar 12, 2013 3:31 AM
                      • 8. Re: single row query returns more than one row
                        John Spencer
                        954180 wrote:
                        the second query returned more rows than the first, what i don't want to. what i want is that wen i search person whit my query by card_number query returned one result. example if Josh has card numbers 001 and 002. when i search person by card_number 001 or 002, query should return 1 Josh, . bet my query returned 2 what isnt correct, i need to display only 1 person info per all card_numbers that he has

                        Edited by: 954180 on Mar 12, 2013 3:30 AM

                        Edited by: 954180 on Mar 12, 2013 3:31 AM
                        OK, for Josh, which of the two card numbers do you want the query to return? Once you decide that, write a query against aip_hpn_registration_cards that returns hpn_id and card_number for the row you want. It could possibly be as simple as
                        select hpn_id, max(card_number) cardnumber
                        from aip_hpn_registration_cards
                        group by hpn_id
                        Then use that query whaere you are currently using the table.

                        John
                        • 9. Re: single row query returns more than one row
                          957183
                          SELECT
                              hpn.id AS id,
                              hpn.psn_id,
                              (hpn.first_name || decode(hpn.middle_name,null,'',' ' || hpn.middle_name )) as FIRST_NAME,
                              hpn.last_name,
                              hpn.person_code,
                              hpn.ident,
                              hpn.sex_id,
                              hpn.birth_date,
                              hpn.death_date,
                              hpn.report_number,
                              hpn.REPORT_REG_DATE,
                              hpn.status,
                              hpn.HPN_TYPE_ID,
                              hpn.notes,
                              hpn.hpn_cty_id,
                              hpn.pid_id,
                              hpn.foreign_ident,
                              hwe.pfn_code,
                              hwe.pfn_name,
                              hwe.scy_code,
                              hwe.scy_name,
                              hwe.hsy_name,
                              hpn.phone,
                              hpn.mobile_phone,
                              hpn.fax,
                              hpn.email,
                              hpn.address,
                              hpn.national_language_level,
                              hpn.national_language_certificate,
                              hpn.eet_id,
                              hpn.cty_id,
                              hpn.graduation_year,
                              hpn.diploma_number,
                              hpn.dsy_id,
                              hpn.dsy_id_2,
                              hpn.pfn_id,
                              hpn.sde_id,
                              hwe.hin_id,
                              hpn.first_name as FIRST_NAME1,
                              max(hrc.card_number)
                            FROM
                              aip_healthcare_persons hpn,  aip.aip_hpn_registration_cards hrc,
                              (SELECT
                                           
                                  w.hpn_id,
                                  s.name AS scy_name,
                                  s.code AS scy_code,
                                  p.name AS pfn_name,
                                  p.code AS pfn_code,
                                  i.name AS hsy_name,  
                                  i.id as hin_id
                                FROM aip_hpn_workplaces w, aip_specialities s, aip_professions p, AIP_HEALTHCARE_INSTITUTIONS i
                                WHERE
                                  (w.scy_id=s.id)AND
                                  (w.pfn_id=p.id)AND
                                 
                                  (w.hin_id=i.id)AND
                                  (w.end_date IS NULL)AND
                                  (w.is_main_job='J')   
                              ) hwe
                            
                            WHERE
                              hpn.id=hwe.hpn_id(+)
                              AND hpn.id =hrc.hpn_id(+)
                          --38337
                          In this case i have [1]: ORA-00937: not a single-group group function
                          Error