Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Single row subquery returns multiple rows

    Hi all,

    I need another help. I'm trying to update this column eff_status as follows:

    select (CASE when SYSDATE>EFFDT and SYSDATE<EXP_DT then 1
    ELSE 0
    END)
    FROM TAB1

    And I gave the following query:

    Update tab1 set eff_status=
    (select (CASE when SYSDATE>EFFDT and SYSDATE<EXP_DT then 1
    ELSE 0
    END)
    FROM TAB1)
    which gives me single row subquery returns multiple rows which is obvious.
    But I cant think of anything else. Can someone please help me with this.

    Thanks very much in advance.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What happens if you kick out SELECT statement?
    Code:
    UPDATE tab1 SET
      eff_status = CASE
                     WHEN sysdate > effdt AND sysdate < exp_dt THEN 1
                     ELSE 0
                   END;

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    It works. Thanks so much Littlefoot.

  4. #4
    Join Date
    Oct 2011
    Posts
    1

    Post Software Developer

    I HAVE THE SAME PROBLEM

    I HAVE A QUERY IN WHICH I WANT TO SHOW REFUND AMOUNT

    IT SHOWS ME THE ERROR MESSAGE

    SINGLE ROW SUB QUERY RETURNS MORE THAN ONE ROW

    ANY HELP WILL BE HIGHLY APPRECIATED

    HERE IS QUERY

    SELECT
    RECEPT.REC_NO,RECEPT.P_ID,RECEPT.P_NAME,RECEPT.CON SULTANT,RECEPT.COMPNAME,
    RECEPT.CREATIONDATE,RECEPT.CREATIONTIME,RECEPT.CRE ATEDBY,
    RECEPT.CANCELED,RECEPT.CANCELEDBY,RECEPT.CANCELLAT IONDATE,RECEPT.CANCELLATIONTIME,
    RECEPT.REC_AMT, RECEPT.TOTALAMT,
    RECEPT.ACCOFAMT REC_ACC,
    RECEPT.ZAKATAMT REC_ZAK,
    RECEPT.STAFFAMT REC_STF,
    RECEPT.BALANCE REC_BAL,
    DECODE(RECEPT.COMMUNITY_DISC_AMT,NULL,0.00,RECEPT. COMMUNITY_DISC_AMT) AS COMMUNITY_DISC_AMT,
    (SELECT sum(recept.rec_amt)FROM RECEPT WHERE RECEPT.REFUND IS NOT NULL AND
    RECEPT.REFUNDDATE BETWEEN '27-SEP-11'
    AND '27-SEP-11' AND RECEPT.REFUNDBY = 'ahameed')REFUND_AMT,
    (SELECT count(recept.rec_no) FROM RECEPT WHERE RECEPT.REFUND IS NOT NULL AND
    RECEPT.REFUNDDATE BETWEEN '27-SEP-11'
    AND '27-SEP-11' AND RECEPT.REFUNDBY = 'ahameed')REFUND_COUNT,REFUND_ID,
    (SELECT RECEPT.REC_AMT FROM RECEPT WHERE RECEPT.REFUNDDATE BETWEEN '27-SEP-11' AND '27-SEP-11'
    AND RECEPT.REFUNDBY = 'ahameed')REFUND_AMOUNT_IND
    FROM RECEPT
    WHERE (RECEPT.CREATIONDATE BETWEEN '27-SEP-11' AND '27-SEP-11') AND RECEPT.CREATEDBY='ahameed'

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    which SELECT throws the error?

    Code:
    SELECT recept.rec_no,
           recept.p_id,
           recept.p_name,
           recept.con                                                    sultant,
           recept.compname,
           recept.creationdate,
           recept.creationtime,
           recept.cre                                                    atedby,
           recept.canceled,
           recept.canceledby,
           recept.cancellat                                              iondate,
           recept.cancellationtime,
           recept.rec_amt,
           recept.totalamt,
           recept.accofamt                                               rec_acc,
           recept.zakatamt                                               rec_zak,
           recept.staffamt                                               rec_stf,
           recept.balance                                                rec_bal,
           Decode(recept.community_disc_amt, NULL, 0.00,
                                             recept. community_disc_amt) AS
           community_disc_amt,
           (SELECT SUM(recept.rec_amt)
            FROM   recept
            WHERE  recept.refund IS NOT NULL
                   AND recept.refunddate BETWEEN '27-SEP-11' AND '27-SEP-11'
                   AND recept.refundby = 'ahameed')                      refund_amt,
           (SELECT COUNT(recept.rec_no)
            FROM   recept
            WHERE  recept.refund IS NOT NULL
                   AND recept.refunddate BETWEEN '27-SEP-11' AND '27-SEP-11'
                   AND recept.refundby = 'ahameed')
           refund_count,
           refund_id,
           (SELECT recept.rec_amt
            FROM   recept
            WHERE  recept.refunddate BETWEEN '27-SEP-11' AND '27-SEP-11'
                   AND recept.refundby = 'ahameed')
           refund_amount_ind
    FROM   recept
    WHERE  ( recept.creationdate BETWEEN '27-SEP-11' AND '27-SEP-11' )
           AND recept.createdby = 'ahameed'
    With Oracle characters between single quote marks are STRINGS!
    'This is a string, 2009-12-31, not a date'
    When a DATE datatype is desired, then use TO_DATE() function.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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