Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    24

    Unanswered: update problem ORA-01427: single-row subquery returns more than one row

    Hi after some help cant figure out what is going wrong

    this is my code below
    UPDATE per_all_assignments pass
    SET pass.default_code_comb_id = (SELECT code.code_combination_id
    FROM
    per_all_assignments_f ass,
    hr_all_organization_units org,
    pay_cost_allocation_keyflex pay,
    gl_code_combinations code
    WHERE pay.segment2 = code.SEGMENT2
    AND
    code.segment3 = '6415'
    AND
    ass.organization_id = org.organization_id
    AND
    pay.COST_ALLOCATION_KEYFLEX_ID = org.COST_ALLOCATION_KEYFLEX_ID
    AND
    code.CODE_COMBINATION_ID = ass.DEFAULT_CODE_COMB_ID)
    WHERE
    pass.assignment_id = (SELECT <----- ORA-01427: single-row subquery returns more than one row




    ass.assignment_id
    FROM
    per_all_assignments_f ass,
    hr_all_organization_units org,
    pay_cost_allocation_keyflex pay,
    gl_code_combinations code
    WHERE
    ass.organization_id = org.organization_id
    AND
    pay.COST_ALLOCATION_KEYFLEX_ID = org.COST_ALLOCATION_KEYFLEX_ID
    AND
    code.CODE_COMBINATION_ID = ass.DEFAULT_CODE_COMB_ID
    AND
    pay.segment2 <> code.SEGMENT2
    AND
    SYSDATE BETWEEN ass.effective_start_date AND ass.effective_end_date)

    i get the ORA-01427: single-row subquery returns more than one row
    on the second where clause

    any help would be great!

  2. #2
    Join Date
    Oct 2005
    Posts
    24
    i have noticed what was happening..
    id put = instead of IN,

    but now the query runs it doesnt update anything but when i run the two selects independantly it brigns back the data?

    any ideas?

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Member since 2005, and still not knowing how to properly format your message! What an ugly, unformatted code! Why do you expect anyone to waste his/her time trying to decipher what you've written? Is it THAT difficult to put it this way?
    Code:
    UPDATE per_all_assignments pass
       SET pass.default_code_comb_id =
              (SELECT code.code_combination_id
                 FROM per_all_assignments_f ass,
                      hr_all_organization_units org,
                      pay_cost_allocation_keyflex pay,
                      gl_code_combinations code
                WHERE pay.segment2 = code.segment2
                  AND code.segment3 = '6415'
                  AND ass.organization_id = org.organization_id
                  AND pay.cost_allocation_keyflex_id = org.cost_allocation_keyflex_id
                  AND code.code_combination_id = ass.default_code_comb_id)
     WHERE pass.assignment_id IN
              (SELECT ass.assignment_id
                 FROM per_all_assignments_f ass,
                      hr_all_organization_units org,
                      pay_cost_allocation_keyflex pay,
                      gl_code_combinations code
                WHERE ass.organization_id = org.organization_id
                  AND pay.cost_allocation_keyflex_id = org.cost_allocation_keyflex_id
                  AND code.code_combination_id = ass.default_code_comb_id
                  AND pay.segment2 <> code.segment2
                  AND SYSDATE BETWEEN ass.effective_start_date
                                  AND ass.effective_end_date)
    As of your question: the fact that query returns records doesn't mean that UPDATE will really update the table; are there any pass.assignment_ids in the result set returned by SELECT you've written?

  4. #4
    Join Date
    Oct 2005
    Posts
    24

    Cool

    sorry... will format it better in furture.. didnt realise it had come over like that..

    do you mean in the second select?
    SELECT ass.assignment_id
    FROM per_all_assignments_f ass,
    hr_all_organization_units org,
    pay_cost_allocation_keyflex pay,
    gl_code_combinations code
    WHERE ass.organization_id = org.organization_id
    AND pay.cost_allocation_keyflex_id = org.cost_allocation_keyflex_id
    AND code.code_combination_id = ass.default_code_comb_id
    AND pay.segment2 <> code.segment2
    AND SYSDATE BETWEEN ass.effective_start_date
    AND ass.effective_end_date

    ? this does return values yes?

  5. #5
    Join Date
    Oct 2005
    Posts
    24
    see its done it again! arr i pasted that formatted like yours but its gone back to left justified

  6. #6
    Join Date
    Mar 2008
    Location
    Seattle
    Posts
    11
    You have to do [ code ] text [ / code ] (but remove the spaces inside the brackets, I just did that so the forum wouldn't interpret it as a code block).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •