This discussion is archived
9 Replies Latest reply: Mar 12, 2013 7:03 AM by 957183 RSS

single row query returns more than one row

957183 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points