Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Unanswered: Problems using SELECT INTO clause:

    Hi,
    I am very novice to PL/SQL. I am trying to execute a simple SELECT INTO statement, but receives the common 'ORA-01427: single-row subquery returns more than one row' error.

    DEPTGROUP_ID in the following statement returns only 1 value. I exeucted the subquery in sqlplus*. It also returns only 1 value.

    Problems with this query:
    SELECT e.email INTO CLASS_ADMIN_EMAIL from etrn.employees e
    where e.userid = (select userid from depts_groups where deptgroup_id=DEPTGROUP_ID);

    Debug efforts:
    If I do this SELECT COUNT(*) INTO TEST FROM DEPTS_GROUPS where deptgroup_id=DEPTGROUP_ID; I get 9 returns in the procedure output.

    But if I do select userid from depts_groups where deptgroup_id=DEPTGROUP_ID value from sql plust, I get only 1 value.

    Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Problems using SELECT INTO clause:

    You have 2 different queries here. Have you verified that both only return 1 row?

    What is DEPTGROUP_ID? Is it a local variable?

    if
    select userid from depts_groups where deptgroup_id=DEPTGROUP_ID

    returns only 1 row, then I would think your outer query

    SELECT e.email
    INTO CLASS_ADMIN_EMAIL
    from etrn.employees e
    where e.userid = (select userid from depts_groups where deptgroup_id=DEPTGROUP_ID);

    is returning too many rows

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Re: Problems using SELECT INTO clause:

    Hi aksamue,

    The Problem is with Sub-Query only.

    Since, SELECT COUNT(*) INTO TEST FROM DEPTS_GROUPS where deptgroup_id=DEPTGROUP_ID; returns 9 in the procedure output.
    Check the WHERE Clause Properly.
    SATHISH .

  4. #4
    Join Date
    Oct 2003
    Location
    Republic of Srpska, Bosnia and Herzegovina
    Posts
    35
    you should not have same field and variable name in the where clause.
    Replace name of your variable DEPTGROUP_ID.
    I think, your query : select userid from depts_groups where deptgroup_id=DEPTGROUP_ID
    returns all rows from depts_groups
    Hope this help.

  5. #5
    Join Date
    Nov 2003
    Posts
    2

    Re: Problems using SELECT INTO clause:

    am not sure how u r getting diff values in ur debugging efforts. but the error that u r getting is definitely coz ur sub query is returning more than 1 row. if ur sub-query is going to return more than 1 row use 'in' instead of '=' in the join.

Posting Permissions

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