Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2013
    Posts
    1

    Unanswered: help me to correct this query

    hello guys i've some problems in my query.

    when i write this query , i've got the right result

    select distinct(eq_status)from rspi_equipment
    where eq_status in('B','TU','R')


    when i write this query, i've got the right reult too

    select eq_status, count(to_number(eq_qty))
    from rspi_equipment
    where it_itemnum='02007003' and loc_locationid =9069 and eq_status in('B','TU','R')
    group by eq_status

    notes: in location 9069 for item 02007003 there's only two status, which is 'B' and 'TU'. So the QTY for item 02007003 whith 'B' status is 7 and 'TU' status is 3


    but when i combine with another query there's error ORA-01427

    here the query

    select (select distinct(eq_status)from rspi_equipment
    where eq_status in('B','TU','R')), count(NVL(to_number(eq_qty),0))
    from rspi_equipment
    where it_itemnum='02007003' and loc_locationid =9069 and eq_status in('B','TU','R')
    group by eq_status;

    the result that what i want is, if item 02007003 didn't have 'R' status it's count 0 .

    can you help me to fix my query???
    sorry for bad english

    thank's for helping

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    [oracle@localhost ~]$ oerr ora 1427
    01427, 00000, "single-row subquery returns more than one row"
    // *Cause:
    // *Action:
    so change SQL so that only a single row is returned.
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by chulz90 View Post
    hello guys i've some problems in my query.
    . . . E t c . . .
    select (select distinct(eq_status)from rspi_equipment
    where eq_status in('B','TU','R')), count(NVL(to_number(eq_qty),0))
    from rspi_equipment
    where it_itemnum='02007003' and loc_locationid =9069 and eq_status in('B','TU','R')
    group by eq_status;
    . . . E t c . . .
    can you help me to fix my query???
    If you would FORMAT your query, perhaps the problem would be obvious:
    Code:
    SELECT (SELECT DISTINCT (eq_status)           --<< This select is returning 
              FROM rspi_equipment                 --<< many rows
             WHERE eq_status IN ('B', 'TU', 'R'))
         , COUNT (NVL (TO_NUMBER (eq_qty), 0))
      FROM rspi_equipment
     WHERE it_itemnum = '02007003'
       AND loc_locationid = 9069
       AND eq_status IN ('B', 'TU', 'R')
     GROUP BY eq_status;
    You may need to move that sub-query out of main query; and ... "perhaps" it's not even necessary if you replace it by the column you seek.
    Try to correct the query, post the results and if you still have problems, maybe an expert here will help you out.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two ideas.
    Not tested.

    Example 1:
    Code:
    SELECT eq_status
         , COUNT(
              CASE
              WHEN it_itemnum     = '02007003'
               AND loc_locationid = 9069       THEN
                   NVL(to_number(eq_qty) , 0)
              END
           )
     FROM  rspi_equipment
     WHERE eq_status in ('B' , 'TU' , 'R')
     GROUP BY
           eq_status
    ;

    Example 2:
    Code:
    SELECT s.eq_status
         , COUNT( NVL(to_number(eq_qty) , 0) )
     FROM  (SELECT 'B'  AS eq_status FROM dual UNION ALL
            SELECT 'TU'              FROM dual UNION ALL
            SELECT 'R'               FROM dual
           ) AS s
     LEFT  OUTER JOIN
           rspi_equipment AS r
      ON   r.eq_status      = s.eq_status
       AND r.it_itemnum     = '02007003'
       AND r.loc_locationid = 9069
     GROUP BY
           s.eq_status
    ;

Posting Permissions

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